Multiple If’s (2000)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

=VLOOKUP(B11,A2:B7,2)

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote