Results 1 to 8 of 8
Thread: Multiple If's (2000)

20071028, 01:48 #1
 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 abovereferenced 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","")))

20071028, 01:55 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20071028, 01:59 #3
 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

20071028, 02:02 #4
 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!

20071029, 13:43 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,353
 Thanks
 4
 Thanked 229 Times in 210 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

20071029, 23:25 #6
 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!

20071029, 23:34 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20071030, 02:40 #8
 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!