interpolation

i have a spreadsheet with a column of numbers in increments of 5 ie 25, 30, 35, 40, 45, 50, etc. and an adjacent column of decending numbers ie 275,273,262,250,238 where the numbers in column b decrease by varying amounts each time. for a given value say 37 i want to interpolate between the corisponding values of 35 and 40 to give an answer. does this make sense.


billy

there you go as i discribed earlier

sorry what do i do with that code i was after a function which i could use in a cell or do i use "=LINTERP(D3,$A$3:$A$18,$B$3:$B$18)[/COLOR]" however i tried to use the LINTERP function and received a #name error

thanks for your help that worked a treat.

is there a way to do it without using visual basic for future refference

billy

If i was to alter the table as shown in the new book 1 i have uploaded and add a second pb column so depending on which column i select it will interpolate relative to that column.

bear in mind that i may want to add further pb columns at a later date how do i add a match or hlookup to your original code and how do i ammend to suit any given number of columns

thanks

billy

Originally Posted by festering

is there a way to do it without using visual basic for future refference

Hello billy,

using shg's setup you could use this formula in H3

=MATCH(G2,B2:E2,0)

then in I3 for the result

=LOOKUP(F3,A3:A17,INDEX(B3:E17,0,H3)+(INDEX(B4:E18,0,H3)-INDEX(B3:E17,0,H3))*(F3-A3:A17)/(A4:A18-A3:A17))

assumes that A3:A18 will be sorted ascending

Originally Posted by daddylonglegs

,

=LOOKUP(F3,A3:A17,INDEX(B3:E17,0,H3)+(INDEX(B4:E18,0,H3)-INDEX(B3:E17,0,H3))*(F3-A3:A17)/(A4:A18-A3:A17))

assumes that A3:A18 will be sorted ascending

Hi,

I am trying to interpolate y value for a fixed x value using the above formula. My x by y data has a quadratic function so x is not sorted ascending (while the formula demand that order), but y is in an ascending order. I many of my spreadsheets, the result returned valid, but not always. I have uploaded a sample spreadsheet with this post. I will use this formula across sheets (I can do that), but need to return the results across sheets in to a new sheet by sheet number. Sorry I don't have knowledge of programing.

Thanks,
Abys

Thanks.
I will do that.