Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q about Excel 2007
Can you now enter more than 7 nested If's in this version?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? | | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q about Excel 2007
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? | | | |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? || || || | | |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? | | |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? || || || | | |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? | || | || | || | | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting a chart from Excel 2007 to Word 2007 trouble | Charts and Charting in Excel | |||
How can you "save as" in excel 2007 (and word 2007)? | Excel Discussion (Misc queries) | |||
Exporting Quickbooks 2007 to Excel 2007 | Excel Worksheet Functions | |||
Problems with my links to an excel 2007 file from a word 2007 file | Links and Linking in Excel | |||
Office 2007 Pro Plus 2007 (Beta) - getting small red and green arr | Excel Discussion (Misc queries) |