If function, need nested more than seven levels


H

HGood

I have many Excel tables with columns with Dates in them in mmm-yyyy format.

I'd like to insert a new column to the right of each of these columns and
enter a formula in them to convert these formats. In the new column I'd like
the result to be Q1, Q2, Q3, or Q4.

I tried "=IF(MONTH(C3)=9,"Q3","")", and it works for the new column, if it
is September, it results with Q3.

But since there are 12 months in the year, I'd need to nest 12 of these IF
functions to cover all my bases, but Excel only lets me nest 7 deep.

How can I come up with an alternate way of doing this so it will evaluate
all 12 months in one formula, and each cell in the column result in either
Q1 to Q4.

Thanks,

Harold
 
Ad

Advertisements

H

Harald Staff

Hi Harold

There's a numeric logic here (higher month; higher Q) that you can take
advantage of. Try
="Q"&INT((MONTH(C3)+2)/3)

HTH. Best wishes Harald
 
H

HGood

Fantastic, this did the job, thanks so much, never thought of using INT for
this.

Harold
 
Ad

Advertisements

F

Fiona O'Grady

Hi Harold,

A less elegant solution than Harald's, but

=IF(MONTH(C2)<=3,"Q1",(IF(MONTH(C2)<=6,"Q2",(IF(MONTH(C2)<=9,"Q3",(IF(MONTH(C2)<=12,"Q4","")))))))

also works if you want to keep your IF statements - if the month is greater
than 3 it will automatically move to the next if statement and will keep
going until it finds the correct quarter.

Fiona
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top