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. 

I would like to create a generic Excel spreadsheet that they complete the total sales amount and it works out their commission pay in another box. 

The scale is:

£0-£2000 @ 15%
£2000-£3000 @ 16%

£3000-£4000 @ 17%
£4000+ @18%

So for example, if their sales is £2500:

they would get 15% of the £2000 = £300

then they would get 16% on the £500 = £80

 

so I would pay them £380

 

Is there a way to do this? 

TIA

excel
Formulas and Functions

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 25, 2024

    JoelSharpe1989 

    This is a MS365 solution which tends to differ from traditional methods.  Defining the Lambda function COMMISSION to be

    COMMISSION
= LAMBDA(sales,
    LET(
      limit, SORT(VSTACK(DROP(threshold, 1), sales)), 
      SUMPRODUCT(limit - threshold, percentage)
    )
  )

    the worksheet formula would be

    Single sales calculation
= COMMISSION(sales)

List of sales calculation
= MAP(salesList, COMMISSION)

     depending on whether a single calculation is required or whether it requires working down a list.

     

    • 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 27, 2024

        TheDub 

        I returned to the problem you posed.  She solutions are so far from the OP requirements that I hesitated to post, but there is an important point that modern Excel supports problem solving which is far closer to programming than it is to traditional spreadsheet authoring on the grid.

         

        The commission problem is simply the evaluation of points on a piecewise-linear function expressed in terms of a sequence of threshold values at which the slope changes.  The inverse problem is also that of evaluating a piecewise-linear function but with different thresholds and slopes.  To solve the new problem, I first Curried the Commissionλ function

        COMMISSIONλ
= PWLINEARλ(Threshold, Percentage);


PWLINEARλ
= LAMBDA(thresholdValues, slopes, LAMBDA(values,
    LET(
        limit, SORT(VSTACK(DROP(thresholdValues, 1), values)),
        SUMPRODUCT(limit-thresholdValues, slopes)
    )
));

         

        The worksheet formula then requires one to evaluate the commission applicable at each sales threshold and combine the values obtained with the inverse of the commission rates

         

        Worksheet formula
= LET(
    thresholdCommission,  MAP(Threshold, COMMISSIONλ),
    salesRequired,        MAP(target, PWLINEARλ(thresholdCommission, 1/Percentage)),
    HSTACK(salesRequired, COMMISSIONλ(salesRequired))
  )

         

        A massive change, very little of which is visible from the worksheet.

         

         

Resources