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

On Wed, 15 Aug 2012 09:10:08 -0700, Pete wrote:

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

On Wednesday, August 15, 2012 12:06:00 PM UTC-5, Bruce Bowler wrote:
On Wed, 15 Aug 2012 09:10:08 -0700, Pete wrote: 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

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

On Wed, 15 Aug 2012 11:09:59 -0700, Pete wrote:

On Wednesday, August 15, 2012 12:06:00 PM UTC-5, Bruce Bowler wrote:
On Wed, 15 Aug 2012 09:10:08 -0700, Pete wrote: 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

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(I 3="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 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}),"")

On Wednesday, August 15, 2012 1:45:38 PM UTC-5, joeu2004 wrote:
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!!!