Friday, July 12, 2013

Google Spreadsheet Choose Function

Question:

( by Darren Thatcher )

Requirement: months Jan, Feb, Mar are in quarter Q3; Apr, May, Jun are in quarter Q4; etc

How do I do the equivalent of the following pseudo-code:

case (value of MONTH(cell to the left))
   1 OR 2 OR 3)   insert "Q3" into this cell
      ;;
   4 OR 5 OR 6)   insert "Q4" into this cell
      ;;
   7 OR 8 OR 9)   insert "Q1" into this cell
      ;;
   10 OR 11 OR 12)   insert "Q2" into this cell
      ;;
esac


Solution:

Have a look at the following screenshot:

I have the following formula in Cell B2:

=arrayformula(iferror(if(A2:A="";"";choose(month(A2:A);"Q3";"Q3";"Q3";"Q4";"Q4";"Q4";"Q1";"Q1";"Q1";"Q2";"Q2";"Q2"))))

the above formula will auto display the quarter for each row as I have used ArrayFormula.


If you want to have the formula for only single cell, then here is it:

=choose(month(A2);"Q3";"Q3";"Q3";"Q4";"Q4";"Q4";"Q1";"Q1";"Q1";"Q2";"Q2";"Q2")


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

No comments:

Post a Comment