Multiple Nested IF Statements Returning FALSE - COMPLEX & TOUGH!

L

leslyons

New Member
Joined
Aug 11, 2002
Messages
18
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):
1583634426980.png

2020 Compensation_Mr Excel Question.xlsx
ABCDEFGHIJKLMNOPQRS
1Full Year Performance2020 Final Billings by QTR
2DOMESTIC REPSQ1Q1 AdjQ2Q2 AdjQ3Q3 AdjQ4Q4 AdjTotalTotal AdjRental 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
22JanFebMarAprMayJunJulAugSepOctNovDecYear100%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
30JanFebMarAprMayJunJulAugSepOctNovDecYear
31 R10 - RepA 160% - - - - - - - - 5,000 5,000 - - 10,000
32 R11 - RepB 140% - - - - - - - - - FALSE FALSE FALSE - Stretch Bonus110%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
RangeFormula
P23:S27P23=$L3*P$22
A23:A27A23=A11
C31C31=IF(C23<$L3*$Q32,0,$R$32)
D31D31=IF(D23<$L3*Q32,0,$R$32)
K31:K33,E31:J31E31=IF(SUM($C31:D31)=$R$32,0,IF(E23<$L3*$Q32,0,$R$32))
L31:N35L31=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:O35O31=SUM(C31:N31)
C32C32=IF(C24<$L4*Q32,0,$R$32)
D32D32=IF(D24<$L4*Q32,0,$R$32)
E32E32=IF(SUM($C32:D32)>0,0,IF(E24<$L4*Q32,0,$R$32))
F32F32=IF(SUM($C32:E32)>0,0,IF(F24<$L4*Q32,0,$R$32))
G32G32=IF(SUM($C32:F32)>0,0,IF(G24<$L4*Q32,0,$R$32))
H32H32=IF(SUM($C32:G32)>0,0,IF(H24<$L4*Q32,0,$R$32))
I32I32=IF(SUM($C32:H32)>0,0,IF(I24<$L4*Q32,0,$R$32))
J32J32=IF(SUM($C32:I32)>0,0,IF(J24<$L4*Q32,0,$R$32))
C33C33=IF(C25<$L5*Q32,0,$R$32)
D33D33=IF(D25<$L5*Q32,0,$R$32)
E33E33=IF(SUM($C33:D33)>0,0,IF(E25<$L5*Q32,0,$R$32))
F33F33=IF(SUM($C33:E33)>0,0,IF(F25<$L5*Q32,0,$R$32))
G33G33=IF(SUM($C33:F33)>0,0,IF(G25<$L5*Q32,0,$R$32))
H33H33=IF(SUM($C33:G33)>0,0,IF(H25<$L5*Q32,0,$R$32))
I33I33=IF(SUM($C33:H33)>0,0,IF(I25<$L5*Q32,0,$R$32))
J33J33=IF(SUM($C33:I33)>0,0,IF(J25<$L5*Q32,0,$R$32))
C34C34=IF(C26<$L6*Q32,0,$R$32)
D34D34=IF(D26<$L6*Q32,0,$R$32)
E34E34=IF(SUM($C34:D34)>0,0,IF(E26<$L6*Q32,0,$R$32))
F34F34=IF(SUM($C34:E34)>0,0,IF(F26<$L6*Q32,0,$R$32))
G34G34=IF(SUM($C34:F34)>0,0,IF(G26<$L6*Q32,0,$R$32))
H34H34=IF(SUM($C34:G34)>0,0,IF(H26<$L6*Q32,0,$R$32))
I34I34=IF(SUM($C34:H34)>0,0,IF(I26<$L6*Q32,0,$R$32))
J34J34=IF(SUM($C34:I34)>0,0,IF(J26<$L6*Q32,0,$R$32))
K34K34=IF(SUM($C34:J34)>0,0,IF(K26<$L6*Q32,0,$R$32))
C35C35=IF(C27<$L7*Q32,0,$R$32)
D35D35=IF(D27<$L7*Q32,0,$R$32)
E35E35=IF(SUM($C35:D35)>0,0,IF(E27<$L7*Q32,0,$R$32))
F35F35=IF(SUM($C35:E35)>0,0,IF(F27<$L7*Q32,0,$R$32))
G35G35=IF(SUM($C35:F35)>0,0,IF(G27<$L7*Q32,0,$R$32))
H35H35=IF(SUM($C35:G35)>0,0,IF(H27<$L7*Q32,0,$R$32))
I35I35=IF(SUM($C35:H35)>0,0,IF(I27<$L7*Q32,0,$R$32))
J35J35=IF(SUM($C35:I35)>0,0,IF(J27<$L7*Q32,0,$R$32))
K35K35=IF(SUM($C35:J35)>0,0,IF(K27<$L7*Q32,0,$R$32))
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Here is one approach...try this to see if it helps. I changed the incentive numbers to distinguish between the 1st and 2nd tier amounts to confirm that different parts of the formula worked correctly.

