Hello
What is the contents of "Quarters"?
This is a discussion on Using Nested Month Function within the Excel Questions forums, part of the Question Forums category; My instructions tell me to use a "nested month function" to convert the date to a numerical month, using a ...
My instructions tell me to use a "nested month function" to convert the date to a numerical month, using a VLOOKUP syntax. I have already used the formula =VLOOKUP(MONTH(A2),Quarters,2) in the cell. I'm not familiar with these functions, so any help is greatly appreciated. Thank you.
Hello
What is the contents of "Quarters"?
What is the result of the VLOOKUP ?
Is this what was asked in the instructions ?
If you want to convert a date into a quarter, you can use:
="Quarter "&ROUNDUP(MONTH(A1)/3,0)
Neil
Quarters (as a named range) would need to be a 2 column range like this (for purposes of demonstrating how VLOOKUP works):
Sheet1
A B C D E 1 Date Quarter _ Month Quarter 2 7/18/2012 Quarter3 _ 1 Quarter1 3 _ _ _ 4 Quarter2 4 _ _ _ 7 Quarter3 5 _ _ _ 10 Quarter4
Quarters refers to: =$D$2:$E$5
Your formula entered in B2:
=VLOOKUP(MONTH(A2),Quarters,2)
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
Yes this is how the table looks:
Breakpoints Quarter 1 Quarter 1 4 Quarter 2 7 Quarter 3 10 Quarter 4
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
My result is correct to return the Quarter, but they are now asking to use a "nested Month function" to return the month for each quarter. This is where I am stuck.
Well, the lookup formula does contain a nested MONTH function as does the suggestion by Neil in post #5.
So, "nested Month function" is really ambiguous and open to interpretation.
Is this a school exercise? If so, then you may want to get more guidance on what you are expected to do with this.
There are many ways to get the calendar quarter from a date. Here are a couple:
A1 = some date
=CEILING(MONTH(A1)/3,1)
=INT((MONTH(A1)+2)/3)
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
Like this thread? Share it with others