expression length

[tcek]

What is the maximum length that an "IF" function expression. I have an
expression that gives and effor message and I think excel is attempting to
truncate it.

 

[Niek Otten]

What formula and what error message

 

[Don Guillett]

Before xl 2007, 7 nested ifs' Have a look in the help index for VLOOKUP

 

[Roger Govier]

Hi

Which version of Excel?
For versions below Xl2007, you can only have 7 levels of nesting.
XL2007 allows up to 64 levels of nesting (not to be recommended).
Show us your formula, and explain what you are trying to do.
There is probably an easier way using Vlookup.

 

[tcek]

=IF(AND(OR(B15="Unknown",B15="1",B15="2",B15="3"),OR(B17="A",B17="B")),6,IF(AND(OR(B15="4",B15="5"),OR(B17="A",B17="B")),7,IF(AND(OR(B15="6",B15="7",B15="8",B15="9"),OR(B17="A",B17="B")),8,5)))

I want to add a "C" for B17 with different resulting values for "Unknown",
"1", "2", etc in B15. The actual formulat has longer texts for 1, 2, A and B.

 

[Don Guillett]

Here are a couple of ideas you can play with to shorten
=LOOKUP(B15,{1,6;3,7;5,9})
1or 2 = 6, etc
=IF(B17<"c",1,2)

 

[tcek]

Excellent...Thank you much

Here are a couple of ideas you can play with to shorten
=LOOKUP(B15,{1,6;3,7;5,9})
1or 2 = 6, etc
=IF(B17<"c",1,2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)