MrExcel_2.xlsm
ABCDEFGHIJKLMNOPQRS
1Full Year Performance2020 Final Billings by QTR
2DOMESTIC REPSQ1Q1 AdjQ2Q2 AdjQ3Q3 AdjQ4Q4 AdjTotalTotal AdjRental Revenue Adjustment Factor:0.5
3R10 - RepA1.630855030855030855030855035855035855035855035855013342001334200
4R11 - RepB1.468750046875068750046875068750046875068750046875027500001875000
5R12 - RepC1.2575000512500575000512500475000423369.5675000601630.523000002050000
6R14 - RepD0.8255000174557430000294350239500163946.5256300175446.51180800808300
7R16 - RepE1.1400000259959792500515041.5792500515041.5400000259958.523850001550000.5
8
9
10
11
12
13
14
15
16
17
18
19
20
212020 Revenue Performance - YTDFull Year Revenue Performance Goals: by Region
22JanFebMarAprMayJunJulAugSepOctNovDecYear11.11.21.3
2301.61645603291204936806582408228009873601178586.71369813.315610401752266.71943493.3213472021347201334200146762016010401734460
2401.42187504375006562508750001093750131250015312501750000196875021875002406250262500026250001875000206250022500002437500
2501.2205000410000615000820000102500012300001399347.81568695.61738043.41978695.62219347.8246000024600002050000225500024600002665000
2600.846548.53393097.067139645.6218138.93296632.27375125.6418844.67462563.73506282.8553068.53599854.276466406466408083008891309699601050790
2701.195318.3190636.6285954.9474803.45663652852500.551041349.11230197.71419046.21514364.31609682.431705000.561705000.561550000.501705000.551860000.602015000.65
28
292020 Incentive Compensation Delivery - Stretch Bonus2020 Comp. Plan
30JanFebMarAprMayJunJulAugSepOctNovDecYear
31R10 - RepA1.60000000020005000007000
32R11 - RepB1.40000000002000500007000Stretch Bonus1.12000
33R12 - RepC1.200000000000200020001.25000
34R14 - RepD0.80000000000000
35R16 - RepE1.10000000000020002000
Sheet23
Cell Formulas
RangeFormula
P23:S27P23=$L3*P$22
A23:A27A23=A11
C31:C35C31=CHOOSE(1+($C23>$L$3*$Q$32)+($C23>$L$3*$Q$33),0,$R$32,$R$32+$R$33)
D31:N35D31=CHOOSE(1+(D23>$L3*$Q$32)*(SUM($C31:C31)<=$R$32)+(D23>$L3*$Q$33)*(SUM($C31:C31)<$R$32+$R$33),0,$R$32,$R$33)
O31:O35O31=SUM(C31:N31)
 
Upvote 0
I think the part that is missing from this treatment concerns the scenario where someone has one month where they are just under any incentive award threshold followed by a very good month that puts them over the 120 % threshold. Is that a possibility? If so, then the formula needs a little more work....and in that case, I'm assuming you'd like to show the sum of R32 and R33 in the stretch bonus cell corresponding to that month?
 
Upvote 0
Hi,

I have made the following solution. Please test it carefully.

Book1
ABCDEFGHIJKLMNOPQRS
212020 Revenue Performance - YTDFull Year Revenue Performance Goals: by Region
22JanFebMarAprMayJunJulAugSepOctNovDecYear11.11.21.3
2301.616456032912049368065824082280098736011785871369813156104017522671943493213472021347201334200146762016010401734460
2401.42187504375006562508750001093750131250015312501750000196875021875002406250262500026250001875000206250022500002437500
2501.22050004100006150008200001025000123000013993481568696173804319786962219348246000024600002050000225500024600002665000
2600.846548.5393097.07139645.6218138.9296632.3375125.6418844.7462563.7506282.8553068.5599854.36466406466408083008891309699601050790
2701.195318.3190636.6285954.9474803.5663652852500.610413491230198141904615143641609682170500117050011550001170500118600012015001
28
292020 Incentive Compensation Delivery - Stretch Bonus2020 Comp. Plan
30JanFebMarAprMayJunJulAugSepOctNovDecYear
31R10 - RepA1.600000000500050000010000
32R11 - RepB1.400000000050005000010000Stretch Bonus1.15000
33R12 - RepC1.20000000000010000100001.25000
34R14 - RepD0.80000000000000
35R16 - RepE1.10000000000050005000
Sheet2
Cell Formulas
RangeFormula
P23:S27P23=$L3*P$22
A23:A27A23=A11
C31:N35C31=$R$32*(COUNTIF($C23:C23,">="&$L3*$Q$32)=1)+$R$33*(COUNTIF($C23:C23,">="&$L3*$Q$33)=1)
O31:O35O31=SUM(C31:N31)
 
Upvote 0
Here is a revised version to address the comment I made earlier. My approach isn't as elegant as J.Ty's, but I wanted to make it available. J.Ty, I think there is one issue in your formula that pertains to a scenario where both incentive awards are issued prior to December (e.g, perhaps one is awarded in Sep, the other in Oct). In that case, the 2nd award is not zeroed out in subsequent months...so in this example both Nov and Dec show a repeated tier 2 incentive award rather than 0.

