Formula Help Required

[PrashanthGM12345]

Hi gurus,

I am referring to the table in the attached excel file.
i have used the below formula in column A for automatically finding the quarter.
="Quarter "&CEILING(MONTH(B2),3)/3

As per the above formula its taking
Jan - Mar = Quarter 1
Apr - Jun = Quarter 2
Jul - Sep = Quarter 3
Oct - Dec = Quarter 4

But my requirement is
Apr - Jun = Quarter 1
Jul - Sep = Quarter 2
Oct - Dec = Quarter 3
Jan - Mar = Quarter 4

How do i achieve this? Please guide me.
I have attached the excel file for your reference.

 

[AliGW]

Try this instead:

="Quarter "&LOOKUP(MONTH(B2),{1,4,7,10},{4,1,2,3})

 

[bosco_yip]

In A2, copied down :

="Quarter "&INT(MOD((MONTH(B2)-7)/3+1,4))+1

Regards
Bosco

 

[PrashanthGM12345]

Thanks a lot. works perfect.

 

[PrashanthGM12345]

In A2, copied down :

="Quarter "&INT(MOD((MONTH(B2)-7)/3+1,4))+1

Regards
Bosco

Thanks a lot. Excellent Work

 

[PrashanthGM12345]

Try this instead:

="Quarter "&LOOKUP(MONTH(B2),{1,4,7,10},{4,1,2,3})[/quot

Perfect. Absolutely Brilliant. Thank you so much

 

[AliGW]

You're welcome! Sometimes a simple LOOKUP will avoid your having to do maths!!!