Sliding Scale Percentage via IF?

[Guest]

Hello all,

I'm trying to do a formula for a sliding scale percentage fee, for which it
references the item price of an object. Ie: First $25.00 = 10%, $25.01-100 =
10% of first $25 + 7% of remaining price, $100.01-1000 = 10% of first $25, 7%
of first $25.01-100, and 5% of remaining price, etc.

However! The formula I cobbled together won't debug correctly - it works up
to the 7% range, but trying to get that third jump to the 5% and beyond
returns a value of 'FALSE'. I can see what's happening in the formula
evaluator, but I'm at a loss on how to side-step it.

Formula:

=IF(B8<25.01,B8*0.1,IF(AND(B8>=25.01,B8<100.01),SUM(2.5,(B8-25)*0.07,IF(AND(B8>=100.01,B8<1000.01)
SUM(2.5,5.25,(B8-100)*0.05),IF(B8>=1000.01,SUM(2.5,5.25,(B8-100)*0.05,(B8-1000)*0.03),)))))

Broke the line, or else it would've stretched the screen quite wide. The
value in B8 is $180. So when it gets to the second part of the first AND
argument, it returns a FALSE and the rest of the formula is moot, and I feel
sure the same would happen for items in the $1000+ range as well. But for
items less than $100, so far it works. So I'd need another way of linking
those two conditions together and returning the amount of the fee seperate
from the initial price (in this case $11.75 if my hand-calculations are
accurate).

 

[Guest]

Try this...just revesed the order but it seems to work in my spreadsheet.

=IF(AND(B8>=100.01,B8<1000.01),SUM(7.75,(B8-100)*0.05),IF(B8>=1000.01,SUM(2.5,5.25,45,(B8-1000)*0.03),IF(B8<25.01,B8*0.1,IF(AND(B8>=25.01,B8<100.01),SUM(2.5+(B8-25)*0.07)))))

 

[Guest]

That worked like a charm. Thank you! Was that the proper way to approach that
particular issue, or was it more of a 'sledgehammer instead of a scalpel'
method?

 

[Guest]

have to be honest...just luck!! I broke down the formula in pieces to ensure
each if worked on its own then put them back together in reverse order---and
it worked. Glad I could help.

 

[RagDyer]

You could try this formula, which is not really a "scalpel", but it also is
*not* as intuitive, although it is a bit more concise:

=SUMPRODUCT((B8>{0;25.01;100.01;1000.01})*(B8-{0;25.01;100.01;1000.01})*({0.1;-0.03;-0.02;-0.02}))