MrExcel_2.xlsm
ABCDEFGHIJKLMNOPQRS
1Full Year Performance2020 Final Billings by QTR
2DOMESTIC REPSQ1Q1 AdjQ2Q2 AdjQ3Q3 AdjQ4Q4 AdjTotalTotal AdjRental Revenue Adjustment Factor:0.5
3R10 - RepA1.630855030855030855030855035855035855035855035855013342001334200
4R11 - RepB1.468750046875068750046875068750046875068750046875027500001875000
5R12 - RepC1.2575000512500575000512500475000423369.5675000601630.523000002050000
6R14 - RepD0.8255000174557430000294350239500163946.5256300175446.51180800808300
7R16 - RepE1.1400000259959792500515041.5792500515041.5400000259958.523850001550000.5
8
9
10
11
12
13
14
15
16
17
18
19
201419046.21514364.3
212020 Revenue Performance - YTDFull Year Revenue Performance Goals: by Region
22JanFebMarAprMayJunJulAugSepOctNovDecYear11.11.21.3
2301.61645603291204936806582408228009873601178586.71369813.315610401752266.71943493.3213472021347201334200146762016010401734460
2401.42187504375006562508750001093750131250015312501750000196875021875002406250262500026250001875000206250022500002437500
2501.2205000410000615000820000102500012300001399347.81568695.61738043.41978695.62219347.8246000024600002050000225500024600002665000
2600.846548.53393097.067139645.6218138.93296632.27375125.6418844.67462563.73506282.8553068.53599854.276466406466408083008891309699601050790
2701.195318.3190636.6285954.9474803.45663652852500.551041349.11230197.71419046.218700001609682.431705000.561705000.561550000.501705000.551860000.602015000.65
28
292020 Incentive Compensation Delivery - Stretch Bonus2020 Comp. Plan
30JanFebMarAprMayJunJulAugSepOctNovDecYear
31R10 - RepA1.60000000020005000007000
32R11 - RepB1.40000000002000500007000Stretch Bonus1.12000
33R12 - RepC1.200000000000700070001.25000
34R14 - RepD0.80000000000000
35R16 - RepE1.10000000007000007000
Sheet23
Cell Formulas
RangeFormula
P23:S27P23=$L3*P$22
A23:A27A23=A11
C31:C35C31=CHOOSE(1+($C23>$L$3*$Q$32)+($C23>$L$3*$Q$33),0,$R$32,$R$32+$R$33)
D31:N35D31=AND(D23>=$L3*$Q$32,D23<$L3*$Q$33)*(SUM($C31:C31)<$R$32)*$R$32+(D23>=$L3*$Q$33)*(SUM($C31:C31)<$R$32)*$R$32+(D23>=$L3*$Q$33)*(SUM($C31:C31)<$R$32+$R$33)*$R$33
O31:O35O31=SUM(C31:N31)
 
Upvote 0
@J.Ty...no, sorry. My mistake. The situation I described only occurs when the cumulative revenue rows are not increasing from left to right, which isn't realistic. For realistic values, everything works well. Nice job!
 
Upvote 0
@leslyons
I see a problem in your data (if it is real, of course). At least two reps have reached the exact minimum necessary to get the stretch bonus: RepC and RepE.
It might indicate that they are doing crazy things just to get past the mark, which effectively harm your business. I know a little about such cases and I would recommend another form of stretch bonus, which does not have jumps.

J.Ty.
 
Upvote 0
KRice said:
@J.Ty...no, sorry. My mistake. The situation I described only occurs when the cumulative revenue rows are not increasing from left to right, which isn't realistic. For realistic values, everything works well. Nice job!
Click to expand...
Thanks!
 
Upvote 0
KRice said:
@J.Ty...no, sorry. My mistake. The situation I described only occurs when the cumulative revenue rows are not increasing from left to right, which isn't realistic. For realistic values, everything works well. Nice job!
Click to expand...

J.Ty. & KRice- thanks to both you and KRice for your help! I will test both approaches. I was going crazy trying to come up with multiple IF statements that would account for the various situations where they would qualify for one bonus, then possibly the second bonus, during the year! And yes, I certainly understand about the "unnatural acts" that could possibly occur if you incent a sales person with a large bonus! I'm counseling my compatriots on that!
 
Upvote 0
KRice - I think I might see one problem with your solution: Rep E goes through the year and ends up at 110% and qualifies for the first tier bonus ($2,000 using your example). Yet it shows him earning $7,000 - both tiers. I haven't had time to completely go through it, but wanted to bring that up for your review/comment.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
210
StephenCrump
S
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
527
XLfriend777
XLfriend777
L
  • Question Question
MSGBOX WHEN A CELL MEETS 2 CONDITIONS
Replies
3
Views
171
dermie_72
D
J
  • Solved
Sorting occurrences of Postal Code in column / Sorting by occurrence high to low
Replies
7
Views
393
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top