length of formula

Topic

New Reply

Is there a maximum for a formula?

I am trying to have excel look at the first two digits of a date…=month(cell) and then I have a formula to tell me the NAME of the month..

=If(c2=1,”JAN”,if(c2=2, “FEB”,….

it works until I get to Aug…then I get an error message telling me my formula is incorrect and it highlights at Aug.

Thanks for any help!

Reply | Quote

Replies

You’re running into the maximum of 7 levels of nested functions.
If you have a date in cell B2, you can either format it with the custom format mmm, or link to it in C2 with the formula =B2 and format C2 as mmm.
Yet another possibility is to use =TEXT(B2,”mmm”) in C2.
If you prefer to have the name in upper case: =UPPER(TEXT(B2,”mmm”))

Reply | Quote

There is a maximum of 8 nested IFs.

You could use a formula like
=HLOOKUP(C2,{1,2,3,4,5,6,7,8,9,10,11,12;”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”},2,TRUE)
to achieve the result you want
You could replace the array {1,2,3,4,5,6,7,8,9,10,11,12;”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”} with a references to cells on the worksheet that contain these values.

Reply | Quote

Or you could set up a small table elsewhere and use the LOOKUP function, or for more versatility, INDEX/MATCH combo.

Reply | Quote