Logical Function - Multiple IF statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
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.
 
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.
 
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.
 
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.
Click to expand...
....

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multipl IF Statements - REVISION 1
Logical Function 2
IF Statement 4
IF IS NULL PROBLEM 3
gross profit 2
IF(AND) function? 5
Data format coversion in Excel (long) 13
nested if statements - need help 9

Back
Top