Interpolate between data

**abduljaleel.mca** · 10-08-2013, 07:35 AM

Hi,

Please have a look at the attached file.

Please let me know how to interpolate these data when I enter a price value of 80.

Any formula is also fine.

Please help.

Thanks
Abdul

**patel45** · 10-08-2013, 08:31 AM

explain better please

**abduljaleel.mca** · 10-08-2013, 08:56 AM

Hi Patel,

I know these data points.

X 16 60 100 150 273
Y 267.47226 184.48218 160.82095 -55.55879 -557.21659

I would like to interpolate Y values when giving any values between specified Xvalue. For example what will be the Y value when X=20 OR what will be the Y value when X=127. If I assume correctly we need to use interpolation and that should consider the nearest points and calculate Y value.

Hope it is clear, please let me know.

Thanks
Abdul

**MrShorty** · 10-08-2013, 10:27 AM

Linear interpolation is a frequent discussion topic here. One of my first suggestions might be to put it into this site's search engine and study the solutions previously presented.

One of the easiest solutions in my opinion would be to use a spreadsheet (like my old version of QuattroPro) that has a built in linear interpolation function (@LINTERP).

The basic algorithm I use for linear interpolation looks something like this (spread over several cells as I am not fond of big, ugly, single cell formulas):

1) A lookup function to locate the interval containing "new_x" -- usually a MATCH() function
2) Four instances of the INDEX() functions to return the x and y points for the desired interval.
3) Then a formula to find new_y at new_x. Maybe the TREND() function or equivalent.

I have recently seen someone proposing variations of the PERCENTILE() function for linear interpolations. I have not studied these in detail to see exactly how it works.