Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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). |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying percentage in ranges in pivot table | Excel Worksheet Functions | |||
Percentage Commission on a sliding scale. | Excel Discussion (Misc queries) | |||
Changing the scale major unit in a graph | Excel Discussion (Misc queries) | |||
calculating commission on sliding scale | New Users to Excel | |||
can I calculate S&H on a sliding scale in an order form? | Excel Worksheet Functions |