Linear interpolation within a range of values

[nielsb]

Hi there,
I'm in search for a formula that can perform linear interpolation between within arange of values.

The case is best illustrated in the attached sheet. The formula need to be entered in the yellow cells (row 7), and should return the interpolated sieve diameter value for the percentages stipulated in row 6
For example; the value in cell D7; should return a value which lies in between J2 and K2 (closer to the J2 value).

Hope anybody knows how to work this.

Kind regards,
Niels

[MrShorty]

If you are not limited to Excel, Gnumeric has a built in INTERPOLATION() function that will make short work of this. =INTERPOLATION($B$3:$N$3,$B$2:$N$2,B6) copied across.

If you are limited to Excel, I usually recommend something like these for linear interpolation:
https://www.excelforum.com/excel-for...ml#post5262186
https://www.excelforum.com/excel-for...ml#post4873049

If you do this often enough, you might be interested in a VBA UDF. This thread has the beginnings of a linear interpolation UDF that could be used as a starting point: https://www.excelforum.com/excel-pro...-computer.html

[RChad]

Hi,

I restructured your setup see Sheet 2 and try this formula:

Please Login or Register  to view this content.

Press CTRL+SHIFT+ENTER to make it an array

[joeu2004]

Ostensibly, you might enter =FORECAST(B6,$B$2:$N$2,$B$3:$N$3) into B7 and copy across into C7:J7.

But that gives a very poor interpolation because the linear regression is a poor fit to the data in B2:N3. That is demonstrated by the fact that =RSQ(B3:N3,B2:N2) returns about 0.4064. A "good" fit would be closer to 0.9000 or above.

This would be true for all other dependencies on the linear regression, e.g. it INTERCERPT and SLOPE.

I think a pairwise interpolation would give you better results. One way to calculate that is to enter the following formula into B7 and copy across:

=FORECAST(B6, OFFSET($B$2,0,MATCH(B6,$B$3:$N$3)-1,1,2), OFFSET($B$3,0,MATCH(B6,$B$3:$N$3)-1,1,2))

You can confirm the correctness of the formula by entering formulas of the form =FORECAST(J6,M2:N2,M3:N3) in J8, for example.

I use OFFSET here for simplicity. But that is a "volatile" function. Consequently, that formula and any cells that depend on it directly or indirectly are recalculated whenever any cell in the workbook is edited or Excel otherwise chooses to recalculate. That can cause huge performance problems if you have many hundreds or thousands of such formulas.

Alternatively, I would replace OFFSET with references of the form INDEX(...):INDEX(...). But that can be messy and inefficient because of the multiple MATCH expressions. It would be better to put the MATCH formula into a "helper" cell, and reference it in the INDEX:INDEX expressions.

[nielsb]

Joeu2004,
many thanks! Your formula in red is exactly what I was looking for!

[nielsb]

@RChad; your formula comes close, but it lacks interpolating in between the values given. The formula from the Joeu2004 does the trick anyhow thanks for your effort.

[BMV]

But that can be messy and inefficient because of the multiple MATCH expressions.

its better then volatile

Please Login or Register  to view this content.