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