IF formula To Convert Months To Quarters

[Craig K.]

I created the following formula to convert a field with months (G:G) to what quarter of the year it falls in.

=IF(G2={"Jan","Feb","Mar"},1,IF(G2={"Apr","May","Jun"},2,IF(G2={"Jul","Aug","Sep"},3,4)))

It doesn't work, how do I get it to work?



*Edit/Correction: I should probably check to see if the formula works in all the cells I want it to first.

[Tony Valko]

If G2 contains the month name as a TEXT entry...

=CEILING(MONTH(1&G2)/3,1)

[oeldere]

with VLookup in a table.

put the months in column A and the quarter i column B

=Vlookup(g2,sheet2!a1:b12,2,0)

[djapigo]

This is my take... but it might be longer...

=ROUNDUP(MATCH(B5083,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)/3,0)

[Craig K.]

Tony, I just reverse engineered it and that formula is beautiful. I hate using formulas I don't understand.

Thank You!

[Tony Valko]

Tony, I just reverse engineered it and that formula is beautiful. I hate using formulas I don't understand.

Thank You!

Me too!

I do the same thing when I see something different. I "slice it and dice it" until I get it then I test it under all kinds of scenarios to see how it reacts.

This is how we learn.

Thanks for the feedback!

[djapigo]

Tony, nice trick with 1&G2... how did you know this can be read in as a date?

[Tony Valko]

I have many years of experience with Excel and I do tons of experimentation trying different things.

There must be something wrong with me because I love this stuff!

[daddylonglegs]

I'd go with Tony's suggestion but just in the interests of learning......

Your original formula will work OK with ORs, i.e.

=IF(OR(G2={"Jan","Feb","Mar"}),1,IF(OR(G2={"Apr","May","Jun"}),2,IF(OR(G2={"Jul","Aug","Sep"}),3,4)))

[Craig K.]

Longlegs - could you explain to me why the OR function makes it work?

[daddylonglegs]

For an IF function to work as required the "test" should normally give you a single TRUE/FALSE answer but when you use G2={"Jan","Feb","Mar"} that returns an "array" of TRUE/FALSE values, e.g. if G2 = "Feb" you get {FALSE,TRUE,FALSE}. In your formula you want TRUE if any of those is TRUE so that's an OR, either of these will give you a single TRUE/FALSE

=OR(G2={"Jan","Feb","Mar"})

or

=OR(G2="Jan",G2="Feb",G2="Mar")

[Craig K.]

I'm still trying to understand arrays and thought the way I had it written (without the OR()) would allow it to cycle through. Thanks for the lesson!