Error message with nesting

[Pete]

I get the following error message when entering this formula...

"You've entered too many arguments for this function."

I need the entire formula.

=IF(AND(I3=3),6,IF(AND(I3=5),1),IF(AND(I3=7),4),IF(AND(I3="A"),7),IF(AND(I3="C"),0),IF(AND(I3="G"),3),IF(AND(I3="K"),8)+,F(AND(I3="R"),5),IF(AND(I3="T"),9),IF(AND(I3="X"),2,""))

Help!

Pete

 

[Bruce Bowler]

I get the following error message when entering this formula...

"You've entered too many arguments for this function."

I need the entire formula.

=IF(AND(I3=3),6,IF(AND(I3=5),1),IF(AND(I3=7),4),IF(AND(I3="A"),7),IF(AND (I3="C"),0),IF(AND(I3="G"),3),IF(AND(I3="K"),8)+,F(AND(I3="R"),5),IF(AND
(I3="T"),9),IF(AND(I3="X"),2,""))

Help!

Pete

First comment would be that I think (I haven't done an exhaustive check)
that none of your AND()s are needed. Looking at the first one, AND(I3=3)
is equivalent to I3=3, so I think you can simplify the formula quite a
bit. Second, look at what follows the '+' - is there a typo in that part
of the equation?

Bruce

 

[Pete]

Hi, yes, the "+" was a typo.

Here is what I canged it to..
=IF((I3=3),6,IF((I3=5),1,IF((I3=7),4,IF((I3="A"),7,IF((I3="C"),0,IF((I3="G"),3,IF((I3="K"),8,IF((I3="R"),5,IF((I3="T"),9,IF((I3="X",2,""))

But now I'm getting the following error message:

"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

 

[joeu2004]

I get the following error message when entering this formula...
"You've entered too many arguments for this function."
I need the entire formula.
=IF(AND(I3=3),6,IF(AND(I3=5),1),IF(AND(I3=7),4),IF(AND(I3="A"),7),
IF(AND(I3="C"),0),IF(AND(I3="G"),3),IF(AND(I3="K"),8)+,
F(AND(I3="R"),5),IF(AND(I3="T"),9),IF(AND(I3="X"),2,""))

Perhaps you want:

=IF(I3=3,6,IF(I3=5,1,IF(I3=7,4,IF(I3="A",7,IF(I3="C",0,
IF(I3="G",3,IF(I3="K",8,IF(I3="R",5,IF(I3="T",9,IF(I3="X",2,""))))))))))

But that is tedious to type and error-prone. Also, Excel 2003 and earlier
will complain about the number of nested functions. (No problem if you do
not care about Excel 2003 compatibility.)

Alternatively, for Excel 2007 and later, try:

=IFERROR(VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")

Be careful with comma and semicolon separators. Reverse them if your region
uses semicolon to separate function parameter.

For Excel 2003 and earlier, ostensibly the lookup must be duplicated :-(.
You might write:

=IF(ISERROR(VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE))=FALSE,
VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")

or

=IF(OR(I3={3,5,7,"A","C","G","K","R","T","X"}),
VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")

 

[Bruce Bowler]

Hi, yes, the "+" was a typo.

Here is what I canged it to..
=IF((I3=3),6,IF((I3=5),1,IF((I3=7),4,IF((I3="A"),7,IF((I3="C"),0,IF ((I3="G"),3,IF((I3="K"),8,IF((I3="R"),5,IF((I3="T"),9,IF((I3="X",2,""))

But now I'm getting the following error message:

"the specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format."

You still have more parens than you need...

=IF(I3=3,6,IF(I3=5,1,IF(I3=7,4,IF(I3="A",7,IF(I3="C",0,IF
(I3="G",3,IF(I3="K",8,IF(I3="R",5,IF(I3="T",9,IF(I3="X",2,""))

eliminates some, but has the problem of 10 '(' and only 2 ')'

It might be easier to help if you explained in English (as opposed to
formula) what you're trying to do and if this is a "one off" formula or if
this is going to be used "lots of places".

On the surface, it looks like you might be better with a lookup table
rather than an inordinately complicated nested IF but it's hard to tell
without more info.

Bruce

 

[joeu2004]

For Excel 2003 and earlier, [... you] might write: [....]
=IF(OR(I3={3,5,7,"A","C","G","K","R","T","X"}),
VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"")

Actually, for this paradigm, I think LOOKUP is more readable. To wit:

=IF(OR(I3={3,5,7,"A","C","G","K","R","T","X"}),
LOOKUP(I3,{3,5,7,"A","C","G","K","R","T","X"},{6,1,4,7,0,3,8,5,9,2}),"")

 

[Pete]

PS.... I wrote: > For Excel 2003 and earlier, [... you] might write: [.....] > =IF(OR(I3={3,5,7,"A","C","G","K","R","T","X"}), > VLOOKUP(I3,{3,6;5,1;7,4;"A",7;"C",0;"G",3;"K",8;"R",5;"T",9;"X",2},2,FALSE),"") Actually, for this paradigm, I think LOOKUP is more readable. To wit: =IF(OR(I3={3,5,7,"A","C","G","K","R","T","X"}), LOOKUP(I3,{3,5,7,"A","C","G","K","R","T","X"},{6,1,4,7,0,3,8,5,9,2}),"")

Hi - the lookup worked awesome! Thank you for the help!!!