Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Logical Function - Multiple IF statements
Can anyone help me figure out a formula for the following sliding bonus
scenario: To receive a bonus, Gross Profit must be at least $250,000 Once minimum is met, bonuses are calculated as a percent of the Adjusted GP (GP minus $250,000) as follows: If Actual GP is under $500,000: 3% of Adjusted GP If Actual GP is $500,000 up to $750,000: 3% of first $250,000 4% of balance of Adjusted GP If Actual GP is over $750,000: 3% of first $250,000 4% of second $250,000 4.5% of balance of Adjusted GP Thanks!! |
#2
|
|||
|
|||
See here for an example:
http://www.mcgimpsey.com/excel/variablerate.html HTH Jason Atlanta, GA -----Original Message----- Can anyone help me figure out a formula for the following sliding bonus scenario: To receive a bonus, Gross Profit must be at least $250,000 Once minimum is met, bonuses are calculated as a percent of the Adjusted GP (GP minus $250,000) as follows: If Actual GP is under $500,000: 3% of Adjusted GP If Actual GP is $500,000 up to $750,000: 3% of first $250,000 4% of balance of Adjusted GP If Actual GP is over $750,000: 3% of first $250,000 4% of second $250,000 4.5% of balance of Adjusted GP Thanks!! . |
#3
|
|||
|
|||
Thanks, but I don't think the SUMPRODUCT function will work because I'm not
going to have the data listed... In my spreadsheet, a person will type in the Actual Gross Profit in cell D29 and then the result of the calculation (where the formula is) will be in cell D31. "Jason Morin" wrote: See here for an example: http://www.mcgimpsey.com/excel/variablerate.html HTH Jason Atlanta, GA -----Original Message----- Can anyone help me figure out a formula for the following sliding bonus scenario: To receive a bonus, Gross Profit must be at least $250,000 Once minimum is met, bonuses are calculated as a percent of the Adjusted GP (GP minus $250,000) as follows: If Actual GP is under $500,000: 3% of Adjusted GP If Actual GP is $500,000 up to $750,000: 3% of first $250,000 4% of balance of Adjusted GP If Actual GP is over $750,000: 3% of first $250,000 4% of second $250,000 4.5% of balance of Adjusted GP Thanks!! . |
#4
|
|||
|
|||
Did you try it?
The whole article is based on entering your AGP in one cell... Your example is a rather straightforward application of the first technique shown: =IF(D29=250000,SUMPRODUCT(--(D29{0,500000,750000}), (D29-{0,500000,750000}),{0.03,0.01,0.005}),0) The remaining techniques make things more flexible, but aren't required. In article , "vnsrod2000" wrote: Thanks, but I don't think the SUMPRODUCT function will work because I'm not going to have the data listed... In my spreadsheet, a person will type in the Actual Gross Profit in cell D29 and then the result of the calculation (where the formula is) will be in cell D31. |
#5
|
|||
|
|||
Sorry, I see now that it is applicable...However, maybe because I'm not as
savvy with the formulas, I cannot get it to work. The one you include doesn't calculate properly(if it was intended to). If actual GP is $850,000, then the result should be $22,000. Any further assistance is appreicated....Thanks. "JE McGimpsey" wrote: Did you try it? The whole article is based on entering your AGP in one cell... Your example is a rather straightforward application of the first technique shown: =IF(D29=250000,SUMPRODUCT(--(D29{0,500000,750000}), (D29-{0,500000,750000}),{0.03,0.01,0.005}),0) The remaining techniques make things more flexible, but aren't required. In article , "vnsrod2000" wrote: Thanks, but I don't think the SUMPRODUCT function will work because I'm not going to have the data listed... In my spreadsheet, a person will type in the Actual Gross Profit in cell D29 and then the result of the calculation (where the formula is) will be in cell D31. |
#6
|
|||
|
|||
vnsrod2000 wrote...
Sorry, I see now that it is applicable...However, maybe because I'm not as savvy with the formulas, I cannot get it to work. The one you include doesn't calculate properly(if it was intended to). If actual GP is $850,000, then the result should be $22,000. Any further assistance is appreicated....Thanks. .... JE may have been a little hasty in his specific formula, but the technique is sound. Try =SUMPRODUCT(--(GP{250,500,750}*1000),GP-{250,500,750}*1000,{0.03,0.01,0.005}) |
#7
|
|||
|
|||
Seems to be working brilliantly!
Thanks Harlan Thanks JE Thanks Jason "Harlan Grove" wrote: vnsrod2000 wrote... Sorry, I see now that it is applicable...However, maybe because I'm not as savvy with the formulas, I cannot get it to work. The one you include doesn't calculate properly(if it was intended to). If actual GP is $850,000, then the result should be $22,000. Any further assistance is appreicated....Thanks. .... JE may have been a little hasty in his specific formula, but the technique is sound. Try =SUMPRODUCT(--(GP{250,500,750}*1000),GP-{250,500,750}*1000,{0.03,0.01,0.005}) |
#8
|
|||
|
|||
In article . com,
"Harlan Grove" wrote: JE may have been a little hasty in his specific formula Indeed. Thanks for the correction! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf Function using multiple criteria | Excel Worksheet Functions | |||
Multiple OR function | Excel Worksheet Functions | |||
Multiple Worksheets and Print Merge function | Excel Discussion (Misc queries) | |||
using logical functions as criteria with the SUMIF function | Excel Worksheet Functions | |||
multiple IF statements | Excel Worksheet Functions |