Forum Discussion

etcioffi's avatar
etcioffi
Copper Contributor
Nov 05, 2021
Solved

Varying % based on $ amount

Hi all,

 

I'm looking for any functions or formulas that can help calculate commission % points based on a $ goal.  

 

Ex:

 

Goal = $1000

 

100% to goal = 5% rate

97.5 to 99.9% of goal = 4% rate

95 to 97.4 of goal = 3% rate

 

etc, etc. 

 

Any help would be greatly appreciated!  Thank you

excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Nov 05, 2021

    etcioffi 

     

    An alternative solution would be to use a table and VLOOKUP as I've done in the attached sample sheet.

     

    Using a table enables you to change the rate very readily, without altering the formula.

     

3 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Nov 05, 2021

    etcioffi 

     

    An alternative solution would be to use a table and VLOOKUP as I've done in the attached sample sheet.

     

    Using a table enables you to change the rate very readily, without altering the formula.

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 05, 2021

    etcioffi 

    Perhaps with goal in A1 and achieved result in B1:

    =MIN(MAX(ROUNDDOWN((B1/A1-0.9)/2.5,2),0),0.04)

    Format the cell with the formula as a percentage.

Resources