On Fri, 9 Sep 2005 12:42:29 -0700, tskoglund
wrote:

Given a series of x and y data, how can I interpolate to find y given a value
of x based only on a line between the two adjacent points in the data series?
This would be like the TREND() function, only I don't want regression of the
entire data series, just the (x,y) data points immediately above and below
the input x value.

This seems such fundamental and essential function that I've been
exasperated for years that Excel doesn't have it or directions how to find
it. I realize that Excel calculates these individual lines every time it
connects the dots in a graph, but not having a function to achieve the same
result on a worksheet is puzzling. Am I missing something?


If X is your range of X's, Y your range of Y's, and NewX is the new value,
then:

=TREND(OFFSET(Y,MATCH(NewX,X),0,-2),OFFSET(X,MATCH(NewX,X),0,-2),NewX)

This formula assumes your X's are in ascending order.

This formula will give an #N/A error if NewX is less than the minimum X.

This formula will give a #VALUE! error if NewX is equal to or greater than the
maximum X.

These errors are in accord with your specifications that the New X be "between"
two adjacent points, but I would eliminate the error when it matches the
maximum X with this addition to the above formula:

=IF(NewX=MAX(X),INDEX(Y,MATCH(NewX,X)),
TREND(OFFSET(Y,MATCH(NewX,X),0,-2),
OFFSET(X,MATCH(NewX,X),0,-2),NewX))


--ron