Excel - Interpolation amidst a listing of values

  • Thread starter Thread starter TKT-Tang
  • Start date Start date
T

TKT-Tang

Amidst a long listing of multiple values,

X1 = 2.0, Y1 = 7.00 ;
X3 = 2.1, Y3 = 7.51 .

For X2 = 2.05, Y2 = ?

Please devise a worksheet formula in order to interpolate the values thereof.

Regards.
 
One way using FORECAST()

If the known x and y values are in cols A and B, row2 down, viz.:

Values of X1 and X3 are in A2:A3
Values of Y1 and Y3 are in B2:B3
X2's value of 2.05 is in A4

Try in B4: =FORECAST(A4,B2:B3,A2:A3)
which returns 7.255
 
Do fix the ranges for the known x and y values if copying down the col,
viz.:

In B4: =FORECAST(A4,$B$2:$B$3,$A$2:$A$3)
Copy B4 down
 
Thank you, everybody ; for responding to my query.

Amidst a long list of values, for whatever that has not been shown,
there are many others such as the following :-

X4 = 2.2, Y4 = 8.59 ;
X5 = 2.3, Y5 = 9.16 ; etc and etc, just to add a couple more.

The requirement of interpolation is to select the 2 sandwiching sets
of data by virtue of Excel automation ; rather than visual inspection
by the worksheet user.

The look-up value is thus to be located aptly straddling the
sandwiching sets (based on which the interpolation is returned).

And there, the question is, how to get in, snuggly fitting, to be
sandwiched and interpolated ?

Regards.
 
Assuming your x and y values are in cols A and B, row2 down
and that there is never more than 1 consecutive "in-between"
missing Y value to be interpolated ..
(x values are assumed complete in col A)

Put in C2:

=IF(B2<>"",B2,IF(AND(B2="",OFFSET(B2,-1,)<>"",OFFSET(B2,1,)<>""),B3-(A2-A1)*
(B3-B1)/(A3-A1),""))

Copy C2 down

Col C will return the existing y values from col B if present,
and return the interpolated y values for missing y values, if any

If there's more than 1 consecutive "in-between"
missing Y value to be interpolated, "blanks" will be returned
 
Mr. Max,

Thank you for responding to my query.

Back to square one again ; given a tabulation as follows :-

X Y
1.10 3.31
1.20 3.64
1.30 3.99
1.40 4.36
1.50 4.75
1.60 5.16
1.70 5.59
1.80 6.04
1.90 6.51
2.00 7.00
2.10 7.51
2.20 8.04
2.30 8.59
2.40 9.16
2.50 9.75
2.60 10.36
2.70 10.99
2.80 11.64
2.90 12.31
3.00 13.00
3.10 13.71

From elsewhere of a worksheet, I would like to jump right into the
tabulation ; holding the reference value, X2 = 2.05, i.e., the seed to
straddle,
X1 = 2.00, X3 = 2.10 ;

And there, sandwiched between Y1 = 7.00 and Y3 = 7.51 ; it would exude
the interpolated value of Y2.

But, how could one get there by riding on Excel automation ? If one
worksheet formula will suffice ......

Regards.
 
This might work ..

Assuming the X and Y values in your post are in cols A and B, row2 down

and you have a list of X values in col D to interpolate the Y values in D2
downwards

Put in E2:

=OFFSET($A$1,MATCH($D2,$A:$A,1),1)-($D2-OFFSET($A$1,MATCH($D2,$A:$A,1)-1,))*
(OFFSET($A$1,MATCH($D2,$A:$A,1),1)-OFFSET($A$1,MATCH($D2,$A:$A,1)-1,1))/(OFF
SET($A$1,MATCH($D2,$A:$A,1),)-OFFSET($A$1,MATCH($D2,$A:$A,1)-1,))

Copy E2 down

Col E will return the interpolated Y values for the X values in col D
(e.g.: if D2 = 2.05, E2 will return 7.255, the interpolated Y value for X =
2.05)
 
Just a clarification ..

The X values in col A are assumed to be in ascending order
(as indicated in your post)
 
Mr. Max,

Thank you for responding to my query.

The given worksheet formula has apparently worked well as given in
columns D and E.

However,

1. Copy the given formula (verbatim) to a location outside of columns
D and E, it does not work thereof.

2. When it is so required to insert (at the top of the worksheet) some
additional (entire) rows above the tabulation of the X and Y values,
the existing interpolation figures (as displayed in columns D and E by
the given formula) would be altered.

In future endeavour, the given formula would be applied gingerly.

Regards.
 
Mr. Max,
Thank you for responding to my query.
Click to expand...

You're welcome ! .. and there's no need for the "Mr said:
The given worksheet formula has apparently worked well as given in
columns D and E.

However,

1. Copy the given formula (verbatim) to a location outside of columns
D and E, it does not work thereof.
Click to expand...

hmm .. I'd usually do either a cut-paste or a "move", rather than a
copy-paste,
to let Excel automatically take care of adjustments to the formulas
2. When it is so required to insert (at the top of the worksheet) some
additional (entire) rows above the tabulation of the X and Y values,
the existing interpolation figures (as displayed in columns D and E by
the given formula) would be altered.
Click to expand...

Try this revised formula in E2
(with the known XY range now "fixed": $A$1:$A$22
replacing the previous entire col reference used: $A:$A)

=OFFSET($A$1,MATCH($D2,$A$1:$A$22,1),1)-($D2-OFFSET($A$1,MATCH($D2,$A$1:$A$2
2,1)-1,))*(OFFSET($A$1,MATCH($D2,$A$1:$A$22,1),1)-OFFSET($A$1,MATCH($D2,$A$1
:$A$22,1)-1,1))/(OFFSET($A$1,MATCH($D2,$A$1:$A$22,1),)-OFFSET($A$1,MATCH($D2
,$A$1:$A$22,1)-1,))

Copy E2 down as before

The above will now work properly even if rows were to be inserted
at the top of the sheet subsequently
 
Back
Top