I want to interpolate two values in Excel

[steven757]

Summary:: Interpolation

Hi,

I want to interpolate two values in Excel.

The first value is in cell "A1" and has the value "0". The second value is in cell "A20" and has the value "1".

I now want to interpolate the values in cells "A2" to "A19" in such a way that a linear straight line is created between the values "0" and "1".

The particular problem is that the cell numbers between "0" and "1" are not always the same size. Sometimes there are e.g. 10 cells between "0" and "1" and other times there are e.g. 25 cells between "0" and "1". Each interpolation has a different number of cells between the value "0" and the value "1".

Is it possible to create a formula that automatically takes into account the different numbers of cells between "0" and "1" and calculates the interpolation between "0" and "1"?

Many thanks for your help.

Steven

[Moderator's note: moved from a technical forum. Member has been advised to show some effort.]

 

[fresh_42]

It is no problem if you know the number of cells in between. If you don't, then create a column that only counts the cell: A1=1, A2=A1+1, A3=A2+1 etc. Then you can take the difference to know the number of cells, say N.

 

[steven757]

Hi fresh_42,

Thanks for your simple advice. I have tried some other solutions but your simple approach is the best.

Thanks.

 

[gmax137]

you could also use the ROW function (on the two end points) to find your number of cells between.

 

[steven757]

Hi gmax137,

thanks for your great suggestion, I have tried it immediately and it works fine. But I use the ROWS function instead of the ROW function.