I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

MiniMax Interpolation

Alexander Wolff 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]

20100222_PB_01_MiniMax_Interpolation

If you are interested in downloading a 143 KB Excel 2010 sample file which was used to create the examples on this page go to my Download page, please.

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.

20100224_PB_02_MiniMax_Interpolation

Now an example for which you cannot apply this solution:

20100224_PB_03_MiniMax_Interpolation

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:

20110129_PB_01_sbMinimax_Interpolation_Screen

This semi-automated approach is included in the sample file mentioned above. 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]

Sulprobil   Get it done   Contact   Disclaimer   Download