Nested IF functions not working together

I have a workbook where I calculate bonus by employee based on various combinations inputted into specific cells. I will do my best to describe each combinations:

Cell C1: IF formula for the specific combinations numbered 1,2,3,5

Please Login or Register  to view this content.

Employee % (cell B3) & Company % (cell B4) cells have numbers inputted = Combo 1
Employee $ (cell B5) & Company $ (cell B6) cells have numbers inputted = Combo 2
Base Salary (B2) is Yes & Employee % (cell B3) cells have been inputted = Combo 3
Base Salary (B2) is Yes & Job Title (A8) & Employee % (cell B8) cell have been inputted = Combo 5

Based on which combo is inputted dictates which formula is used to calculate the bonus.

I will further explain combo 3 - 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 is < regular bonus

I will further explain combo 5 - 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 is < regular bonus

The problem I am having is combo 3 & 5 both use the false statement within the nested IF formula causing the "Too Many Arguments" error message when I try to combine all of the formulas together. Within the attached spreadsheet, I labeled the formula for each combo, but I am trying to combine all of the formulas together into one long IF formula rather than separate formulas. I am trying to avoid creating a secondary table for calculations if possible, but open to ideas.

Thank you for the help!

Here's a thought to start with: when you replace the numbers in the formula in C1 with the formulae you have built, you do not need to include IF(C1=1 ... bit at the beginning, and you should not, therefore, need an IF FALSE variable at the end of any formula except in the very last formula that is nested. So, you need to build your nested formulae with this in mind.

Yes, I realise this. Let me try to put my thoughts into words.

What you have got to do is eliminate one of those IF FALSE sections ro that you can properly nest the formulae. There may be ways round this: have you thought about using the AND/OR functions? Ultimately you are aiming for this:

=IF(logical_test_1,value_if_true,IF(logical_test_2,value_if_true,IF(logical_test_3,value_if_true,IF(logical_test_4,value_if_true,IF(logical_test_5,value_if_true,value_if_false)))))

or maybe this, if the final IF FALSE value is the only other scenario available:

=IF(logical_test_1,value_if_true,IF(logical_test_2,value_if_true,IF(logical_test_3,value_if_true,IF(logical_test_4,value_if_true,value_if_false))))

A couple of things you could look at:

1. AND and OR operators.
2. CHOOSE function.

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)))

With this one, you might be able to employ the MIN function, for instance.