Overcoming the restriction of 7 nested IFs in Excel XP. Is it possible?

[pcw]

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.

 

[Dave F]

Just do this to extract the three-letter month: =TEXT(MONTH(D18),"mmm") where
D18 contains 1/2/2007, etc.

Dave

 

[Bob Phillips]

See response in public.excel

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Toppers]

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

 

[bj]

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","")))))

 

[Dave F]

I think I misunderstood your post. The other responses sound more useful to
you.

Dave

 

[Bob Phillips]

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)

 

[Harlan Grove]

Better than mine, but we should have gone to

=CHOOSE(MONTH(B10),"X","Y","Z","XX","YY","ZZ","XXX","YYY","ZZZ",
"XXXX","YYYY","ZZZZ")

Why not

=REPT(CHAR(88+MOD(MONTH(B10)-1,3)),INT((MONTH(B10)+2)/3))

?

 

[pcw]

Thanks bj. It works perfectly. Much appreciated.

pc

 

[Leo Heuser]

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

 

[Bernd]

Or

=MID("XXXXYYYYZZZZ",1+4*(MOD(MONTH(A1)-1,3)),(MONTH(A1)+2)/3)

Regards,
Bernd