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% ...
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.

     

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