ExcelBanter - Q about Excel 2007

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Q about Excel 2007 (https://www.excelbanter.com/excel-discussion-misc-queries/160604-q-about-excel-2007-a.html)

Q about Excel 2007

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

Q about Excel 2007

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

"Jim May" wrote in message
...
Can you now enter more than 7 nested If's in this version?

Q about Excel 2007

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

"Jim May" wrote in message
...
Can you now enter more than 7 nested If's in this version?

Q about Excel 2007

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

"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

"Jim May" wrote in message
...
Can you now enter more than 7 nested If's in this version?

Q about Excel 2007

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 he

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
|
|
|
| "Jim May" wrote in message
| ...
| Can you now enter more than 7 nested If's in this version?
|
|

Q about Excel 2007

Q about Excel 2007

van de --- can be

"Niek Otten" wrote in message ...
|A good tutorial about VLOOKUP van de found he
|
| http://www.contextures.com/xlFunctions02.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Jim May" wrote in message ...
|| 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
||
||
||
|| "Jim May" wrote in message
|| ...
|| Can you now enter more than 7 nested If's in this version?
||
||
||
|
|

Q about Excel 2007

Thanks Niek for the info and the reference..
Jim

"Niek Otten" wrote:

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 he

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
|
|
|
| "Jim May" wrote in message
| ...
| Can you now enter more than 7 nested If's in this version?
|
|

Q about Excel 2007

Hi Niek -

Are you teaching us Dutch?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Niek Otten" wrote in message
...
van de --- can be

"Niek Otten" wrote in message
...
|A good tutorial about VLOOKUP van de found he
|
| http://www.contextures.com/xlFunctions02.html
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Jim May" wrote in message
...
|| 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
||
||
||
|| "Jim May" wrote in message
|| ...
|| Can you now enter more than 7 nested If's in this version?
||
||
||
|
|

Q about Excel 2007

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","Ma y",IF(A1="Jun","June",IF(A1="Jul","July",IF(A1="Au g","August",IF(A1="Sept","September",IF(A1="Oct"," October",IF(A1="Nov","November",IF(A1="Dec","Decem ber","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

http://PeltierTech.com/
_______

"Jim May" wrote in message
...
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

"Jim May" wrote in message
...
Can you now enter more than 7 nested If's in this version?

Q about Excel 2007

LOL!

I seem to have moved my chair some centimeters

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jon Peltier" wrote in message ...
| Hi Niek -
|
| Are you teaching us Dutch?
|
| - Jon
| -------
| Jon Peltier, Microsoft Excel MVP
| Tutorials and Custom Solutions
| Peltier Technical Services, Inc. - http://PeltierTech.com
| _______
|
|
| "Niek Otten" wrote in message
| ...
| van de --- can be
|
|
| "Niek Otten" wrote in message
| ...
| |A good tutorial about VLOOKUP van de found he
| |
| | http://www.contextures.com/xlFunctions02.html
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "Jim May" wrote in message
| ...
| || 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
| ||
| ||
| ||
| || "Jim May" wrote in message
| || ...
| || Can you now enter more than 7 nested If's in this version?
| ||
| ||
| ||
| |
| |
|
|
|
|