Divide cell amounts between cells equally

**hotwoz** · 07-07-2011, 06:39 AM

Hi, I have a chart of numbers and i need the difference inbetween 2 cells (shown here on the left and right) to be divided up equally between the amount of cells inbetween the cells. The amount of cells in the centre here is two but the amount will vary.

Please see below where i have manually worked one out to give you an example.

13.78 14.62 15.46 16.30
15.24 ------------------ 21.17
22.74 ------------------ 29.09
26.84 ------------------ 35.42
32.10 ------------------ 40.71
37.02 ------------------ 49.84
44.60 ------------------ 57.58
49.58 ------------------ 62.05

Thanks

**Domski** · 07-07-2011, 06:57 AM

If you data is in A1:A8 and D1:D8 then this formula in B1 copied across B1:C8 will work:

=($D1-$A1)/(COLUMN($D1)-1)+A1

For more columns you just need to change reference in the formula to the last column holding the data.

Dom

**hotwoz** · 07-07-2011, 08:05 AM

This is strange, perhaps you can find out what is wrong. It only seems to work in cells A to D. If I place the formula anywhere else and change to cell reference numbers to suit it doesn't work. If I do it in cells A to D and drag somewhere else the results change from cell to cell.

I have uploaded an example.

Thanks.

How would I write this formula correctly? =a4-a1 divided by 3 plus a1. Although I would have to write a new formula for each column it should work.

**Domski** · 07-07-2011, 08:14 AM

On your example it would be =(($L17-$I17)/(COLUMN($D17)-1)+I17) in J17 copied down and across.

The COLUMN($D17)-1 bit returns the number that the difference between your 2 values should be divided but to increment the result. You could just hard code it as 3 in this example but it allows for you inserting more columns.

It may not be the best solution to be honest as I came up with it fairly quickly.

Dom

**hotwoz** · 07-07-2011, 08:41 AM

Yes that worked for that particular situation but in my actuall sheet the $D17 has other data in it which messes it up. If I change the cell ref to a different blank one it changes all the values. Sorry I dont understand why the $d17 is where it is, in the middle of nowhere. Obviously its location is essential otherwise it would not mess up if moved somewhere else.

Could this be made simpler? I know i'd have to do a formula for every column but would something like this work, however the correct formula would be needed.

How would I write this formula correctly? =a4-a1 divided by 3 plus a1. This would be based on 2 blank columns in the centre as above, this is basically how I worked it out on the calculator.

If I did the above then wrote a new formula for the remaining column??!

**Domski** · 07-07-2011, 08:54 AM

It doesn't matter whether D17 has a value/formula in it or not. All Column(Cell_Ref) does it return the number of the column that you are referencing. So Column($D17)-1 will always be equal to 3 no matter how many columns you drag the formula across.

I'll see if I can think of a better formula.

Dom

**hotwoz** · 07-08-2011, 05:54 AM

This is a solution I have found through a friend which works perfectly, thanks for your help.

=ROUND(SUM($A1,(($D1-$A1)/(COLUMNS($A1:$D1)-1)*COLUMNS($A1:A1))),2)

:-)