Forum Discussion
Multiply formula with multiple criteria
I need a formula that allows me to calculate commission based off of several scenarios. If they have less than 3 deals (let's say in cell A1 to make simple) than I need to multiply their revenue (in cell A2) by .75, if over 3 deals, AND their revenue is under $75K then multiply by .80, if over 3 deals, AND their revenue is between 76K-100K then multiply by .85, if over 3 deals, AND their revenue is over $100K then multiply by .90. I've tried using IF's, PRODUCT formulas, can anyone help me with this please?
| TOTAL DEALS | TOTAL REVENUE FOR AGENT |
| 3 | $ 44,250.00 |
=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))
5 Replies
=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))
HansVogelaar How would you adjust this if you wanted to grab the data/numbers in bold from actual cells instead so that if we ever had to change the percentages or numbers it would automatically update? Like a plug and play scenario I guess. Is that possible?
=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))
See the attached sample workbook. I used two cells for the 3 and 0.75, and a table for the other values.
You can edit the values, and if necessary expand the table. The formula will take the changes into account.
AMAZING!!! Thank you so much! That was driving me crazy!
