excel: how do i interpolate in an x-y data series?

[barqqing]

I have a set of x-y data, where each pair are non-integers (generally) and
the relationship is monotonic.

I want to be able to enter an x value, and have excel find a corresponding y
value based on the data.

The x value argument is unlikely to correspond exactly to an x in the
dataset, so I want the function to interpolate between the x values itfalls
between to find a y-value.

Is there such a function?

 

[Lori]

No built-in interpolate function. For jointly increasing data try:

=PERCENTILE(B:B,PERCENTRANK(A:A,C1,308))

where C1 is the value to interpolate from the first two columns.

where columns A an

 

[Stan Brown]

Tue, 15 May 2007 22:30:00 -0700 from barqqing

I have a set of x-y data, where each pair are non-integers (generally) and
the relationship is monotonic.

I want to be able to enter an x value, and have excel find a corresponding y
value based on the data.

The x value argument is unlikely to correspond exactly to an x in the
dataset, so I want the function to interpolate between the x values itfalls
between to find a y-value.

Is there such a function?

Is the relationship merely monotonic, or does it fit a straight line
reasonably closely? If it fits a straight line, then that line has a
slope m and an intercept b, and for any given x value that you enter
the corresponding interpolated y is equal to m*x+b.

Suppose the known X's are in A1:A9 and the known y's in B1:B9. Then m
is =SLOPE(B1:B9,A1:A9) -- put that in say C1. The intercept is
=INTERCEPT(B1:B9,A1:A9) -- put that in say C2. Put your known x value
in say C4, and the corresponding interpolated y value is =C1*C4+C2.

Now, if the general x-y relationship isn't roughly a straight line,
the above technique won't work. To do the job right in that case,
you'll need to do some curve fitting.

 

[Lori]

Original post was truncated:

[...] columns A and B contain the x and y values respectively. In
general try array-entered (evaluate with ctrl+shift+enter):

=FORECAST(C1,B1:B10,IF((MATCH(C1,A:A)-A1:A10+0.5)^2<1,A1:A10))