How to have formula that adjusts depending on value in cell another sell? sliding commission structure

0
votes

Firstly, please accept my apologies for what is probably a very simple question....I'm trying to come up with a formula for our commission structure for our sales people.

Our commission structure has different scales of commission driving them to sell more.

Our quarterly commission structure is as follows:

  • Achieve between 90% and 99.99% - Commission is 1% of all sales
  • Achieve between 100% and 109.99% - commission is 1.5% of all sales
  • Achieve between 110% and 114.99% = Commission is 2% of all sales
  • Achieve between 115% and 119.99% = Commission is 3% of all sales
  • Achieve 120% = Commission is 4% of all sales
  • Receive 15% of all sales made over 120%, capped at a total of £3750

I would like to have one cell that calculates the commission based on what they have achieved according to the above structure. I have already completed the Cell G5 which would work out commission if they are hitting 90% or more, but I do not know how to make the formula adjust depending on the value in F5 (percentage achieved).

Any help would be greatly appreciated. The spreadsheet is here -

https://docs.google.com/spreadsheets/d/1EzssgLipdnlgbxaFEq0_yk2WnAgxW2SA5XoVf8UdAaM/edit?usp=sharing

Oli

2
excelexcel-formulaformulagoogle-docsformulas

2 Answers

0
votes

Make a helper table:

Achieve (lower bound) | Commission

Then in your formula use approximate VLOOKUP to get the Commission value based on Achieve in each row.

Like, if your Achieve actual values are in column D:D and you placed the abovementioned helper table on a separate Achieve worksheet, you need this formula:

=VLOOKUP($D1,Achieve!$A:$B,2,TRUE)
0
votes

You can use this:

=LOOKUP(A1;{0.9;1;1.1;1.15;1.2;1.2001};{0.01;0.015;0.02;0.03;0.04;0.15})

In this case A1 contains the percentage achived