| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overcoming the restriction of 7 nested IFs in Excel XP. Is it possible?
Any suggestion on this would be greatly appreciated.
I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it pos
Just do this to extract the three-letter month: =TEXT(MONTH(D18),"mmm") where
D18 contains 1/2/2007, etc. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "pcw" wrote: Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it possible?
See response in public.excel
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pcw" wrote in message ... Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it pos
TRY:
=LOOKUP(MONTH(B10),{1,2,3,4,5,6,7,8,9,10,11,12},{" X","Y","Z","XX","YY","ZZ","XXX","YYY","ZZZ","XXXX" ,"YYYY","ZZZZ"}) HTH "pcw" wrote: Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it pos
This is awkward way to do it but the error is the "+", An "&" should be used
and I think you need to add some ""s =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX","")))))))& IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY", IF(MONTH(B10)=12,"ZZZZ",""))))) "pcw" wrote: Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it
I think I misunderstood your post. The other responses sound more useful to
you. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Dave F" wrote: Just do this to extract the three-letter month: =TEXT(MONTH(D18),"mmm") where D18 contains 1/2/2007, etc. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "pcw" wrote: Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it pos
Better than mine, but we should have gone to
=CHOOSE(MONTH(B10),"X","Y","Z","XX","YY","ZZ","XXX ","YYY","ZZZ","XXXX","YYYY","ZZZZ") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Toppers" wrote in message ... TRY: =LOOKUP(MONTH(B10),{1,2,3,4,5,6,7,8,9,10,11,12},{" X","Y","Z","XX","YY","ZZ","XXX","YYY","ZZZ","XXXX" ,"YYYY","ZZZZ"}) HTH "pcw" wrote: Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it pos
"Bob Phillips" wrote...
Better than mine, but we should have gone to =CHOOSE(MONTH(B10),"X","Y","Z","XX","YY","ZZ","XX X","YYY","ZZZ", "XXXX","YYYY","ZZZZ") Why not =REPT(CHAR(88+MOD(MONTH(B10)-1,3)),INT((MONTH(B10)+2)/3)) ? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it pos
Thanks bj. It works perfectly. Much appreciated.
pc "bj" wrote in message ... This is awkward way to do it but the error is the "+", An "&" should be used and I think you need to add some ""s =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX","")))))))& IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY", IF(MONTH(B10)=12,"ZZZZ",""))))) "pcw" wrote: Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it possible?
"pcw" skrev i en meddelelse
... Any suggestion on this would be greatly appreciated. I thought there was a way to overcome the limit of 7 nested IFs in the one formula. My worksheet is setup like this: Column A hold dates for an entire year. The dates are entered in the following format: 1-2-07 In Column B I need to setup an IF formula that checks if the month is either Jan, Feb, March and so on to December. If the month is Jan return "X", if the month is Feb return "Y", if the month is March return "Z", and so on to December. In Column B the formula works fine with 7 IFs =IF(MONTH(B10)=1,"Jan", IF(MONTH(B10)=2,"Feb", IF(MONTH(B10)=3,"March", IF(MONTH(B10)=4,"April", IF(MONTH(B10)=5,"May", IF(MONTH(B10)=6,"June", IF(MONTH(B10)=7,"July",False))))))) Below is the formula I have setup for 11 Ifs. I use the IF formula above, then I position the text cursor at the end of the formula (outside the last closing parenthesis), then I select IF in the Functions dropdown list located above A; as soon as I do this Excel automatically inserts a + sign. I leave the + sign there then enter another 4 Ifs. =IF(MONTH(B10)=1,"X, IF(MONTH(B10)=2,"Y", IF(MONTH(B10)=3,"Z", IF(MONTH(B10)=4,"XX", IF(MONTH(B10)=5,"YY", IF(MONTH(B10)=6,"ZZ", IF(MONTH(B10)=7,"XXX",False)))))))+ IF(MONTH(B10)=8,"YYY", IF(MONTH(B10)=9,"ZZZ", IF(MONTH(B10)=10,"XXXX", IF(MONTH(B10)=11,"YYYY","ZZZZ")))) When I press Enter Excel displays an error message telling me there is an error in the formula. Would anybody know if this is feasible? Thank you very much in advance. Pc. Pc One more option. =REPT(INDEX({"X","Y","Z"},MOD(MONTH(A1)-1,3)+1),INT((MONTH(A1)-1)/3+1)) With positive integers (1 and forward) in A1, try for example: =REPT(INDEX({"X","Y","Z","A"},MOD(A1-1,4)+1),INT((A1-1)/4+1)) -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overcoming the restriction of 7 nested IFs in Excel XP. Is it possible?
Or
=MID("XXXXYYYYZZZZ",1+4*(MOD(MONTH(A1)-1,3)),(MONTH(A1)+2)/3) Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Overcoming Nested IF limits | Excel Discussion (Misc queries) | |||
| question about some excel restriction script running on ... | Excel Discussion (Misc queries) | |||
| question about some excel restriction script running on ... | Excel Discussion (Misc queries) | |||
| overcoming nested IF limitations...with VBA? | Excel Worksheet Functions | |||
| add restriction in Excel | Excel Worksheet Functions | |||
Linear Mode
