Linear Interpolation

[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?

 

[Jarek Kujawa]

try LINEST function

 

[Harish]

Not workin for me

 

[Jarek Kujawa]

maybe this will be helpful

 

[Jarek Kujawa]

or this

http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html

 

[Bernd P]

Hello Harish,

I suggest to use my UDF interp:
http://www.sulprobil.com/html/interpolate.html

Regards,
Bernd

 

[JoeU2004]

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 -----

 

[JoeU2004]

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 -----

 

[Herbert Seidenberg]

Excel 2007
This is a sigmoid function,
which Excel does not model.
Used Gompertz function at ZunZun.com to curve fit.
http://www.mediafire.com/file/wmk2mirtyom/03_28_09.xlsx

 

[Lori Miller]

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

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

 

[Bernd P]

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

 

[Lori Miller]

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.

 

[Herbert Seidenberg]

The SHGC data is measured data
and not based on a mathematical function.
I copied four of 11822 measurements of
different types of glazing from
http://windows.lbl.gov/software/window/window.html
If all the data points to 3 significant digits are used
and fitted to the Gompertz function,
the fit is extremely good.
R-squared= 0.999999
Here is the updated file:
http://www.mediafire.com/file/wmdjmlygmmw/03_28_09.xlsx

 

[Bernd P]

Hello Lori,

I honestly think that if your approach is published without any
information about its restrictions users are being lead up the garden
path.

I took the liberty to publish this as a bad example:
http://www.sulprobil.com/html/interpolate.html

Regards,
Bernd

 

[Lori Miller]

We'll let the OP decide what's best...

I have little to add other than refer to Harlan's comments on this:
http://groups.google.com/group/micr...read/thread/3c0ea0a9229d550c/e4bc40ca23ed8331

RE: sigmoid function. If you want to use a smooth function, try a
cubic spline then R²=1 exactly and you can set SHGC=0 at A=90
as in the diagram giving better behavior at the boundary.

 

[Bernd P]

Hello Lorimer,

Thanks. I included a link to that thread on my webpage.

To give your approach the right but limited credit:
http://www.sulprobil.com/html/poor_man_s_interpolation.html

IMHO all formulas should inform their user about their limitations
and, if they cannot provide a correct result, they should return with
an error message.

Regards,
Bernd

 

[Lori Miller]

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.)