This is the equation for interpolation that I use. The formula may be off
one or two cells as I ripped it from a larger spread sheet.


A B
1 X Y Unknown Y value
2 1 5 Y= 10
3 2
4 3 15 (ABS((((A2-A3)/(A2-A4))*(B2-B4)))+B2)
Good luck!

Ron

"Michael Plog" wrote:

Steve, you have the classic condition here for interpolation. First,
you have a curvilinear regression happening, not a straight
correlation. So, the traditional slope formula will not work. Your
data set shows a nice progression except for one "bump."

Anyway, interpolation is fairly simple to explain and you can probably
get the formula set up better than I could. Look at the two points on
your data set from 14000-20000. That is a range of 6000. The
corresponding Y values are 10.68 to 19.51, a spread of 8.83. If you
change X by 6000 you change Y by 8.83. Now, if you change X by 3000,
you change Y by 4.415. If you change X by 1000, you change Y by 1.47.
(This is 8.83 divided by 6.)

The basic idea of interpolation is fairly simple. You assume the
connection between two known points on your scale is a straight line.
You assume the slope of the line changes with each two points known
points (e.g., 1400-2075 or 3450-4150). You need to calculate the
range from one X point to the next X point. Calculate the range from
one Y point to the next Y point. Then, get the relationship between
them for a unit change in the X series. For your "new" X point,
calculate the expected Y value.

Hope this helps. I did not give you a formula--I am not that
proficient in all of Excel's instructions; would use a different
programming language for something like this.




"Steve J. Vaughan" wrote in
message ...
Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give
the same
value of Y 19.51