Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple If's (2000)

    I have been working on a multiple IF statement but I'm getting messed up...I keep getting a window stating I've "entered too many arguments for this function". I haven't even finished entering all the IF, AND bits that I want! I have a total of 5 conditions that need met and I've managed two of them when this the above-referenced error pops up. Here's what I have so far and I'd appreciate it if someone can tell me what I've done wrong. Thank you!

    =IF(B6>=$B$2, "Diamond","",(IF((AND(B6>=$C$2, B6<$C$3)),"Platinum","")))

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple If's (2000)

    The syntax for the IF function is

    IF(condition,value_if_true,value_if_false)

    In your first IF function, the condition is B6>=$B$2. You have three arguments following after it, instead of two:
    1) "Diamond"
    2) ""
    3) The second IF function

    You should remove the "" and the comma after it.
    You can omit some parentheses, they are superfluous.
    Here is the correct formula:
    <code>
    =IF(B6>=$B$2,"Diamond",IF(AND(B6>=$C$2,B6<$C$3),"P latinum",""))</code>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple If's (2000)

    YOu have (as the error indicates too many arguments. An IF has 3:
    1) a condition
    2) what to do if condition is true
    3 what to do if condition is false

    =If(condition, truepart, falsepart)

    I think you want:

    =IF(B6>=$B$2, "Diamond",IF((AND(B6>=$C$2, B6<$C$3)),"Platinum",""))

    And to add more, replace the last "" with the new IF statement...

    Each succeeding IF is the "falsepart" of the statement and does not get the NULL. The null string ("") is added at the end for when none of the conditions are true...

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple If's (2000)

    Thank you Hans. Parentheses can really mix me up sometimes...I appreciate your explanation!

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Multiple If's (2000)

    Incidentally, from the look of your formula, I suspect a lookup table might be easier to maintain.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple If's (2000)

    Thanks Rory (and Steve). Rory, I had considered a lookup table after reading about it somewhere but wasn't sure how to get it going given the "greater than or equal to" and "less than" parameters. Although I have this formula solved now, could you give me a point in the right direction for future reference on how I could do this?

    Thanks!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple If's (2000)

    See the attached example. The lookup table is in the upper left corner of the sheet.
    Enter a value in the yellow cell to see the corresponding description in the cell below. This cell uses the formula
    <code>
    =VLOOKUP(B11,A2:B7,2)
    </code>
    B11 is the yellow cell.
    A2:B7 is the lookup table.
    2 specifies that the value to be returned must be retrieved from the second column.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple If's (2000)

    The "aha" factor just kicked in! Thank you Hans...I like that solution way better than my very messy looking formula!

Posting Permissions

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