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

 

[SteveO59L]

something along the lines of

=if(a1<101,(a1*1.25)/100,if(a1<301,(a1*1.00)/100,if(a1<701,(a1*0.75)/100,(a1*0.5)/100))))

 

[gaz_chops]

Or maybe
=A1*LOOKUP(A1,{100,200,400,700},{0.0125,0.01,0.0075,0.005})

 

[Kaper]

I'd probably say:

=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))))

 

[gaz_chops]

Think i made a mistake!

=A1*LOOKUP(A1,{0,101,301,701},{0.0125,0.01,0.0075,0.005})

 

[kellta]

something along the lines of

=if(a1<101,(a1*1.25)/100,if(a1<301,(a1*1.00)/100,if(a1<701,(a1*0.75)/100,(a1*0.5)/100))))

Thanks
Not sure that this takes into account the Income tax style tiers.

I have done this but it is returning #VALUE!

=IF(A6>=C3,((A6-C3)*0.005)+62500),IF(A6>=C2,((A6-C2)*0.0075)+32500),IF(A6>=C1,((A6-C1)*0.01)+12500),IF(A6<=C1,A6*0.0125,"ERROR")

That is with different numbers.


Thanks a lot

 

[gaz_chops]

Did you try
=A6*LOOKUP(A6,{0,101,301,701},{0.0125,0.01,0.0075,0.005})

 

[gaz_chops]

Or with a table in D1:E4

=A6*LOOKUP(A6,D1:D4,E1:E4)

[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65, align: right"]0[/TD]
[TD="class: xl63, width: 65, align: right"]1.25%[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="class: xl63, align: right"]1.00%[/TD]
[/TR]
[TR]
[TD="align: right"]301[/TD]
[TD="class: xl64, align: right"]1%[/TD]
[/TR]
[TR]
[TD="align: right"]701[/TD]
[TD="class: xl63, align: right"]0.50%[/TD]
[/TR]
</tbody>[/TABLE]

 

[kellta]

Or with a table in D1:E4

=A6*LOOKUP(A6,D1:D4,E1:E4)

[TABLE="width: 130"]
<TBODY>[TR]
[TD="width: 65, align: right"][/TD]
[TD="class: xl63, width: 65, align: right"]1.25%
[/TD]
[/TR]
[TR]
[TD="align: right"]101
[/TD]
[TD="class: xl63, align: right"]1.00%
[/TD]
[/TR]
[TR]
[TD="align: right"]301
[/TD]
[TD="class: xl64, align: right"]1%
[/TD]
[/TR]
[TR]
[TD="align: right"]701
[/TD]
[TD="class: xl63, align: right"]0.50%
[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks

I'm not sure that the LOOKUP takes into account the fact that the first 100 is charged at 1.25% and the next x amount is charged at 1% (up until 300), and so on.


Thanks again

 

[gaz_chops]

Ah got you.

Thanks

I'm not sure that the LOOKUP takes into account the fact that the first 100 is charged at 1.25% and the next x amount is charged at 1% (up until 300), and so on.


Thanks again