MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Interpolation


Posted by Martin Söderstjerna on March 21, 2001 4:04 AM

Hello!
I´ve got what I think is an easy question about interpolation.
A B
1 0 0,4
2 0,1 0,35
3 0,2 0,31
4 0,3 0,27
5 0,4 0,23
6 0,5 0,20

I wold like Excel to return the value for column B if I now the value for column A. For example A=0,15 gives me B=0,33
Thanks!
Best regards
Martin


Posted by Mark W. on March 21, 2001 5:36 AM

Martin, I have a few questions about your posting.

1. I don't quite understand your data. Are the
comma separated values listed x,y coordinates for
points A and B?

2. If these are coordinates and how are we to
find B given only one point, A? Remember, it
takes 2 points to make a line.

3. Are you confusing interpolation with extrapolation?
Interpolation is an estimate of values between 2
known values which you don't appear to have.
Extrapolation extends a data set beyond known
values.

Posted by Martin Söderstjerna on March 21, 2001 5:57 AM

The values are not coordinates, I'll give you an new example. I've got an table with 40 X-values and the corresponding 40 G(X)-values.
X G(X)
0 40
1 35
2 31
3 28
4 26
I would like to have a function that delivers the G(X) value for all X-values between 0 and 4.
For example
X=0,3 G(X)=...
X=0,53 G(X)=...
X=3,89 G(X)=...

Regards
Martin

Posted by Martin Söderstjerna on March 21, 2001 6:03 AM

Posted by Mark W. on March 21, 2001 6:19 AM

Can you describe function G?

Posted by Mark W. on March 21, 2001 6:27 AM


> I would like to have a function that delivers
> the G(X) value for all X-values between 0 and 4.

...but, isn't there an infinite number of X-values
between 0 and 4?

Posted by Martin Söderstjerna on March 21, 2001 6:35 AM

G(X) is calculated from a normal distribution table, but it's enough if the interpolation is linear. I'll give you a new example:
IF X=10 then G(X)=8
X=12 then G(X)=9
I would like to determine the G(X) value if X=10,5.
Solution:
((10,5-10)/(12-10))*(9-8)+8 makes G(X)=8,25


Posted by Martin Söderstjerna on March 21, 2001 6:40 AM

:There are only 41 X-values (0.1, 0.2, 0.3,..., 3.8, 3.9, 4.0) and of course the corresponding 41 G(X) values.

Posted by Mark W. on March 21, 2001 6:46 AM

Okay, I just woke up! ; )

I just realized that the commas that you're using
are decimal symbols! This array formula should
do the trick:

{=TREND({8;9},{10;12},10.5)}

If you're not familiar with array formulas, they're
entered using the Control+Shift+Enter key
combination. The braces, {}, are not typed by
you -- they're an supplied by Excel as an
indication that you've entered an array formula.

Sorry, it took so long for me to wake up. I
think I'll go re-fill my coffee cup!