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

bear1970

New Member
Joined
Dec 20, 2013
Messages
31
Office Version
  1. 2016
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
ABCDEFGHIJKLMNOPQRS
1SET ANNUAL AGENT SPLIT
272
3
4DateAddressSale priceCommGCIAgent SplAgent Pre $Agent FeesAgent $Co SplitCompany $Cumulative C $Cap Exceeded (CE)CE Agent $CE Company $Agent enters
501/03/25Deal 1$400,000.003%$12,000.0072$8,640.00$845.00$7,795.0028$3,360.00$3,360.00$0.00$0.00
602/10/25Deal2$720,000.003%$21,600.0072$15,552.00$1,421.00$14,131.0028$6,048.00$9,408.00$0.00$0.00Formulas
702/17/25Deal 3$900,000.003%$27,000.0072$19,440.00$1,745.00$17,695.0028$7,560.00$16,968.00$0.00$0.00
803/10/25Deal 4$600,000.003%$18,000.0098$12,960.00$1,205.00$11,755.0028$5,040.00$22,008.00$2,200.00$2,156.00$44.00
904/01/25Deal 5$125,000.003%$3,750.0098$3,675.00$350.00$3,325.002$75.00$22,083.00$0.00$0.00
1004/20/25Deal 6$400,000.003%$12,000.0098$11,760.00$845.00$10,915.002$240.00$22,323.00$0.00$0.00List Values
11$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00CFList
12$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.009872
13$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.0076
14$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.0080
15$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.0084
16$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.0088
17$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.0092
18$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.0098
19$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.000
20$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
21$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
22$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
23$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
24$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
25$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
26$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
27$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
28$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
29$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
30$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
31$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
32$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
33$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
34$0.0098$0.00$0.00$0.002$0.00$22,323.00$0.00$0.00
35Totals$67,772.00$22,367.00
Calc 1d
Cell Formulas
RangeFormula
E5:E34E5=C5*D5
F5F5=IF(SUM($K$5:K5)>19808,98,A2)
G5G5=IF(E5*A2%-125<0,0,E5*A2%)
H5:H34H5=IF(C5="",0,E5*6%+125)
F6:F34F6=IF(SUM($K$5:K6)>19808,98,F5)
G6:G34G6=IF(E6*F5%-125<0,0,E6*F5%)
N5:N34N5=M5*98%
O5:O34O5=M5*2%
J5J5=100-A2
J6:J34J6=100-F5
L5:L34L5=IFERROR(K5+L4,K5)
I5:I34I5=IF(C5="",0,G5-H5)
I35I35=SUM(I5:I34+N5:N34)
K5:K34K5=E5*J5%
K35K35=SUM(K5:K34+O5:O34)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:L34,A35:F35,H35:K35,O35,A36:O3734Expression=$R$12=$F5textNO
Cells with Data Validation
CellAllowCriteria
A2List=$S$12:$S$19
 
In M8:
Excel Formula:
=IF(AND(L8>=19808,L7<19808),L8-19808,"")

copied up/down as appropriate.
 
Upvote 0
@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
ABCDEFGHIJKLMNOPQRS
1SET ANNUAL AGENT SPLIT
272
3
4DateAddressSale priceCommGCIAgent SplAgent Pre $Agent FeesAgent $Co SplitCompany $Cumulative C $Cap Exceeded (CE)CE Agent $CE Company $Agent enters
501/03/25Deal 1$400,000.003%$12,000.0072$8,640.00$845.00$7,795.0028$3,360.00$3,360.00 #VALUE!#VALUE!
602/10/25Deal2$720,000.003%$21,600.0072$15,552.00$1,421.00$14,131.0028$6,048.00$9,408.00 #VALUE!#VALUE!Formulas
702/17/25Deal 3$900,000.003%$27,000.0072$19,440.00$1,745.00$17,695.0028$7,560.00$16,968.00 #VALUE!#VALUE!
803/10/25Deal 4$600,000.003%$18,000.0098$12,960.00$1,205.00$11,755.0028$5,040.00$22,008.00$2,200.00$2,156.00$44.00
904/01/25Deal 5$125,000.003%$3,750.0098$3,675.00$350.00$3,325.002$75.00$22,083.00 #VALUE!#VALUE!
1004/20/25Deal 6$400,000.003%$12,000.0098$11,760.00$845.00$10,915.002$240.00$22,323.00 #VALUE!#VALUE!List Values
11$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!CFList
12$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!9872
13$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!76
14$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!80
15$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!84
16$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!88
17$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!92
18$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!98
19$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!0
20$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
21$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
22$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
23$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
24$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
25$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
26$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
27$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
28$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
29$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
30$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
31$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
32$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
33$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
34$0.0098$0.00$0.00$0.002$0.00$22,323.00 #VALUE!#VALUE!
35Totals#VALUE!#VALUE!
Calc 1d
Cell Formulas
RangeFormula
E5:E34E5=C5*D5
F5F5=IF(SUM($K$5:K5)>19808,98,A2)
G5G5=IF(E5*A2%-125<0,0,E5*A2%)
H5:H34H5=IF(C5="",0,E5*6%+125)
F6:F34F6=IF(SUM($K$5:K6)>19808,98,F5)
G6:G34G6=IF(E6*F5%-125<0,0,E6*F5%)
L5:L34L5=IFERROR(K5+L4,K5)
M5:M34M5=IF(AND(L5>=19808,L4<19808),L5-19808,"")
N5:N34N5=M5*98%
O5:O34O5=M5*2%
J5J5=100-A2
J6:J34J6=100-F5
I5:I34I5=IF(C5="",0,G5-H5)
I35I35=SUM(I5:I34+N5:N34)
K5:K34K5=E5*J5%
K35K35=SUM(K5:K34+O5:O34)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:L34,A35:F35,H35:K35,O35,A36:O3734Expression=$R$12=$F5textNO
Cells with Data Validation
CellAllowCriteria
A2List=$S$12:$S$19
 
Upvote 0
@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."
 
Upvote 0
Change M5 to the more simple:
Excel Formula:
=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.
Excel Formula:
=IFERROR(M5*98%,"")
 
Upvote 0
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top