Help with a kind of interpolation

[kcmuppet]

Hello,

In the example below, I'm calculating how much of a variable fall between each of 2 fixed points, which I think is a kind of interpolation (correct me if I'm wrong!).

Example: F3 = the difference between variable1 and the nearest next highest fixed point (fixed point 4) as a % of the difference between the two points that it falls between (fixed point 3 and fixed point 4)

Book2
	A	B	C	D	E	F	G	H	I	J
1				Fixed point 1	Fixed point 2	Fixed point 3	Fixed point 4	Fixed point 5	Fixed point 6	Fixed point 7
2	variable1=	96		85	90	95	100	105	110	115
3				0	0	0.8	0.2	0	0	0
sheet1

Cell Formulas
Range	Formula
D3	D3	=IF(variable1>=E$2,0,(E$2-variable1)/(E$2-D$2))
E3:I3	E3	=IF(OR(variable1<=D$2,variable1>=F$2),0,IF(variable1>E$2,(F$2-variable1)/(F$2-E$2),1-D3))
J3	J3	=IF(variable1<=I$2,0,1-I3)

Named Ranges
Name	Refers To	Cells
variable1	=sheet1!$B$2	D3:J3

While this solution works, it requires 3 different formulae in columns in the range (one in the middle and a different one at each end).

Is there a better way of doing this (where better = simpler formulae / single formula)?

 

[NateSC]

You can add an AND to check if the cell to the upper right is blank.

AND(variable1>=E$2,E$2<>"")

The original equation in E3 worked for D3, but didn't work in J3. The AND I added just makes it fail the IF statement so it goes ahead with the calculation. If your number is lower than D2, then D3 always shows a 1. If it is greater than J2, then J3 will give you a number greater than 1. You could add conditional formatting to highlight these cases so it is obvious your variable1 is outside the interpolation range.

Book1
	A	B	C	D	E	F	G	H	I	J
1				Fixed point 1	Fixed point 2	Fixed point 3	Fixed point 4	Fixed point 5	Fixed point 6	Fixed point 7
2	variable1=	115		85	90	95	100	105	110	115
3				0	0	0	0	0	0	1
Sheet1

Cell Formulas
Range	Formula
D3:J3	D3	=IF(OR(variable1<=C$2,AND(variable1>=E$2,E$2<>"")),0,IF(variable1>D$2,(E$2-variable1)/(E$2-D$2),1-C3))

Named Ranges
Name	Refers To	Cells
variable1	=Sheet1!$B$2	D3:J3

 

[kcmuppet]

Thank you