Interpolate from a table?

  • Thread starter Thread starter israelica
  • Start date Start date
I

israelica

I'm working with this table:


H/W 0.25 0.5 0.75 1 1.5
Angle
20 0.08 0.08 0.08 0.07 0.07
30 0.18 0.17 0.17 0.16 0.15
45 0.38 0.37 0.36 0.34 0.33
60 0.6 0.59 0.57 0.55 0.52
75 0.89 0.87 0.84 0.81 0.77
90 1.3 1.3 1.2 1.2 1.1

I'm trying to develop a formula which will interpolate a value for when
I'm in between numbers. For example, if my angle is 25 degrees, and my
H/W is 1.25, then I would get 0.1125. There's gotta be a way to do
this. Any suggestions?
 
I have imported your data and plotted it to see what function they fit
to nicely. It seems that the best match is the quadratic that goes
through 0, i.e. f(x)=ax^2+bx, where x is the angle. The problem is that
a and b change with H/W, therefore we will assume a linear change of
both a and b with H/W. We thus seek to find another function:

f(x,y)=(ay+b)x^2+(cy+d)x, where x is angle and y is H/W.

We will use the Solver to find a, b, c and d with the Least Squares
method.

Use four cells, say A12:D12 to hold a, b, c and d. Initially you can
set them to 0.

Copy the entire data set to the right. Start from I1. Erase the values
and enter the following formula in place of the first 0.08 of the
copied data:

=($A$12*I$1+$B$12)*$H3^2+($C$12*I$1+$D$12)*$H3

Copy over the previously copied data.

In yet another area of the same dimensions, use a formula to calculate
the square error between your data and the estimated function:

=(B3-I3)^2

Copy this accross and down as far as the data area. Then produce the
sum of all these cells in yet another cell, say, M18. Now issue the
Solver:

Tools|Solver (if it does not appear in Tools menu, first Tools|Add
Ins... and choose SOlver Add in).

Set target cell: M18
By Changing cells: A12:D12
Solve

When you solve, A12:D12 will get the values of the above expression. In
my run they produced the following:

f(Angle, HW) =
(-0.0000199600635287658*HW+0.00014782591576092)*Angle^2+(0.000025289182495776*HW+0.00153502941872466)*Angle

Now, by supplying whatever values you want for Angle and H?W you can
get the interpolated value.

Does this help?

Kostis Vezerides
 
Back
Top