Update List Number Going Forward in a Column Once Other Cell Is Greater Than A Specific $ Number

[bear1970]

Hello! This one has me wrapped around the axle for sure!

I'm creating a sales person's commission calculator. New sales people will start at a specific percent commission (currently showing 72% in column E) I have a list on the right of available commission percentages. This list is available as a drop down in column E. However, when the companies amount in cell H13 hits $19,808 and higher their commission will change to 92% going forward. So I'd like the amount in column E to change at the point and row that H13 hits or goes above the $19,808 as each sale is entered.

Example: So let's say sales person Mary starts the years out at 76% in column "Sales person's split %". The company will enter in 76% in column E going down. Mary sells enough for the "Company $" total to reach or exceed $19,808 in whatever row so that and subsequent rows in column E all change to 92%.

I've attached the sheet below. I do so appreciate any and all help!!

Thank you,
-Barry

Agent Commission.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		List price	Commision	GCI	Sales person's split %	Company split %	Sales person $	Company $					List
3	1	$500,000.00	3%	15,000.00	72	28	$10,800.00	$4,200.00					72
4	2				72								76
5	3				72								80
6	4				72								84
7	5				72								88
8	6				72								92
9	7				72
10	8				72
11	9				72
12
13		Totals					$10,800.00	$4,200.00
14
Sheet1

Cell Formulas
Range	Formula
D3	D3	=B3*C3
F3	F3	=100-E3
G3	G3	=D3*E3%
H3	H3	=D3*F3%
G13:H13	G13	=SUM(G3:G11)

Cells with Data Validation
Cell	Allow	Criteria
E3:E11	List	=$M$3:$M$8

 

[Rijnsent]

Hi bear, I don't know why you have a whole list of percentages, but what you could do is crudely:

E4: =IF(SUM($H$3:H3)>19808,92,E3)

