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

 

[MARK858]

What to do?

Have you set up the named ranges?

 

[bear1970]

Ugh.. I do not know what that is!

 

[MARK858]

Ugh.. I do not know what that is!

@Rijnsent provided a link to how to use Named ranges in post 8 (that you also copied into post 9), all the data you need to set them up is in the XL2BB in post 8 bar you'll need to change the sheet names to match yours

 

[bear1970]

Mark858, thank you for pointing that out! I totally missed it attempting to answer @Rijnsent's questions! Ok.. will work on it!!

 

[bear1970]

@Rijnsent I have NAME errors all over the place even after renaming the page. Not sure what to do next??

Agent Commission.xlsx
	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	#NAME?	#NAME?	#NAME?	#NAME?		Threshold	19808
3	125000	0.03	3750	18870	#NAME?	#NAME?	#NAME?	#NAME?		Low percentage	0.72
4	845000	0.03	25350	44220	#NAME?	#NAME?	#NAME?	#NAME?		High percentage	0.98
5	400000	0.06	24000	68220	#NAME?	#NAME?	#NAME?	#NAME?
6	534000	0.03	16020	84240	#NAME?	#NAME?	#NAME?	#NAME?
7	874000	0.03	26220	110460	#NAME?	#NAME?	#NAME?	#NAME?
8	534000	0.03	16020	126480	#NAME?	#NAME?	#NAME?	#NAME?
9			0	126480	#NAME?	#NAME?		#NAME?
10			0	126480	#NAME?	#NAME?		#NAME?
11			0	126480	#NAME?	#NAME?		#NAME?
12
13	Totals					#NAME?		#NAME?
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)

 

[MARK858]

Then your named ranges aren't correct (again) what do you get with the below?

Book3
	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	10886.4	10886.4	0.72	4233.6		Threshold	19808
3	125000	0.03	3750	18870	13586.4	2700	0.72	1050		Low percentage	0.72
4	845000	0.03	25350	44220	38185.52	24599.12	0.970379	750.88		High percentage	0.98
5	400000	0.06	24000	68220	61705.52	23520	0.98	480
6	534000	0.03	16020	84240	77405.12	15699.6	0.98	320.4
7	874000	0.03	26220	110460	103100.7	25695.6	0.98	524.4
8	534000	0.03	16020	126480	118800.3	15699.6	0.98	320.4
9			0	126480	118800.3	0		0
10			0	126480	118800.3	0		0
11			0	126480	118800.3	0		0
12
13	Totals					118800.3		7679.68
Blad1

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

 

[Rijnsent]

Hi @bear1970 ,
you could try the solution that @MARK858 posted, it doesn't have named ranges in it.

If you want to use named ranges, please do check out the link I provided. The basics of named ranges: in a "normal" formula you could write =A1+A2 . Alternatively, you select cell A1 and check out the box at the left of the formula bar (standard, that box holds the cell address, like in this case "A1"). In that box, you write the name you'd like it to have, e.g. "tax_rate". After you've done that, your formula could become =tax_rate+A2. Excel "translates" the name "tax_rate" into A1. So that's what you need to do with K2 (name it perc_high), K3 (perc_low) and K4 (threshold).
Same goes for ranges: if you select e.g. the range B1:B10, you can use that same box to give that range a name, say "dollar_values". In a formula, you could than use =SUM(dollar_values).

Named ranges are there to make formulas more readable, but not mandatory to make excel work .

 

[bear1970]

Hey @MARK858 The math from your post is not making sense to me in regards to the regular calculations. It makes me thing I have not communicated very well. I'm going to spend some more time with this and see what I can get going even if it's further clarification. Thank you so much for your help! @Rijnsent too!

BTW.. Happy New Year's ALL!

​

 

[MARK858]

The math is from the formulas you posted in post 15, all I have done is replaced the named ranges with cell references

 

[bear1970]

@MARK858 Thx for that. I'll see what's going on!