Interpolation function for Excel
When you have a number in cell A1, say it is "10" and then another number in cell J1 and it is "100" it would be great to have a function to do linear interpolation (lerp) between the two numbers without having to resort to long and complicated formula that is easy to get wrong.
=LERP(startvaluecell, endvaluecell) for a simple useage
=LERP2(startvalue, rangefirstcell, endvalue, rangeendcell)
So using LERP and my example:
A1= "10" , B1=LERP(A1, J1), C1=LERP(A1,J1), D1=LERP(A1,J1),…I1=LEPR(A1, J1), J1="100"
SO USING LERP2 and my example
A1="10", J1="100" B1 to I1 blank
A2=LERP2($A1, $A2, $I1, $I2) select this cell and fill to right across to J2. Done.
Optionally add a function option to make it non linear using a choice of a few built in curves (Sin, Exp, Quadratic, etc, etc)
The universal formula to solve this without this LERP function looks like this:
=$A6+(COLUMN(B7)-COLUMN($A6)) * ($J6-$A6)/(COLUMN(J1)-COLUMN(A1))
Pretty unintelligible, (like most Excel stuff maybe)
Or call it Curve or Interpolation:
=INTERPOLATION (range, [start-value], [end-value])
where range is a single column wide or row high range of cells of three or more cells.
start value and end value optional, if not provided then function assumes first cell in range specified is start value and last cell in range is end value for the linear Interpolation.
=CURVE (range, [start-value], [end-value], [curve-index])
Where curve index determines the function used to map the interpolation
e.g. 0 = linear
1 = sine
2 = quadratic
3 = cubic
4 = exponential
Here is MS Office advice on lerp
DIY basically as I have in my example file/