Too many arguments

[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?

 

[Leo Heuser]

Connie

You have 3 arguments in
IF(X16=7,H$15,H$8,"")
instead use
IF(X16=7,H$15,"")

 

[Kevin Stecyk]

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

 

[George Gee]

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:

 

[J.E. McGimpsey]

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

 

[Peo Sjoblom]

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

 

[Connie]

Oops! I didn't even notice that! Replacing the H$8
with "" works. The H8 cell was a blank cell. Thank you!

 

[Coinnie]

This was indeed the problem, and I hadn't even noticed
that H$8 being the return value. Thank you!

 

[Haldun Alay]

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?

 

[Leo Heuser]

You're welcome.
I appreciate your feedback.

LeoH