Varying % based on $ amount

[etcioffi]

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

[#2]

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

[#3 · Accepted solution]

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

[#4]

Perfect thank you!! @mathetes