Too many arguments

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

Here's my formula, which works wonderfully:
=IF(X16=2,H$10,IF(X16=3,H$11,IF(X16=4,H$12,IF(X16=5,H$13,IF
(X16=6,H$14,IF(X16=7,H$15,H$8))))))

But if none of those statements are true, I want the cell
to remain blank, so I added a comma and two quotes, and it
says now I've too many arguments, i.e.
=IF(X16=2,H$10,IF(X16=3,H$11,IF(X16=4,H$12,IF(X16=5,H$13,IF
(X16=6,H$14,IF(X16=7,H$15,H$8,""))))))

I didn't think ,"" was an argument. How many arguments
are you allowed anyway? Can anyone see a work-around?
 
Connie,

You are allowed 7 nested IF statements.

Here's one way,

=IF(AND(X16>=2,X16<=7), INDEX(H10:H15,X16),"")

Hope that helps.

Regards,
Kevin
 
Connie

Try this:

=IF(X16="","",IF(X16=2,H$10,IF(X16=3,H$11,IF(X16=4,H$12,IF(X16=5,H$13,IF(X16
=6,H$14,IF(X16=7,H$15,H$8)))))))

George Gee

*Connie* has posted this message:
 
Right now, if none of the conditional statements are TRUE, the
function returns the value in H$8.

You can replace H$8 with "" if you wish.

IF() takes 3 arguments:

=IF(<condition>,<true branch>,<false branch>)

Take a look at the definition of argument in XL Help. "" is an
example of a text value argument.

Note that you could simplify your function a bit:

=IF(AND(X16>=2,X16<=7),OFFSET(H$10,X16-2,0),"")
 
Using your original formula

=IF(X16=2,H$10,IF(X16=3,H$11,IF(X16=4,H$12,IF(X16=5,H$13,IF(X16=6,H$14,IF(X1
6=7,H$15,""))))))

or another way in case there will be more nesting

=IF(ISNUMBER(MATCH(X16,{2;3;4;5;6;7},0)),INDEX($H$10:$H$15,MATCH(X16,{2;3;4;
5;6;7},0)),"")

note that you have no use of H8 anymore if you want it to be blank if no
conditions were true
 
Oops! I didn't even notice that! Replacing the H$8
with "" works. The H8 cell was a blank cell. Thank you!
 
This was indeed the problem, and I hadn't even noticed
that H$8 being the return value. Thank you!
 
Hi,

If statement has three arguments, IF(logical expression, If True , If False). So if you add arguments more than three to IF statement, you get an error message.

In your first formula you already declared that what value the cell is going to has if none of the statements are True (H$8).

So you need to add one more if statement into last if statement instead of H$8.


--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Connie" <[email protected]>, iletide sunu yazdi Here's my formula, which works wonderfully:
=IF(X16=2,H$10,IF(X16=3,H$11,IF(X16=4,H$12,IF(X16=5,H$13,IF
(X16=6,H$14,IF(X16=7,H$15,H$8))))))

But if none of those statements are true, I want the cell
to remain blank, so I added a comma and two quotes, and it
says now I've too many arguments, i.e.
=IF(X16=2,H$10,IF(X16=3,H$11,IF(X16=4,H$12,IF(X16=5,H$13,IF
(X16=6,H$14,IF(X16=7,H$15,H$8,""))))))

I didn't think ,"" was an argument. How many arguments
are you allowed anyway? Can anyone see a work-around?
 
Back
Top