Forum Discussion

Tyler Smith's avatar
Tyler Smith
Copper Contributor
Feb 12, 2018

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

Formulas & Functions
Need Help
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 13, 2018

    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

     

Resources