“The man of knowledge must be able not only to love his enemies but also to hate his friends.” [Friedrich Nietzsche]

Abstract

Alexander W. came up with yet another interpolation formula for problems which you can often encounter with taxes or other fee structures:

=MIN(C6%%*{110,70,35,25}+{0,400,1275,1775},3212.5)

See below, this would result in 2150:

How do you calculate his function parameters and what are the prerequisites or the conditions under which his function can be applied? His formula can be applied if and only if the slopes between adjacent points are monotonously increasing or decreasing. For the function parameters refer to the example file mentioned above, please.

Now an example for which you cannot apply this solution:

If this approach is applicable it is pretty fast. But if the number of interpolations and the number of given points increase (let us say some hundreds) then the runtime of this approach is not considerably faster than my UDF Interp.

I recommend to use the Minimax approach not with a manual calculation but with a (semi-)automated one:

These are the necessary worksheet formulas:

C3 =IF(A3<>"",(B3-B2)/(A3-A2),"") [copy down as far as necessary]
D3 =IF(A3<>"",B2-C3*A2,"") [copy down]
E2 =COUNT(A:A)
E4 =AND(C3:INDEX(C:C,E2)<=C4:INDEX(C:C,E2+1)) [Array formula]
E6 =AND(C3:INDEX(C:C,E2)>=C4:INDEX(C:C,E2+1)) [Array formula]
F4 =REPT("=MAX(F2*{"&sbCat(C3:INDEX(C:C,E2+1))&"}+{"&sbCat(D3:INDEX(D:D,E2+1))&"})",E4)
F6 =REPT("=MIN(F2*{"&sbCat(C3:INDEX(C:C,E2+1))&"}+{"&sbCat(D3:INDEX(D:D,E2+1))&"})",E6)
F7 =REPT("No solution: slopes need to increase or to decrease monotonously!",NOT(E4+E6))
F9 =IF(E4,sbEval(F4),IF(E6,sbEval(F6),"")) [sbEval is similar to sbLockedFormula]

Please read my Disclaimer.

MiniMax_Interpolation.xlsm [60 KB Excel file, open and use at your own risk]