Forum Discussion
Calculating Value Based on Multiple Criteria
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 func...
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,
- edit the range of index formula if a new fixed cost condition/any volume and unit combination; and
- edit the range in match formula if a new fixed cost condition; and
- edit the range of sumproduct formula if any new volume and unit combinations