Single Column Formula Help With Annual Sales Agent Calculator w/CAP

[bear1970]

This is a simple calculator (pasted below) for sales agents to enter their starting annual commission split, along with their sales transactions row by row to see what they will NET per transaction and for the year. They can also see what the company/brokerage makes. I’ve entered dummy data to demonstrate how the calculator works.

Once a year, if the agent meets or exceeds the company dollar CAP of $19,808 the agent's commission (Agent Spl) automatically goes to 98% for the rest of the year. In this example: the CAP is met and exceeded on row 9.

To complete the calculator, I need your help with a formula in the Cap Exceeded (CE) column. What the formula needs to do is watch the Cumulative C $ column to see at what row the company CAP is exceeded AND then to show by how much (In the example here it’s exceeded by ($2,200). I did the math to show correct math and how it’s supposed to look/work but there is no formula YET!

*Any cell above or below at the point the CAP is met or exceeded needs to stay at Zero as this only happens once a year AND doing it this way the calculator works!

I appreciate any help. Been banging my head against a wall on this one for a while!

-Barry

Annual Agent Commission Calc v1d.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	SET ANNUAL AGENT SPLIT
2	72
3
4	Date	Address	Sale price	Comm	GCI	Agent Spl	Agent Pre $	Agent Fees	Agent $	Co Split	Company $	Cumulative C $	Cap Exceeded (CE)	CE Agent $	CE Company $			Agent enters
5	01/03/25	Deal 1	$400,000.00	3%	$12,000.00	72	$8,640.00	$845.00	$7,795.00	28	$3,360.00	$3,360.00		$0.00	$0.00
6	02/10/25	Deal2	$720,000.00	3%	$21,600.00	72	$15,552.00	$1,421.00	$14,131.00	28	$6,048.00	$9,408.00		$0.00	$0.00			Formulas
7	02/17/25	Deal 3	$900,000.00	3%	$27,000.00	72	$19,440.00	$1,745.00	$17,695.00	28	$7,560.00	$16,968.00		$0.00	$0.00
8	03/10/25	Deal 4	$600,000.00	3%	$18,000.00	98	$12,960.00	$1,205.00	$11,755.00	28	$5,040.00	$22,008.00	$2,200.00	$2,156.00	$44.00
9	04/01/25	Deal 5	$125,000.00	3%	$3,750.00	98	$3,675.00	$350.00	$3,325.00	2	$75.00	$22,083.00		$0.00	$0.00
10	04/20/25	Deal 6	$400,000.00	3%	$12,000.00	98	$11,760.00	$845.00	$10,915.00	2	$240.00	$22,323.00		$0.00	$0.00			List Values
11					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00			CF	List
12					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00			98	72
13					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				76
14					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				80
15					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				84
16					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				88
17					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				92
18					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				98
19					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00				0
20					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
21					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
22					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
23					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
24					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
25					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
26					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
27					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
28					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
29					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
30					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
31					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
32					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
33					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
34					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		$0.00	$0.00
35								Totals	$67,772.00		$22,367.00
Calc 1d

Cell Formulas
Range	Formula
E5:E34	E5	=C5*D5
F5	F5	=IF(SUM($K$5:K5)>19808,98,A2)
G5	G5	=IF(E5*A2%-125<0,0,E5*A2%)
H5:H34	H5	=IF(C5="",0,E5*6%+125)
F6:F34	F6	=IF(SUM($K$5:K6)>19808,98,F5)
G6:G34	G6	=IF(E6*F5%-125<0,0,E6*F5%)
N5:N34	N5	=M5*98%
O5:O34	O5	=M5*2%
J5	J5	=100-A2
J6:J34	J6	=100-F5
L5:L34	L5	=IFERROR(K5+L4,K5)
I5:I34	I5	=IF(C5="",0,G5-H5)
I35	I35	=SUM(I5:I34+N5:N34)
K5:K34	K5	=E5*J5%
K35	K35	=SUM(K5:K34+O5:O34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:L34,A35:F35,H35:K35,O35,A36:O3734	Expression	=$R$12=$F5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$S$12:$S$19

 

[myall_blues]

In M8:

=IF(AND(L8>=19808,L7<19808),L8-19808,"")

copied up/down as appropriate.

 

[bear1970]

@myall_blues This is GENIUS!!! OMG! Thank you!! Ok, a couple questions... some things stopped working when I added the formula but I'm sure these may be easy fixes.

1. I am sure M5 is incorrect as it refers to a column heading. How should I modify that formula? If this helps: It is possible the CAP could be exceeded on this row if the sale is high enough.
2. Column N & O have errored. Anything I can do to remedy that?
3. My totals in I35 and K35 have errored. What can I do there?

Thanks again for your time!!
-Barry

Annual Agent Commission Calc v1d.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	SET ANNUAL AGENT SPLIT
2	72
3
4	Date	Address	Sale price	Comm	GCI	Agent Spl	Agent Pre $	Agent Fees	Agent $	Co Split	Company $	Cumulative C $	Cap Exceeded (CE)	CE Agent $	CE Company $			Agent enters
5	01/03/25	Deal 1	$400,000.00	3%	$12,000.00	72	$8,640.00	$845.00	$7,795.00	28	$3,360.00	$3,360.00		#VALUE!	#VALUE!
6	02/10/25	Deal2	$720,000.00	3%	$21,600.00	72	$15,552.00	$1,421.00	$14,131.00	28	$6,048.00	$9,408.00		#VALUE!	#VALUE!			Formulas
7	02/17/25	Deal 3	$900,000.00	3%	$27,000.00	72	$19,440.00	$1,745.00	$17,695.00	28	$7,560.00	$16,968.00		#VALUE!	#VALUE!
8	03/10/25	Deal 4	$600,000.00	3%	$18,000.00	98	$12,960.00	$1,205.00	$11,755.00	28	$5,040.00	$22,008.00	$2,200.00	$2,156.00	$44.00
9	04/01/25	Deal 5	$125,000.00	3%	$3,750.00	98	$3,675.00	$350.00	$3,325.00	2	$75.00	$22,083.00		#VALUE!	#VALUE!
10	04/20/25	Deal 6	$400,000.00	3%	$12,000.00	98	$11,760.00	$845.00	$10,915.00	2	$240.00	$22,323.00		#VALUE!	#VALUE!			List Values
11					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!			CF	List
12					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!			98	72
13					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				76
14					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				80
15					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				84
16					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				88
17					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				92
18					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				98
19					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!				0
20					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
21					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
22					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
23					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
24					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
25					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
26					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
27					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
28					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
29					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
30					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
31					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
32					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
33					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
34					$0.00	98	$0.00	$0.00	$0.00	2	$0.00	$22,323.00		#VALUE!	#VALUE!
35								Totals	#VALUE!		#VALUE!
Calc 1d

Cell Formulas
Range	Formula
E5:E34	E5	=C5*D5
F5	F5	=IF(SUM($K$5:K5)>19808,98,A2)
G5	G5	=IF(E5*A2%-125<0,0,E5*A2%)
H5:H34	H5	=IF(C5="",0,E5*6%+125)
F6:F34	F6	=IF(SUM($K$5:K6)>19808,98,F5)
G6:G34	G6	=IF(E6*F5%-125<0,0,E6*F5%)
L5:L34	L5	=IFERROR(K5+L4,K5)
M5:M34	M5	=IF(AND(L5>=19808,L4<19808),L5-19808,"")
N5:N34	N5	=M5*98%
O5:O34	O5	=M5*2%
J5	J5	=100-A2
J6:J34	J6	=100-F5
I5:I34	I5	=IF(C5="",0,G5-H5)
I35	I35	=SUM(I5:I34+N5:N34)
K5:K34	K5	=E5*J5%
K35	K35	=SUM(K5:K34+O5:O34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:L34,A35:F35,H35:K35,O35,A36:O3734	Expression	=$R$12=$F5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$S$12:$S$19

 

[bear1970]

@myall_blues ..OK! I fixed #2 and #3 issues in my above post just telling Excel that Zero values are ok using =IF(M8="","", Do you have any recommendations as to how to fix #1? "

1. I am sure M5 is incorrect as it refers to a column heading. How should I modify that top of column cell formula? If this helps: It is possible the CAP could be exceeded on this row if the sale is high enough."

 

[myall_blues]

Change M5 to the more simple:

=IF(L5>=19808,L5-19808,"")

An alternative and slightly neater way to handle 2 and 3 is to wrap the formula in IFERROR
e.g.

=IFERROR(M5*98%,"")

 

[bear1970]

@myall_blues Thank you Sir!!!

 

[myall_blues]

You’re welcome.