(and drag down.
But I assume you want some smarter system where you have brackets? E.g. 0-19807 -> 72 and 19808+ -> 92 ? If so, you could do it like this:

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		List price	Commision	GCI	Sales person's split %	Company split %	Sales person $	Company $	Cumulative Company$	Lookup Perc			Total Comp$	Perc
2	1	500000	0,03	15000	72	28	10800	4200	4200	72			0	72
3	2	500000	0,06	30000	72	28	21600	8400	12600	80			6000	80
4	3	500000	0,06	30000	72	28	21600	8400	21000	92			19808	92
5	4	500000	0,33	165000	92	8	151800	13200	34200	92
6	5				92				34200	92
7	6				92
8	7				92
9	8				92
10	9				92
11
12		Totals					205800	34200
Blad1

Cell Formulas
Range	Formula
F2:F5	F2	=100-E2
G2:G5	G2	=D2*E2%
H2:H5	H2	=D2*F2%
I2:I6	I2	=IFERROR(H2+I1,H2)
J2:J6	J2	=XLOOKUP(I2,$M$2:$M$4,$N$2:$N$4,0,-1,1)
D2:D5	D2	=B2*C2
E3:E10	E3	=IF(SUM($H$2:H2)>19808,92,E2)
G12:H12	G12	=SUM(G2:G10)

 

[bear1970]

Hello Rijnsent! Thank you so very much for the reply!!

1. I made a mistake!! The commission goes to 98% (not 92%) after Company $ reaches $19,808. I was able to update the formula in column E but not sure how that interacts with everything else?
2. Curious why the formula isn't necessary or used in cell E2?
3. It appears you did away with the list/drop down I had which is fine. Is this just a cleaner way of doing it?
4. I would like to understand what column I,J,M,N are doing.

I understand you are probably very busy but I would like to learn and understand more if you have a few moments!

Thank you again for your time and patience!
-Barry

 

[Rijnsent]

Hi Barry,

4. Okay, column I shows the cumulative Company$ including that line, as that is the base to select what percentage somebody should get from that point. Column M & N are basically a lookup table, where the value from column I is compared with. I'm using the XLOOKUP function (XLOOKUP Function), the penultimate parameter is -1, which means that the value that I want to search for has to be "-1 = exact match or next smallest" (see the link). So in J3 for example. the function looks up 12600 (value of I3), compares that with the list in M2:M4, finds that the second row is the outcome (as that is the biggest value smaller than 12600), so returns from N2:N4 the second value (which is 80).
3. Well, if you have a table to calculate the percentage, why would you give anyone the option to fill in something?
2. As I wrote: that formula in column E is really crude and if any of the parameters change, you have to change all the formulas, that's why the second solution is much more flexible.
1. The exact values are up to you, the formulas are the main thing .

Cheers,
Koen

 

[bear1970]

Rijnsent, I am learning! I just found out that as soon as the agent reaches the company dollar of $19,808 even if it's in the middle of a transaction they credit the agent going forward for the rest of the year at 98%. This is great but I really have no idea how to put that into the calculator. Here's a simple example from the spreadsheet to illustrate the point (attached). In this example.. The agent does one deal that overshoots the Company $ ($19,808) by $18,075.60. In this case, as explained above, we would then credit the agent at whatever their annual split is (in this case it's 72%) up to the $19,808 and then from the $18,075.60 going forward... for the rest of the year, their commission goes up to 98% for every transaction (line). How could we include that in this calculator!??

Thank you so very much for taking the time to answer my beginner questions and help me!!

Agent Commission.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Sales price	Comm.	GCI	SP split %	C split %	Agent $		C $	Cumulative C $	Lookup Perc			Total Comp$	Perc	List
2	1	$504,000.00	3%	$15,120.00	72	28	$10,761.40		$4,233.60	$4,233.60	72	98		$0.00	72	72
3	2	$2,000,000.00	6%	$120,000.00	72	28	$86,275.00		$33,600.00	$37,833.60	92			$6,000.00	80	76
4	3			$0.00	98	2	$0.00		$0.00	$37,833.60	92			$19,808.00	92	80
5	4			$0.00	98	2	$0.00		$0.00	$37,833.60	92					84
6	5			$0.00	98	2	$0.00		$0.00	$37,833.60	92					88
7	6			$0.00	98	2	$0.00		$0.00	$37,833.60	92					92
8	7			$0.00	98	2	$0.00		$0.00	$37,833.60	92					0
9	8			$0.00	98	2	$0.00		$0.00	$37,833.60	92
10	9			$0.00	98	2	$0.00		$0.00	$37,833.60	92
11	10			$0.00	98	2	$0.00		$0.00	$37,833.60	92
12
13		Totals					$97,036.40		$37,833.60
Calc 1 (2)

Cell Formulas
Range	Formula
D2:D11	D2	=B2*C2
F2:F11	F2	=100-E2
G2:G11	G2	=IF(D2*E2%-125<0,0,D2*E2%-125)
I2:I11	I2	=D2*F2%
J2:J11	J2	=IFERROR(I2+J1,I2)
K2:K11	K2	=XLOOKUP(J2,$N$2:$N$4,$O$2:$O$4,0,-1,1)
E3:E11	E3	=IF(SUM($I$2:I2)>19808,98,E2)
G13,I13	G13	=SUM(G2:G10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I11	Expression	=$L$2=$E2	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$P$2:$P$8

 

[bear1970]

Rijnsent, my last reply above is not helpful! I was wading through complexity!! So, I've put more thought into it so a solution is much clearer now although my abilities are still limited and I still need your help! So here it goes....

Banging my head against a wall here! What would be the formula to produce what is explained in the steps below. There are no formulas for items in red. This is dummy data to explain what I'm trying to do!

1. Cap column (G) watches C $ (H) summing from H2 down, accumulatively
2. As soon as column H hits or goes over $19,808 (Cap amount) it throws the difference (if there is one) down one cell in G in order to be added (summed) into the immediate cell on it's left (F7 - in this example) so that total number in F7 (or whatever cell Caps or goes over) will go to the Agent $ at 98% going down the list!

All of your help is appreciated!

Agent Commission.xlsx
	A	B	C	D	E	F	G	H
1	Sales price	Comm.	GCI	SP split %	C split %	Agent $	Cap	C $
2	$504,000.00	3%	$15,120.00	72	28	$10,761.40	-	$4,233.60
3	$125,000.00	3%	$3,750.00	72	28	$2,575.00	-	$1,050.00
4	$845,000.00	3%	$25,350.00	72	28	$18,127.00	-	$7,098.00
5	$400,000.00	6%	$24,000.00	72	28	$17,155.00	-	$6,720.00
6	$534,000.00	3%	$16,020.00	72	28	$11,409.40	-	$4,485.60
7	$874,000.00	3%	$26,220.00	98	2	$29,349.80	3,779.20	$524.40
8	$534,000.00	3%	$16,020.00	98	2	$15,574.60		$320.40
9			$0.00	98	2	$0.00		$0.00
10			$0.00	98	2	$0.00		$0.00
11			$0.00	98	2	$0.00		$0.00
12
13	Totals					$104,952.20		$24,432.00
Calc 1 (3)

Cell Formulas
Range	Formula
C2:C11	C2	=A2*B2
E2:E11	E2	=100-D2
F2:F6	F2	=IF(C2*D2%-125<0,0,C2*D2%-125)
D3:D11	D3	=IF(SUM($H$2:H2)>19808,98,D2)
F7:F11	F7	=IF(C7*D7%-125<0,0,C7*D7%-125)+G7
H2:H11	H2	=C2*E2%
F13,H13	F13	=SUM(F2:F10)

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=#REF!

 

[bear1970]

Quick note! I should mention for the formula's sake in column G once it's created is that once the Cap is reached at $19,808 and the actions above occur column G should no longer generate additional numbers.

 

[Rijnsent]

Hi Bear, mmm, that is quite confusing. So to simplify it: can you describe how the system is supposed to work? This is what I understand:

• a part of the salesprice is commission, in your model default 3%
• the commission (also named CGI, column C) is split between the company and the sales person
• the default split is 72% for the sales person, 28% for the company
• on a yearly basis, for all commission brought in over 19808, the split should be 2% for the company and 98% for the sales person

Why do you include a -125 in your formula? Anyhow, this is how you could set it up. Note: I created some named ranges (makes formulas more readable, Named Ranges in Excel) and reordered the calculations.

Map1
	A	B	C	D	E	F	G	H	I	J	K
1	Sales price	Comm.	GCI	Cum. GCI	Cum Agent$	Agent$	Agent %	Company$
2	504000	0,03	15120	15120	10.886	10.886	72%	4.234		Threshold	19808
3	125000	0,03	3750	18870	13.586	2.700	72%	1.050		Low percentage	72%
4	845000	0,03	25350	44220	38.186	24.599	97%	751		High percentage	98%
5	400000	0,06	24000	68220	61.706	23.520	98%	480
6	534000	0,03	16020	84240	77.405	15.700	98%	320
7	874000	0,03	26220	110460	103.101	25.696	98%	524
8	534000	0,03	16020	126480	118.800	15.700	98%	320
9			0	126480	118.800	-		-
10			0	126480	118.800	-		-
11			0	126480	118.800	-		-
12
13	Totals					118.800		7.680
Blad1

Cell Formulas
Range	Formula
C2:C11	C2	=A2*B2
D2:D11	D2	=SUM($C$2:C2)
E2:E11	E2	=IF(D2>threshold,(D2-threshold)*perc_high+threshold*perc_low,D2*perc_low)
F2:F11	F2	=IFERROR(E2-E1,E2)
G2:G8	G2	=F2/C2
H2:H11	H2	=C2-F2
F13,H13	F13	=SUM(F2:F11)

Named Ranges
Name	Refers To	Cells
perc_high	=Blad1!$K$4	E2:E11
perc_low	=Blad1!$K$3	E2:E11
threshold	=Blad1!$K$2	E2:E11

 

[bear1970]

Hello Rijnsent! THank you for your reply! I'm straight into meetings this morning but I at least wanted to answer your questions! My responses are after the //!

• a part of the salesprice is commission, in your model default 3% // Kind of.. but depending on the deal it could change. Could be 4, 6, 2, 1. THat's why I left to type in.
• the commission (also named CGI, column C) is split between the company and the sales person // Yes, that is correct!
• the default split is 72% for the sales person, 28% for the company // Not default.. We look at agent's previous year and base their incoming split on that. If you see the drop down in the 1st cell you can see all the choices.
• on a yearly basis, for all commission brought in over 19808, the split should be 2% for the company and 98% for the sales person // Correct, once the brokerage reaches their 19,808 all agents go to 98% which will leave the brokerage 2%

Why do you include a -125 in your formula? // We have a few fees agents pay on their side that equal $125 . Anyhow, this is how you could set it up. Note: I created some named ranges (makes formulas more readable, Named Ranges in Excel) and reordered the calculations. // Thank you for this!! I will check it out as soon as I have a chance. This is exciting!!

 

[bear1970]

Not sure what to do. Column E through H all just say "#Name? " when I Paste it into a page. What to do?