+ Reply to Thread
Results 1 to 16 of 16

Problem with multiple IF statement

Hybrid View

  1. #1
    Registered User
    Join Date
    08-15-2007
    Posts
    10

    Problem with multiple IF statement

    I am trying to write a formula that will do multiple IF statements checking to see if words are written in certian boxes and if so to do certian things and I need help, never done multiple IF statements in one formula before and can't find anything in the books I have or on the web that pertains to this so any help would be appreciated and thank you in advance.

    PROBLEM:

    = IF(D30 = "Oceanic",
    IF(D35 = "Yes",
    Both True
    (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)),

    Oceanic = "Oceanic", D35 = "No"
    (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31)

    Oceanic = False, D35 = "Yes"
    ((d32 * 4) + (d33 * 5) + (d34 * 9) + (4500 + d31))

    Both False
    ((d32 * 4) + (d33 * 5) + (d34 * 9))

    =======================================================
    I got:

    = IF(D30 = "Oceanic", IF(D35 = "Yes", (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)), (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31), ((d32 * 4) + (d33 * 5) + (d34 * 9) + (4500 + d31)), ((d32 * 4) + (d33 * 5) + (d34 * 9))

    But it says too many arguments so any help on this is appreciated.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Here's one attempt...

    just make sure the math is right for all combinations....

    =((D32*4)+(D33*5)+(D34*9)+D31)*0.15+(D32*4)+(D33*5)+(D34*9)+D31+IF(D30="Oceanic",IF(D35="yes",4500,0),(D32*4)+(D33*5)+(D34*9)+IF(D30=FALSE,IF(D35="Yes",4500+D31,0)))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-15-2007
    Posts
    10
    =((D32*4)+(D33*5)+(D34*9)+D31)*0.15+(D32*4)+(D33*5)+(D34*9)+D31+IF(D30="Oceanic",IF(D35="yes",4500,0),(D32*4)+(D33*5)+(D34*9)+IF(D30=FALSE,IF(D35="Yes",4500+D31,0)))

    Ok here is the problem with that one, it will add up correctly but ONLY if D30 = "Oceanic", if not, the result is totally wrong, it will add up both the first lines and the If = True and almost double the correct expected results.

    I only use a 1 in D32 to test the results to make it easier:

    D30 = "Oceanic" & D35 = "Yes"
    Result: 4,504.6
    D30 = "Oceanic" & D35 = False
    Result: 4.6
    D30 = False & D35 = "Yes"
    Result: 8.6 But Correct Result should be: 4,504
    D30 = False & D35 = False
    Result: 8.6 But correct Result should be: 4

    So you see I am having problems trying to figure out the code for it since I am pretty new to Excel, I can do the basic things but multiple IF statements are beyond me. =(

    Thank you for the help anyway, it is appreciated. =)

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Does this work?

    =IF(D30="Oceanic",((D32*4)+(D33*5)+(D34*9)+D31)*0.15+(D32*4)+(D33*5)+(D34*9)+D31+IF(D35="yes",4500,0),IF(D30=FALSE,(D32*4)+(D33*5)+(D34*9)+IF(D35="Yes",4500+D31,0)))

    If not, please tell me what numbers you put in D31, D32, D33 and D34 to get the results you submitted above.

  5. #5
    Registered User
    Join Date
    08-15-2007
    Posts
    10
    =IF(D30="Oceanic",((D32*4)+(D33*5)+(D34*9)+D31)*0.15+(D32*4)+(D33*5)+(D34*9)+D31+IF(D35="yes",4500,0),IF(D30=FALSE,(D32*4)+(D33*5)+(D34*9)+IF(D35="Yes",4500+D31,0)))

    Nope, that's not it either, The Result comes out "False" instead of a calculation and narrowed it down to the IF(D30 = False, statement I believe.

    and I have no other information in any of the other boxes, just a 1(D32 = 1 * 4) in D32 to come up with those results.

    IF Oceaning AND IF "Yes" then (4 * .15) = 4.6 + 4,500 = 4,504.6
    IF Oceanic and IF(D35 = "No" then (4 * .15) + 4) = 4.6
    IF NOT Oceanic and IF = "Yes" then (4 + 4,500) = 4,504
    IF NOT Oceanic and IF(D35 = False"No" then Result = 4

    D32 = 1
    D33 = Blank
    D34 = Blank

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    See attached.

    With the last formula I gave you and a 1 entered in D32, I get the results that you gave....


    Note: I actually put the word FALSE in the cells....did you you mean blanks?


    or maybe you meant "NO" instead of FALSE?

    perhaps?

    =IF(D30="Oceanic",IF(D35="yes",(((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)),(((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31)),IF(D30="NO",IF(D35="Yes",((D32 * 4) + (D33 * 5) + (D34 * 9) + (4500 + D31)),((D32 * 4) + (D33 * 5) + (D34 * 9)))))
    Attached Files Attached Files
    Last edited by NBVC; 08-15-2007 at 02:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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