+ Reply to Thread
Results 1 to 8 of 8

Nested IF functions not working together

  1. #1
    Registered User
    Join Date
    10-25-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    34

    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!
    Attached Files Attached Files
    Last edited by Stuepef; 01-04-2018 at 12:24 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,630

    Re: Nested IF functions not working together

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-25-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    34

    Re: Nested IF functions not working together

    thank you for the post. I built the formula in C1 to account the different possible combinations and that is how excel knows which formula to use. The way combo 3 & combo 5 are setup, they need the false part of the IF statement making it hard to combine all of the formulas together. Open to any suggestions.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,630

    Re: Nested IF functions not working together

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

  5. #5
    Registered User
    Join Date
    10-25-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    34

    Re: Nested IF functions not working together

    Yes the formatting of the IF statement will not work when combo 3 & 5 both have a false value and I understand that. I am looking for ideas of how I can make the formula work to avoid making a secondary calculation table to pull from.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,630

    Re: Nested IF functions not working together

    A couple of things you could look at:

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

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,630

    Re: Nested IF functions not working together

    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.

  8. #8
    Registered User
    Join Date
    10-25-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    34

    Re: Nested IF functions not working together

    how would I use the MIN function for that specific formula?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1