CHOOSE function
IntermediateWhen monthly data needs quarter labels, the CHOOSE function is a simple way to select a value from a list based on an index number.
The CHOOSE function works like this:
=CHOOSE(index_num, value1, value2, value3, ...)
The first argument is the index number (1-4), and the following arguments are the values to choose from. If index_num is 1, it returns value1; if 2, it returns value2; and so on.
For example: =CHOOSE(2, "Red", "Green", "Blue") returns "Green" because index 2 corresponds to the second value.
Your task
You have monthly sales data with month numbers in column B (1 for January through 12 for December). In column D, you need to display which quarter each month belongs to:
- Months 1-3: Q1
- Months 4-6: Q2
- Months 7-9: Q3
- Months 10-12: Q4
Use CHOOSE to return "Q1", "Q2", "Q3", or "Q4" based on the month number. You'll need to convert the month number (1-12) into a quarter index (1-4) as part of your formula.
Hint: Think about how you can mathematically transform a month number into a quarter number. For example, months 1, 2, 3 should all become 1; months 4, 5, 6 should become 2; and so on.
Fill the formula down for all 12 months.
Need some help?
Hint 1
CHOOSE needs an index from 1 to 4 to pick the right quarter. You need to calculate this from the month number.
Hint 2
To convert month to quarter: divide by 3 and round up. Months 1-3 divided by 3 give values up to 1, months 4-6 give values up to 2, etc.
Hint 3
Use CEILING(B2/3,1) to convert month to quarter index. This gives 1 for months 1-3, 2 for months 4-6, and so on.
CHOOSE function
IntermediateWhen monthly data needs quarter labels, the CHOOSE function is a simple way to select a value from a list based on an index number.
The CHOOSE function works like this:
=CHOOSE(index_num, value1, value2, value3, ...)
The first argument is the index number (1-4), and the following arguments are the values to choose from. If index_num is 1, it returns value1; if 2, it returns value2; and so on.
For example: =CHOOSE(2, "Red", "Green", "Blue") returns "Green" because index 2 corresponds to the second value.
Your task
You have monthly sales data with month numbers in column B (1 for January through 12 for December). In column D, you need to display which quarter each month belongs to:
- Months 1-3: Q1
- Months 4-6: Q2
- Months 7-9: Q3
- Months 10-12: Q4
Use CHOOSE to return "Q1", "Q2", "Q3", or "Q4" based on the month number. You'll need to convert the month number (1-12) into a quarter index (1-4) as part of your formula.
Hint: Think about how you can mathematically transform a month number into a quarter number. For example, months 1, 2, 3 should all become 1; months 4, 5, 6 should become 2; and so on.
Fill the formula down for all 12 months.
Need some help?
Hint 1
CHOOSE needs an index from 1 to 4 to pick the right quarter. You need to calculate this from the month number.
Hint 2
To convert month to quarter: divide by 3 and round up. Months 1-3 divided by 3 give values up to 1, months 4-6 give values up to 2, etc.
Hint 3
Use CEILING(B2/3,1) to convert month to quarter index. This gives 1 for months 1-3, 2 for months 4-6, and so on.