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)
Excel would be enhanced by an interpolation function that allows a column of values representing a function of independent variables listed in one column to be interpolated as a function of differently spaced independent variables listed in a third column. An important example is comparison of theory and experiment. From the experiment, values are measured at fixed time increments. From the theory, values are calculated at different time increments. To compare theory and experiment one would like to have both experimental and theoretical values expressed as functions having the same time increments.
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/