Clever formula

[kellta]

Hello

Can anyone help me write a single formula that will solve for the total fee to be deducted given this fee card:

First 100 - 1.25%
Next 200 - 1.00%
Next 400 - 0.75%
Balance above 700 - 0.50%


I have only been able to do it so far using a range of cells.

Many thanks
Tom

 

[Andrew Poulsom]

Look here:

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

 

[TBP]

Both gaz_chops and Andrew Poulsom's ideas

=LOOKUP(A1,{0,100,300,700},{0,1.25,3.25,6.25})+(A1-LOOKUP(A1,{0,100,300,700}))*LOOKUP(A1,{0,100,300,700},{0.0125,0.01,0.0075,0.005})

=SUMPRODUCT(--(A1>{0;100;300;700}),(A1-{0;100;300;700}),{0.0125;-0.0025;-0.0025;-0.0025})

 

[Kaper]

Just in case - It seems you missed my answer (or at least not commented on it), while I still think what I originaly posted on first page of this thread:

=if(a1<=100,a1*1.25%,if(a1<=300,(a1-100)*1%+1.25,if(a1<=700,(a1-300)*0.75%+3.25,(a1-700*0.5%+6.25))))

follows the right pattern.

of course 1.25 in second if is 1.25%*100, 3.25 in second if is 1.25%*100+1%*200, and final 6.25 is 1.25%*100+1%*200+0.75%*400