Is my IF statement too long? (determining quarter)

[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((MO NTH(N$5))=5,M6+N6,IF((MONTH(N$5))=6,L6+M6+N6,IF((M ONTH(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"))))))))))))

[VBA Noob]

Hi,

If statements only allow 7 if's


Try using the choose function. for 1 you enter N6 for 2 enter M6+N6 etc

Allows 30 conditions

VBA Noob

[SteveG]

Not sure what constitutes an Error but you can try,

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

If the MONTH(N5) returns an error than the cell will populate with "Error", if N5 is blank than the cell will return blank if neither are true than it executes the CHOOSE function.

HTH

Steve

[Myriad_Rocker]

I just tried that...don't really understand how it works. Can you provide an example?

I got this to work, but taking it farther wouldn't work.

=CHOOSE(MONTH(A1)=1,"QTR1")

[Myriad_Rocker]

Not sure what constitutes an Error but you can try,

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

If the MONTH(N5) returns an error than the cell will populate with "Error", if N5 is blank than the cell will return blank if neither are true than it executes the CHOOSE function.

HTH

Steve

Nothing really constituted an error....I just put it in there as the final false clause.

That worked, though. I greatly appreciate it. I understand the CHOOSE function now!

[VBA Noob]

Hi,

Trying searching excel help or take a look at the attached link

http://www.meadinkent.co.uk/xloffsetchoose.htm

VBA Noob

[VBA Noob]

Glad I could help

VBA Noob

[Carim]

Hi,

Cell B1 formula
=IF(C1>0,C1,IF(A1=1,25,IF(A1=2,24,IF(A1=3,23,IF(A1=4,22,IF(A1=5,21,IF(A1=6,20,0)))))))

Cell C1 formula
=IF(D1>0,D1,IF(A1=7,19,IF(A1=8,18,IF(A1=9,17,IF(A1=10,16,IF(A1=11,15,IF(A1=12,14,0)))))))

Cell D1 formula
=IF(E1>0,E1,IF(A1=13,13,IF(A1=14,12,IF(A1=15,11,IF(A1=16,10,IF(A1=17,9,IF(A1=18,8,0)))))))

Cell E1 formula
=IF(F1>0,F1,IF(A1=19,7,IF(A1=20,6,IF(A1=21,5,IF(A1=22,4,IF(A1=23,3,IF(A1=24,2,0)))))))

Cell F1 formula
=IF(A1=25,1,IF(A1>50,"Greater than 50!!!",0))

HTH
Carim

[SteveG]

Glad to see you got it. If you are simply trying to sum based on what quarter the dates fall in you could try,

=SUM((I1=ROUNDUP(MONTH(L5:N5)/3,0))*L6:N6)

Where I1 is the quarter number you'd like to sum for. This is an array formula so it must be commited using Ctrl+Shift+Enter not just enter.

HTH

Steve