PROBLEM

There are 10 Sales Representatives.

They all are eligible for a commission which is basis their performance.

For example, Darren Powers is entitled to 10% Bonus since his sales is in the 0-5000 range.


So his Bonus = 10% * 1000 = 100

Total Earning = 1100

001.png

solution 1

Using nested IF statement would be very complicated here, as the formula will look like this!

=IF(C6<5001,C6*(1+$H$6),IF(C6<20001,C6*(1+$H$7),IF(C6<35001,C6*(1+$H$8),IF(C6<38001,C6*(1+$H$9),C6*(1+$H$10)))))

002.png

These are the results:

003.png

solution 2

Using VLOOKUP with Approximate Match will reduce this effort significantly.

First, we create the minimum Sales amount required for each Bonus tier.

 

This is written in Column F, in cells F6:F10.

004.png

Now we can write a VLOOKUP function:

=C6*(1+VLOOKUP(C6,$F$6:$H$10,3,TRUE))

005.png

We get the same results:

006.png

SOLUTION 3
[ADVANCED BONUS TIP]

You can get the same results even WITHOUT the lookup table – by directly creating the Table Array inside the function itself.

 

The benefit here is that the function is NOT dependent on any external data.

007.png

The Formula used is:

=C6*(1+VLOOKUP(C6,{0,0.1;5001,0.15;20001,0.2;35001,0.25;38001,0.3},2,TRUE))

We get the same results:

008.png

GET SAMPLE FILE AND SOLUTION

Send an email at:

havish@havishmconsulting.com 

  • LinkedIn
  • YouTube
  • Instagram

©2020 by HAVISH M. CONSULTING. Proudly created with Wix.com