Is my IF statement too long? (1 Viewer)

[Myriad_Rocker]

I'm trying to add certain cells together depending on what month a particular cell is showing. The formula gives me an error unless I cut it down.

Could my formula be too long? If so, suggestions on what other options there are? It would be easier to use an "IN" type of statement but I don't think that works in an IF in Excel.

Here's my IF statement:

=IF((MONTH(N$5))=1,N6,IF((MONTH(N$5))=2,M6+N6,IF((MONTH(N$5))=3,L6+M6+N6,IF((MONTH(N$5))=4,N6,IF((MONTH(N$5))=5,M6+N6,IF((MONTH(N$5))=6,L6+M6+N6,IF((MONTH(N$5))=7,N6,IF((MONTH(N$5))=8,M6+N6,IF((MONTH(N$5))=9,L6+M6+N6,IF((MONTH(N$5))=10,N6,IF((MONTH(N$5))=11,M6+N6,IF((MONTH(N$5))=12,L6+M6+N6, "ERROR"))))))))))))

 

[Myriad_Rocker]

Got it from SteveG over at excelforum.com!

Posting here for the people that might search it.

=IF(ISERROR(MONTH(N5)),"Error",IF(ISBLANK(N5),"",CHOOSE(MONTH(N5),N6,M6+N6,L6+M6+N6,N6,M6+N6,L6+M6+N 6,N6,M6+N6,L6+M6+N6,N6,M6+N6,L6+M6+N6)))

 

[mdjks]

Your formula is 318 characters including spaces, Excel doesn't like to evaluate that many. Try a vlookup to get your varable. It looks like you are only using N or M+N or L+M+N. Try an simplifying your if statement. You should only have three choices. Use an Or statement, =IF(OR(B1="ok",B1="good"),"ok","not")

 

[shades]

Technically, a formula may contain 1024 characters. However, if a portion of the formula is another "complete formula" then the total limit goes down, often 255 characters.
________
CHRYSLER CROSSFIRE HISTORY

 

[yearightsure]

use the lookup formula (you can reference any other sheet)...much easier...or you choose()...depending on what you are doing. It looks like you are calculating a range of columns based upon a certain input (probably a period range or month number) to get a year-to-date amount. I'd use =choose() for this function.

 

[The_Bateman]

I'm trying to add certain cells together depending on what month a particular cell is showing. The formula gives me an error unless I cut it down.

Could my formula be too long? If so, suggestions on what other options there are? It would be easier to use an "IN" type of statement but I don't think that works in an IF in Excel.

Here's my IF statement:

=IF((MONTH(N$5))=1,N6,IF((MONTH(N$5))=2,M6+N6,IF((MONTH(N$5))=3,L6+M6+N6,IF((MONTH(N$5))=4,N6,IF((MONTH(N$5))=5,M6+N6,IF((MONTH(N$5))=6,L6+M6+N6,IF((MONTH(N$5))=7,N6,IF((MONTH(N$5))=8,M6+N6,IF((MONTH(N$5))=9,L6+M6+N6,IF((MONTH(N$5))=10,N6,IF((MONTH(N$5))=11,M6+N6,IF((MONTH(N$5))=12,L6+M6+N6, "ERROR"))))))))))))

I think Excel only supports upto 9 nested 'IF's

 

[shades]

Excel supports 7 nested IFs. However, by using named formulas, I have nested 13 IF statements. Not pretty, not easy to trouble shoot. It is much better/easier/more reliable/expandable to use lookup tables.
________
Growing marijuana

 

[vangogh228]

Try this

=if(mod(month(n5),3)=1,n6,if(mod(month(n5),3)=2,m6+n6,if(mod(month(n5),3)=0,l6+m6+n6,"error")))

--- --- ---

EDIT:
Actually, it might even be easier than that, if you guarantee the entries will always be dates:

=if(isblank(n6),"",if(mod(month(n5),3)=1,n6,if(mod(month(n5),3)=2,m6+n6,l6+m6+n6)))