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
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
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 |