Sliding Scale Percentage via IF?
 

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),SU M(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).

Sliding Scale Percentage via IF?
 

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

"JadewindFalcon" wrote:

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),SU M(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).

Sliding Scale Percentage via IF?
 

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?

"sjk153" wrote:

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

"JadewindFalcon" wrote:

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),SU M(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).

Sliding Scale Percentage via IF?
 

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.

"JadewindFalcon" wrote:

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?

"sjk153" wrote:

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

"JadewindFalcon" wrote:

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),SU M(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).

Sliding Scale Percentage via IF?
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"JadewindFalcon" wrote in message
...
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?

"sjk153" wrote:

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

"JadewindFalcon" wrote:

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),SU M(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).