Forum Discussion
Having trouble calculating commission which commissions portions of a sale differently
Hey,
Trying I am having trouble calculating commission which commissions portions of a sale differently. For example, if you have a sale that is 9000 USD. The first 3500 gives a commission of 4%, the 3500 after it gives a commission of 4.5%, and anything after that is commissioned 5%. Can anyone show me, while utilizing the vlookup function, how to do this?
TIA
2 Replies
Create a small lookup list as in the screenshot:
Column I shows the increment from each tier to the next.
With a sales amount in A2, the commission is
=SUMPRODUCT((A2>$G$2:$G$4)*(A2-$G$2:$G$4),$I$2:$I$4)
HansVogelaar Thanks for the help man, I tried it, it worked very nicely.
