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