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. |
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 |