Feb 12 2018
01:26 PM
- last edited on
Jul 25 2018
11:02 AM
by
TechCommunityAP
Feb 12 2018
01:26 PM
- last edited on
Jul 25 2018
11:02 AM
by
TechCommunityAP
I am working on a spreadsheet that calculates a bonus based on multiple values: fixed cost, units and volume. I have included the chart in which the bonus is calculated. I am able to use an if function to calculate based on volume and fixed cost, but adding units in as well is causing me great difficulty. Any help is greatly appreciated!
So a salesman who had $1,700,000 in volume, 6 units and $1,200 in fixed cost would have a $5,000 bonus.
This is an example of the spreadsheet I am using along with the formula (excluding units)
Formula: =IF(OR(B2<4,C2<500000),0)+IF(AND(C2>=500000,C2<1500000),IF(AND(D2<=2000,D2>=1001),5000,IF(AND(D2<=1000,D2>=0),7000)))+IF(AND(C2>=1500000,C2<3000000),IF(AND(D2<=2000,D2>=1001),8000,IF(AND(D2<=1000,D2>=0),12000)))
Feb 12 2018 05:38 PM - edited Feb 12 2018 07:31 PM
You may need to create the bonus table in a new worksheet, e.g. BonusSchema
A | B | C | |
1 | Minimum Volume | $500,000 | $1,500,000 |
2 | Minimum Units | 4 | 9 |
3 | Max Fixed Cost | ||
4 | $2,000 | $5,000 | $8,000 |
5 | $1,000 | $7,000 | $12,000 |
Formula:
=IFERROR(INDEX('BonusSchema'!$B$4:$C$5, MATCH(<COST>, 'BonusSchema'!$A$4:$A$5, -1), SUMPRODUCT(N(<Volume> >= 'BonusSchema'!$B$1:$C$1), N(<Unit> >= 'BonusSchema'!$B$2:$C$2)))
Then, if you have a new bonus schema,