Forum Discussion
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. ...
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)?