Multiple Nested IF Statements Returning FALSE - COMPLEX & TOUGH!

[leslyons]

I apologize in advance for the complexity in trying to explain. I've built a compensation plan with several incentive awards. One of them is a two-tier bonus, whereby I award $5,000 if the person achieves 110% of their annual sales quota by the end of the year, and a second tier whereby I award an additional $5,000 if they get to 120% of their annual sales quota.

You can ignore most of the spreadsheet and focus in on the Stretch Bonus in rows 52-62. The statement that I thought I wrote correctly is in cell L54.

Essentially the way I thought I needed to write it is as follows:
"Look at the previous months year-to-date total stretch bonus paid:
> if $10,000 has been paid (both $5,000 increments), return 0.
> If it hasn't been paid, check to see if the first $5,000 increment has been paid;
> if it has, then test to see if the rep qualifies for the second $5,000 payout;
> if he qualifies, award it, if not, return 0.
> If no $5,000 increment has been paid out, test to see if the rep qualifies for the first $5,000 payout at 110%.
> If yes, award it; if not, return 0.

Here is my spreadsheet (XL2BB is shown below the screenshot):

2020 Compensation_Mr Excel Question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Full Year Performance	2020 Final Billings by QTR
2	DOMESTIC REPS		Q1	Q1 Adj	Q2	Q2 Adj	Q3	Q3 Adj	Q4	Q4 Adj	Total	Total Adj	Rental Revenue Adjustment Factor:			0.5
3	R10 - RepA	160%	308,550	308,550	308,550	308,550	358,550	358,550	358,550	358,550	1,334,200	1,334,200
4	R11 - RepB	140%	687,500	468,750	687,500	468,750	687,500	468,750	687,500	468,750	2,750,000	1,875,000
5	R12 - RepC	120%	575,000	512,500	575,000	512,500	475,000	423,370	675,000	601,631	2,300,000	2,050,000
6	R14 - RepD	80%	255,000	174,557	430,000	294,350	239,500	163,947	256,300	175,447	1,180,800	808,300
7	R16 - RepE	110%	400,000	259,959	792,500	515,042	792,500	515,042	400,000	259,959	2,385,000	1,550,001
8
21	2020 Revenue Performance - YTD															Full Year Revenue Performance Goals: by Region
22			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Year	100%	110%	120%	130%
23	R10 - RepA	160%	164,560	329,120	493,680	658,240	822,800	987,360	1,178,587	1,369,813	1,561,040	1,752,267	1,943,493	2,134,720	2,134,720	1,334,200	1,467,620	1,601,040	1,734,460
24	R11 - RepB	140%	218,750	437,500	656,250	875,000	1,093,750	1,312,500	1,531,250	1,750,000	1,968,750	2,187,500	2,406,250	2,625,000	2,625,000	1,875,000	2,062,500	2,250,000	2,437,500
25	R12 - RepC	120%	205,000	410,000	615,000	820,000	1,025,000	1,230,000	1,399,348	1,568,696	1,738,043	1,978,696	2,219,348	2,460,000	2,460,000	2,050,000	2,255,000	2,460,000	2,665,000
26	R14 - RepD	80%	46,549	93,097	139,646	218,139	296,632	375,126	418,845	462,564	506,283	553,069	599,854	646,640	646,640	808,300	889,130	969,960	1,050,790
27	R16 - RepE	110%	95,318	190,637	285,955	474,803	663,652	852,501	1,041,349	1,230,198	1,419,046	1,514,364	1,609,682	1,705,001	1,705,001	1,550,001	1,705,001	1,860,001	2,015,001
28
29	2020 Incentive Compensation Delivery - Stretch Bonus															2020 Comp. Plan
30			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Year
31	R10 - RepA	160%	-	-	-	-	-	-	-	-	5,000	5,000	-	-	10,000
32	R11 - RepB	140%	-	-	-	-	-	-	-	-	-	FALSE	FALSE	FALSE	-	Stretch Bonus	110%	5,000
33	R12 - RepC	120%	-	-	-	-	-	-	-	-	5,000	-	-	5,000	10,000		120%	5,000
34	R14 - RepD	80%	-	-	-	-	-	-	-	-	-	FALSE	FALSE	FALSE	-
35	R16 - RepE	110%	-	-	-	-	-	-	-	-	-	FALSE	FALSE	FALSE	-
2020 Domestic Comp

