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?

try LINEST function


On 28 Mar, 04:00, Harish wrote:
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?

Not workin for me

maybe this will be helpful

http://www.youtube.com/watch?v=ECA2VSOhbuU



On 28 Mar, 05:11, Harish wrote:
Not workin for me

or this

http://www.tushar-mehta.com/excel/ne...ion/index.html



On 28 Mar, 05:32, Jarek Kujawa wrote:
maybe this will be helpful

http://www.youtube.com/watch?v=ECA2VSOhbuU

On 28 Mar, 05:11, Harish wrote:



Not workin for me- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Hello Harish,

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

Regards,
Bernd

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

"Harish" wrote in message
...
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?

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

"JoeU2004" wrote in message
...
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 -----

"Harish" wrote in message
...
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?

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

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

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

"Harish" wrote in message
...
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?

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 P" wrote in message
...
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 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

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

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/micro...bc40ca23ed8331

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" wrote in message
...
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

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_m...rpolation.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

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



"Bernd P" wrote in message
...
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_m...rpolation.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