CODEWITHSUNDEEP
excelif-statementexcel-formulanested-if
Hery0502
Hery0502

Reputation: 91

Nested IF statements assistance

I made a small workbook that exactly mimics the original formulas I posted. The purpose of the workbook is to calculate the bonuses for each employee based on various inputted combinations (listed below). Here is how my workbook is laid out:

  1. Data Tab
  2. Bonus Tab
  3. Base Bonus Tab
  4. Summary Tab

Picture of the Data tab that I utilize to input the different combinations: Data Tab

List of the combinations:

I use cell C1 for an IF statement to dictate which formula to use based on the combinations above. (ex. numbers in B5 & B6 = 1)

=IF(AND($B$2<>"",$A$8<>"",$B$8<>""),5,IF(AND($B$2<>"",$B$3<>""),3,IF($B$3<>"",1,IF($B$5<>"",2))))

Picture of the Bonus Tab: Bonus Tab

Picture of the Base Bonus Tab: Base Bonus Tab

Picture of the Summary Tab: Summary Tab

I have created formulas for each combinations separately, but as soon as I try to combine them into one, I get the Too many arguments error message since combo 3 & combo 5 use false within the IF statement. I am attempting to avoid making a calculations table and would prefer utilizing one formula on the summary tab by employee in column B.

I will further explain combo 3 specifically - If the base bonus is less than the regular bonus, than multiply the base bonus * Employee % + Company&Vendor bonus * Company %. If base bonus is more than regular bonus, than multiply regular bonus * Employee % + Company&Vendor bonus * Company %.

Example:

Bobby

Base Bonus $50 < Regular Bonus $100

$50 * 50% employee % = $25 + $200 company + $0 vendor * 50% company = $100

Final answer is $125 if base bonus if < regular bonus

I will further explain combo 5 specifically - If the base bonus is less than the regular bonus, than multiply the base bonus * employees job title Employee % + Company&Vendor bonus * job title Company %. If base bonus is more than regular bonus, than multiply regular bonus * employees job title Employee % + Company&Vendor bonus * job title Company %.

Example:

Bobby

Base Bonus $50 < Regular Bonus $100

Find Bobby's Job title to determine Employee % & Company %

75% Employee % for Officers and 50% Company %

75% * $50 + 50% * ($200 Company & $0 Vendor) = $137.5

Final answer is $137.5 of base bonus if < regular bonus

Here is list of each formula by combo:

Combo 1:

=IF(Data!$C$1=1,Data!$B$3*Bonus!D2+Data!$B$4*(Bonus!E2+Bonus!F2))

Combo 2:

=IF(Data!$C$1=2,IF(SUM(Bonus!B2:C2)<1,0,1)*Data!$B$6+Data!B5))

Combo 3:

=IF(Data!$C$1=3,IF(VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)<VLOOKUP(Summary!A2,Bonus!$A$2:$D$5,4,FALSE),VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)*Data!$B$3+Data!$B$4*(Bonus!E2+Bonus!F2),VLOOKUP(A2,Bonus!$A$2:$D$5,4,FALSE)*Data!$B$3+Data!$B$4*(Bonus!E2+Bonus!F2)))

Combo 5:

IF(Data!$C$1=5,IF(VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)<VLOOKUP(Summary!A2,Bonus!$A$2:$D$5,4,FALSE),VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,2,FALSE)+(Bonus!E2+Bonus!E2)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,3,FALSE),VLOOKUP(A2,Bonus!$A$2:$D$5,4,FALSE)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,2,FALSE)+(Bonus!E2+Bonus!E2)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,3,FALSE)))

Hopefully this is a lot more clear than my initial post and can easily be replicated. Let me know if you have any questions, Thank you!

Upvotes: 1

Views: 158

Answers (1)

virtualdvid
virtualdvid

Reputation: 2421

I found some issues in your formulas

  1. Combo3 In the else option you have A2 following the pattern it should be Summary!A2

  2. Combo5 the sum is in the same cell you have (Bonus!E2+Bonus!E2) it should be (Bonus!E2+Bonus!F2)

  3. I didn't record the initial nested If to compare why it is not working. I got this:

Final Nested:

    =IF(Data!$C$1=1,Data!$B$3*Bonus!D2+Data!$B$4*(Bonus!E2+Bonus!F2),IF(Data!$C$1=2,IF(SUM(Bonus!B2:C2)<1,0,1)*Data!$B$6+Data!B5,IF(Data!$C$1=3,IF(VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)<VLOOKUP(Summary!A2,Bonus!$A$2:$D$5,4,FALSE),VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)*Data!$B$3+Data!$B$4*(Bonus!E2+Bonus!F2),VLOOKUP(Summary!A2,Bonus!$A$2:$D$5,4,FALSE)*Data!$B$3+Data!$B$4*(Bonus!E2+Bonus!F2)),IF(VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)<VLOOKUP(Summary!A2,Bonus!$A$2:$D$5,4,FALSE),VLOOKUP(Summary!A2,'Base Bonus'!$A$2:$B$5,2,FALSE)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,2,FALSE)+(Bonus!E2+Bonus!F2)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,3,FALSE),VLOOKUP(B2,Bonus!$A$2:$D$5,4,FALSE)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,2,FALSE)+(Bonus!E2+Bonus!F2)*VLOOKUP(VLOOKUP(Summary!A2,Data!$A$16:$B$19,2,FALSE),Data!$A$8:$C$11,3,FALSE)))))

Upvotes: 1

Related Questions