how to do linear interpolation

Trend performs a linear regression on the overall dataset, then finds the corresponding value.

Sometimes I think the easiest way to do this would be to buy QuattroPro and use the built in @LINTERP() function available in that spreadsheet application. Since this is an Excel forum, I probably should not say such a thing though, lest I be ousted from the community for speaking such heresy.

Since Excel does not offer a built in function, you need to build the different parts. Locate interval containing the desired unkown, then compute the linear trend for that interval to determine the desired unkown. If you are interested, I generated an example of one approach in this post: http://www.excelforum.com/excel-char...ml#post3904113 This example is in ln(y) vs ln(x) space, but it should adapt readily to regular y vs. x type problems. It consists of:"

1) a LINEST() function to determine slope and intercept for each pair of points.
2) a lookup function to return the slope and intercept specific to the desired unkown.
3) simple algebra to compute the desired unkown.

Another way:

Row\Col	A	B	C	D	E	F	G
2	X	62.0	66.2	70.0		62	F2: Input
3
4	Y	1939	2615	3416		1939	F4: =PERCENTILE(B4:D4, PERCENTRANK(B2:D2, F2, 6))

BTW, the formula I posted only works for a monotone ascending function. There's a different (similar) one for a descending function, and a different one yet (using a UDF) for non-monotone functions.