Forum Discussion
Creating a Sliding Commission Scale in Excel
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.
- 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)?
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.
... 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.
Entirely agree - though I'm still now sure what this will do the future of Excel and its massive number of users. Will they change their approach to problem solving or just drop out?
To solve the new problem, I first Curried the Commissionλ function
Yes, tomorrow morning when my colleagues come back from their (US) Memorial Day weekend I'll try to explain this one to them and see where that takes me
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
!
