Linear Interpolation

  • Thread starter Thread starter Harish
  • Start date Start date
H

Harish

Hi,

I have a table:

A SHGC
0 0.86
40 0.84
50 0.82
60 0.78
70 0.67
80 0.42

For example, I want to find the SHGC value for the corresponding A
value of 27.662, How can i use a function in excel to do that
interpolation?
 
The following provides a __linear__ interpolation between two data points.
If you chart your data, you will see that it is not linear. However,
without an equation for the data, you cannot interpolate along the curve. A
linear interpolation might be sufficient if the known data points are close
enough.

The formula for interpolating (x2,y2) between (x1,y1) and (x2,y2) is:
y1 + (y3-y1)*(x2-x1)/(x3-x1)

If your data are in A2:B7 and the intermediate data point (27.662) is in D2,
then the corresponding SHGC value is:

=VLOOKUP(D2,$A$2:$B$7,2) +
(INDEX($B$2:$B$7,1+MATCH(D2,$A$2:$A$7)) - VLOOKUP(D2,$A$2:$B$7,2)) *
(D2 - VLOOKUP(D2,$A$2:$A$7,1)) /
(INDEX($A$2:$A$7,1+MATCH(D2,$A$2:$A$7)) - VLOOKUP(D2,$A$2:$A$7,1))

It would be more efficient if you computed the repeated INDEX and VLOOKUP
functions in helper cells, say F2:G2 for x1 and x3 and H2:I2 for y1 and y3.
If you do that, then you can use the TREND function as follows:

=TREND(H2:I2,F2:G2,D2)


----- original message -----
 
Better....

=FORECAST(D2,
OFFSET($B$1,MATCH(D2,$A$2:$A$7),0,2,1),
OFFSET($A$1,MATCH(D2,$A$2:$A$7),0,2,1))

And of course, you could compute the MATCH() value in a helper cell.


----- original message -----
 
=PERCENTILE(B:B,1-PERCENTRANK(A:A,D2,30))

with data in first two columns eg D2=27.662 gives 0.846
 
Hello,

Lori's formula only works if values in A are increasing and if values
in B are decreasing (if B values are increasing we could drop the
"1-"). And its not extrapolating (ok, that was not asked for).

IMHO Lori's approach as well as Joe's one are outright dangerous,
Herbert's is a perfect 110% solution for this special case but he
documented his approach very well so that we can apply it generally, I
think.

Regards,
Bernd
 
The data relate to how the solar heat gain coefficient varies with angle.
A sigmoidal model may fit the data given well but there is little physical
reason to use that function, articles on the subject tend to use linear
transformations of cos(A) eg see (3) in
http://gaia.lbl.gov/btech/papers/37747.pdf

i don't agree there is anything dangerous about either formula. There is
definitely a decreasing relation for 0<A<90 and so we can make use of a
simple formula. Looking through the archives, nearly all interpolation
examples posted have had a 1-to-1 relation either increasing or decreasing -
those that dont are generally better estimated by other means.
(This was discussed in another post i recall).

Extrapolation, however, can be very misleading when based on only two
data points, much better to find a suitable regression model, as here, if
you're
needing to estimate values outside the data range.
 
Bernd - Thanks for updating this.

Just a point regarding objectivity of posts and web links. You're right
to point out limitations here, but i believe in letting users make up
their own minds based on individual merit without too much opinion.

In fact most formulas provided by posters will not return correct
results in all situations, instead they are balanced to the needs of
the given problem, too much error checking makes them unwieldy and
harder to understand. For example all formulas using match/lookup
functions for nonexact matches can return incorrect results if data is
not arranged appropriately.

I think most would agree extrapolation should be turned off by default
anyway, eg forecasting tomorrows FTSE based on yesterdays movement
is misguided, but that it can be occasionally useful.

Excel solutions are often designed around expediency and a RAD mentality.
The emphasis is usually on getting results - it's often impractical
to try and account for every conceivable turn of events along the way -
instead time is always spent on thoroughly checking the answer.
(this is how it works on financial services trading floors anyway.)
 
Back
Top