Forum Discussion

JoelSharpe1989's avatar
JoelSharpe1989
Copper Contributor
May 25, 2024

Creating a Sliding Commission Scale in Excel

Hi   I have a Sliding Scale Commission that I pay my staff.  They have to submit an invoice to me to show how much sales have been done and then how much I need to pay them in commission.  ...
excel
Formulas and Functions
TheDub's avatar
TheDub
Iron Contributor
May 26, 2024
Not to hijack the question, but PeterBartholomew1, here's a fun (to me at least) question - how would you modify your COMMISSION function to work in reverse: given a final commission (460, for example) find the sale amount (3000)?
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 26, 2024

TheDub 

I confess, there was an element of playing with this.  I first used the function I had already written to determine the the commission corresponding to each threshold.  Then I calculated the commission shortfall for each band.  What I should have done then is select the band with the smallest positive amount and base the calculation upon that.

 

Being perverse, I performed the calculation for each band and relied upon convexity of the piecewise-linear sales/commission curve to determine that lowest value is the correct value.  The final return values include a check that the 'required' sales indeed returns the target commission.

= LET(
    delta,    target - MAP(Threshold, COMMISSIONλ),
    uplift,   delta / Percentage,
    required, MIN(Threshold + uplift),
    HSTACK(required, COMMISSIONλ(required))
  )

... and then I wonder why so many standard Excel users fail to recognise the solutions I offer as having anything to do with Excel :lol:!

 

Resources