Error message with nesting

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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
Click to expand...

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
 
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."
 
Pete said:
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,""))
Click to expand...

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),"")
 
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."
Click to expand...

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
 
PS.... I said:
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),"")
Click to expand...

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}),"")
 
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}),"")
Click to expand...

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