Calculating Value Based on Multiple Criteria

Copper Contributor

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!

 

Capture.JPG

 

 

 

 

 

 

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)

 

ddfd.JPG

 

 

 

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

1 Reply

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,

  1. edit the range of index formula if a new fixed cost condition/any volume and unit combination; and
  2. edit the range in match formula if a new fixed cost condition; and
  3. edit the range of sumproduct formula if any new volume and unit combinations