Cell Formulas
Range	Formula
P23:S27	P23	=$L3*P$22
A23:A27	A23	=A11
C31	C31	=IF(C23<$L3*$Q32,0,$R$32)
D31	D31	=IF(D23<$L3*Q32,0,$R$32)
K31:K33,E31:J31	E31	=IF(SUM($C31:D31)=$R$32,0,IF(E23<$L3*$Q32,0,$R$32))
L31:N35	L31	=IF(SUM($C31:K31)=($R$32+$R$33),0,IF(SUM($C31:K31)=$R$32,IF(L23>=$L3*$Q$33,$R$33,IF(L23>=$L3*$Q$32,$R$32,0))))
O31:O35	O31	=SUM(C31:N31)
C32	C32	=IF(C24<$L4*Q32,0,$R$32)
D32	D32	=IF(D24<$L4*Q32,0,$R$32)
E32	E32	=IF(SUM($C32:D32)>0,0,IF(E24<$L4*Q32,0,$R$32))
F32	F32	=IF(SUM($C32:E32)>0,0,IF(F24<$L4*Q32,0,$R$32))
G32	G32	=IF(SUM($C32:F32)>0,0,IF(G24<$L4*Q32,0,$R$32))
H32	H32	=IF(SUM($C32:G32)>0,0,IF(H24<$L4*Q32,0,$R$32))
I32	I32	=IF(SUM($C32:H32)>0,0,IF(I24<$L4*Q32,0,$R$32))
J32	J32	=IF(SUM($C32:I32)>0,0,IF(J24<$L4*Q32,0,$R$32))
C33	C33	=IF(C25<$L5*Q32,0,$R$32)
D33	D33	=IF(D25<$L5*Q32,0,$R$32)
E33	E33	=IF(SUM($C33:D33)>0,0,IF(E25<$L5*Q32,0,$R$32))
F33	F33	=IF(SUM($C33:E33)>0,0,IF(F25<$L5*Q32,0,$R$32))
G33	G33	=IF(SUM($C33:F33)>0,0,IF(G25<$L5*Q32,0,$R$32))
H33	H33	=IF(SUM($C33:G33)>0,0,IF(H25<$L5*Q32,0,$R$32))
I33	I33	=IF(SUM($C33:H33)>0,0,IF(I25<$L5*Q32,0,$R$32))
J33	J33	=IF(SUM($C33:I33)>0,0,IF(J25<$L5*Q32,0,$R$32))
C34	C34	=IF(C26<$L6*Q32,0,$R$32)
D34	D34	=IF(D26<$L6*Q32,0,$R$32)
E34	E34	=IF(SUM($C34:D34)>0,0,IF(E26<$L6*Q32,0,$R$32))
F34	F34	=IF(SUM($C34:E34)>0,0,IF(F26<$L6*Q32,0,$R$32))
G34	G34	=IF(SUM($C34:F34)>0,0,IF(G26<$L6*Q32,0,$R$32))
H34	H34	=IF(SUM($C34:G34)>0,0,IF(H26<$L6*Q32,0,$R$32))
I34	I34	=IF(SUM($C34:H34)>0,0,IF(I26<$L6*Q32,0,$R$32))
J34	J34	=IF(SUM($C34:I34)>0,0,IF(J26<$L6*Q32,0,$R$32))
K34	K34	=IF(SUM($C34:J34)>0,0,IF(K26<$L6*Q32,0,$R$32))
C35	C35	=IF(C27<$L7*Q32,0,$R$32)
D35	D35	=IF(D27<$L7*Q32,0,$R$32)
E35	E35	=IF(SUM($C35:D35)>0,0,IF(E27<$L7*Q32,0,$R$32))
F35	F35	=IF(SUM($C35:E35)>0,0,IF(F27<$L7*Q32,0,$R$32))
G35	G35	=IF(SUM($C35:F35)>0,0,IF(G27<$L7*Q32,0,$R$32))
H35	H35	=IF(SUM($C35:G35)>0,0,IF(H27<$L7*Q32,0,$R$32))
I35	I35	=IF(SUM($C35:H35)>0,0,IF(I27<$L7*Q32,0,$R$32))
J35	J35	=IF(SUM($C35:I35)>0,0,IF(J27<$L7*Q32,0,$R$32))
K35	K35	=IF(SUM($C35:J35)>0,0,IF(K27<$L7*Q32,0,$R$32))

 

[KRice]

I don't think there is a problem with the formula, but rather with what I did to check it out. I changed Rep E's cumulative revenue in several places to confirm that the formula was working. In the particular example I posted, in Sep, Rep E does not qualify for any award, and then in October, after a very good (and fictional) month...I changed the value to 1870000...Rep E would have exceeded 120% of the target, qualifying for both awards. In that case, the formula sums both stretch bonus amounts (which is why you see 7000). If you change the cumulative revenue value back to its original value (1514364.3...which I had copied just above the table), you should see the award disappear from Oct and appear only in December when the threshold is reached. Let me know if that works.

 

[leslyons]

Makes sense! Now I understand. Thanks so much!

 

[KRice]

You're welcome...we're glad to help.