Q about Excel 2007

[Jim May]

Can you now enter more than 7 nested If's in this version?

 

[Roger Govier]

Hi Jim

Yes you can.
I think the limit is now 256, but I don't think I would want to use the
limit!!!<bg>

 

[Jim May]

I'm still in Excel 2003, I was just wondering. I've recently written a
formula with 6 nested if's, and now suddenly need a 7th - Ouch !!

Overall suggestions seem to be to convert to a Vlookup method, but not sure
at this point how to Set up my scenario table.

Thanks Roger !!

 

[Jim May]

Further to my just-posted-reply... In the Worksheet Functions group I just
posted
an issue I'm having in the move from 6 if's to 7..
Under Subject:

Editing and Moviing from 6 If's to 7 Ifs (Nested)

Jim May

 

[Niek Otten]

Hi Roger, Jim,

Actually the limit in Excel 2007 is 64 levels, but of course even that is far too many to be able to understand a formula.
The limit applies to all functions and mixtures of functions, not just IF's. With just IF's, even 7 levels exceed my capability
for understanding!
I assume it is only used because users are not familiar with the alternatives.
If so, they might benefit from looking here:

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Govier" <rogerattechnology4NOSPAMu.co.uk> wrote in message | Hi Jim
|
| Yes you can.
| I think the limit is now 256, but I don't think I would want to use the
| limit!!!<bg>
|
| --
| Regards
| Roger Govier
|
|
|
| | > Can you now enter more than 7 nested If's in this version?
|
|

 

[Niek Otten]

A good tutorial about VLOOKUP van de found here:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm still in Excel 2003, I was just wondering. I've recently written a
| formula with 6 nested if's, and now suddenly need a 7th - Ouch !!
|
| Overall suggestions seem to be to convert to a Vlookup method, but not sure
| at this point how to Set up my scenario table.
|
| Thanks Roger !!
|
|
| "Roger Govier" wrote:
|
| > Hi Jim
| >
| > Yes you can.
| > I think the limit is now 256, but I don't think I would want to use the
| > limit!!!<bg>
| >
| > --
| > Regards
| > Roger Govier
| >
| >
| >
| > | > > Can you now enter more than 7 nested If's in this version?
| >
| >
| >

 

[Niek Otten]

van de ---> can be


|A good tutorial about VLOOKUP van de found here:
|
| http://www.contextures.com/xlFunctions02.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| I'm still in Excel 2003, I was just wondering. I've recently written a
|| formula with 6 nested if's, and now suddenly need a 7th - Ouch !!
||
|| Overall suggestions seem to be to convert to a Vlookup method, but not sure
|| at this point how to Set up my scenario table.
||
|| Thanks Roger !!
||
||
|| "Roger Govier" wrote:
||
|| > Hi Jim
|| >
|| > Yes you can.
|| > I think the limit is now 256, but I don't think I would want to use the
|| > limit!!!<bg>
|| >
|| > --
|| > Regards
|| > Roger Govier
|| >
|| >
|| >
|| > || > > Can you now enter more than 7 nested If's in this version?
|| >
|| >
|| >
|
|

 

[Jim May]

Thanks Niek for the info and the reference..
Jim

 

[Jon Peltier]

Hi Niek -

Are you teaching us Dutch?

- Jon

 

[Jon Peltier]

Sometimes you can fake more levels if you use a binary approach to your ifs.

Instead of this example (which not only exceeds the limit of seven, but is
also a fine candidate for VLOOKUP)

=IF(A1="Jan","January",IF(A1="Feb,"February",IF(A1="Mar","March",IF(A1="Apr","April",IF(A1="May","May",IF(A1="Jun","June",IF(A1="Jul","July",IF(A1="Aug","August",IF(A1="Sept","September",IF(A1="Oct","October",IF(A1="Nov","November",IF(A1="Dec","December","No
Month"))))))))))))

try this:

=IF(OR(A13="Jan",A13="Feb",A13="Mar",A13="Apr",A13="May",A13="Jun"),
IF(OR(A13="Jan",A13="Feb",A13="Mar"),
IF(A13="Jan",
"January",
IF(A13="Feb",
"February",
"March")),
IF(OR(A13="Apr",A13="May",A13="Jun"),
IF(A13="Apr",
"April",
IF(A13="May",
"May",
"June")),
"No Month")),
IF(OR(A13="Jul",A13="Aug",A13="Sep",A13="Oct",A13="Nov",A13="Dec"),
IF(OR(A13="Jul",A13="Aug",A13="Sep"),
IF(A13="Jul",
"July",
IF(A13="Aug",
"August",
"September")),
IF(OR(A13="Oct",A13="Nov",A13="Dec"),
IF(A13="Oct",
"October",
IF(A13="Nov",
"November",
"December")))),
"No Month"))

I've written it in a kind of outline form in a text editor to help me keep
the nesting levels straight.

This example illustrates the binary approach (split the solution space in
half, then split each half into halves, etc.), and also why you don't want
to use this many levels, which I think is six, but it's hard to count.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Niek Otten]

LOL!

I seem to have moved my chair some centimeters


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Niek -
|
| Are you teaching us Dutch?
|
| - Jon
| -------
| Jon Peltier, Microsoft Excel MVP
| Tutorials and Custom Solutions
| Peltier Technical Services, Inc. - http://PeltierTech.com
| _______
|
|
| | > van de ---> can be
| >
| >
| > | > |A good tutorial about VLOOKUP van de found here:
| > |
| > | http://www.contextures.com/xlFunctions02.html
| > |
| > | --
| > | Kind regards,
| > |
| > | Niek Otten
| > | Microsoft MVP - Excel
| > |
| > | > || I'm still in Excel 2003, I was just wondering. I've recently written a
| > || formula with 6 nested if's, and now suddenly need a 7th - Ouch !!
| > ||
| > || Overall suggestions seem to be to convert to a Vlookup method, but not
| > sure
| > || at this point how to Set up my scenario table.
| > ||
| > || Thanks Roger !!
| > ||
| > ||
| > || "Roger Govier" wrote:
| > ||
| > || > Hi Jim
| > || >
| > || > Yes you can.
| > || > I think the limit is now 256, but I don't think I would want to use
| > the
| > || > limit!!!<bg>
| > || >
| > || > --
| > || > Regards
| > || > Roger Govier
| > || >
| > || >
| > || >
| > || > | > || > > Can you now enter more than 7 nested If's in this version?
| > || >
| > || >
| > || >
| > |
| > |
| >
| >
|
|