Multi tiered commission structure formula help

[Yasmin1985]

Sorry if I post this incorrectly .. first time poster!

I am trying to put together one sheet that calculates the commission due on a sale based on a number of factors - in addition the commission is stepped therefore one sale may be due commission at 4 different percentages.

The commission amount depends on where it is event income or marketing income, what year and quarter the income falls in, how much of their target they have reached cumulatively, along with whether it is a rebook or not and whether the annual target has been reached or not.

As an Example - first sale of the year is £900K in Q2 2024/25 - The commission on this would be
£40K @ 4% = £1,600
£5K @ 7% = £350
£782,200 @ 8% = £62,576
£72,800 @ 10% = £7,280
Total commission = £71,806

Obviously, this is simplified as in reality there will be many invoices between different quarters.

So far, I have a IF formula for each quarter, which isn't allowing for the annual target being hit for the Q1, Q2, Q3 & Q4 event commissions which seems to work upon testing.

My questions are;

1. Does anyone have a better formula to use to use?
2. If not a better way, how can I add in the annual target to the equation?

Thank you

 

[Dave Patton]

The commission can be calculated on the cumulative sales with Sumproduct or sum.
Please edit the following for your rate structures.

The second example uses Lambda.
You can create similar formulas for your various requirements.

To use the formula that you will create using Lambda, do the following in Formulas | Name Manager
- New name the function an appropriate name that you prefer, I used Commission_ABC
- Value put a copy of the formula details in this section

The function will prompt for the input when you enter the function in your sheet.

N.B. You can copy the example below into a clean sheet. Click on the icon below the f(x) in the example, move to your sheet, and paste.

Commission Cumulative Sales.xlsm
	A	B	C
1	Bracket/ Hurdle	Rate	Rate Differential
2	0.00	4%	4%
3	40,000.00	7%	3%
4	45,000.00	8%	1%
5	827,200.00	10%	2%
6
7
8		900,000.00	71,806.00
9			71,806.00
10
11	Bracket/ Hurdle	Rate
12	0.00	4%
13	40,000.00	7%
14	45,000.00	8%
15	827,200.00	10%
16
17		1,000,000.00	81,806.00
18
1d

Cell Formulas
Range	Formula
C2:C5	C2	=B2-N(B1)
C8	C8	=SUM((B8>$A$2:$A$5)*(B8-$A$2:$A$5)*$C$2:$C$5)
C9	C9	=Commission_ABC($A$12:$B$15,B8)
C17	C17	=Commission_ABC($A$12:$B$15,B17)

Lambda Functions
Name	Formula
Commission_ABC	=LAMBDA(rngRateData,Income,LET(rng,rngRateData,i,Income,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((i>b)*(i-b)*(r-ro))))

 

[Dave Patton]

Hopefully my suggestion helped.
A formula can include more factors but we would need a concise/clear explanation and an example with expected results.
N.B. The forum provides a tool named XL2BB to post an extract of your sheet to the forum.