Convert Monthly Data to Quarterly Data

[shantanu97]

Need to convert "Data-Interest" from Monthly data to Quarterly Data. Attach File

Quarter is from July to June.

Quarter 1 - July,Aug,Sep
Quarter 2- Oct,Nov,Dec
Quarter 3-Jan, Feb, March
Quarter 4-April,May,June

Please help me, how to do this in excel?

 

[arthurbr]

Hi
have a look at

 

[CA_Punit]

=VLOOKUP(MONTH(A2),IF({1,0},{7;8;9;10;11;12;1;2;3;4;5;6},{1;1;1;2;2;2;3;3;3;4;4;4}),2,0)

With Ctrl+Shift+Enter

 

[Dossfm0q]

Hi May this Help

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	May-92			8.37	9.12	5.47	8.11	8.81	9.65
3	Jun-92		7.04	7.83	8.9	5.05	7.34	8.31	9.34
4	Jul-92		6.22	7.05	8.3	4.7	6.45	7.45	8.61
5	Aug-92		7.86	8.41	8.97	4.55	8.19	8.85	9.41
6	Sep-92		7.91	8.32	8.94	4.58	8.07	8.77	9.36
7	Oct-92		7.89	8.29	8.86	4.5	8.03	8.7	9.25
8	Nov-92		8.01	8.48	9.14	4.47	8.21	8.96	9.56
9	Dec-92		7.74	8.24	8.94	4.58	7.97	8.74	9.36
10	Jan-93		7.49	8	8.6	4.6	7.74	8.45	8.98
11	Feb-93		6.67	7.26	7.98	4.55	6.95	7.64	8.3
12	Mar-93		6.63	7.05	7.82	4.35	6.65	7.38	8.09
13	Apr-93		6.27	6.69	7.55	4.31	6.29	7.02	7.78
14	May-93		6.59	7.09	7.71	4.03	6.63	7.28	7.97
15	Jun-93		6.22	6.83	7.37	4.03	6.29	6.94	7.69
16	Jul-93		5.85	6.43	6.88	3.71	5.96	6.6	7.19
17	Aug-93		5.66	6.16	6.64	3.17	5.81	6.33	6.91
18	Sep-93		5.88	6.35	6.84	3.24	6.05	6.55	7.1
19	Oct-93		5.51	6.01	6.49	3.35	5.7	6.22	6.76
20	Nov-93		5.84	6.36	6.81	3.5	6.09	6.59	7.09
21	Dec-93		5.87	6.17	6.68	3.6	5.95	6.42	6.94
22	Jan-94		5.62	6.05	6.36	3.59	5.71	6.14	6.64
23
24
25			Because OF First Month of this Quarter Miss reads 0
26			0	0	0	0	0	0	0	0	8.37	9.12	5.47	8.11	8.81	9.65	7.04	7.83	8.9	5.05	7.34	8.31	9.34
27			6.22	7.05	8.3	4.7	6.45	7.45	8.61	7.86	8.41	8.97	4.55	8.19	8.85	9.41	7.91	8.32	8.94	4.58	8.07	8.77	9.36
28			7.89	8.29	8.86	4.5	8.03	8.7	9.25	8.01	8.48	9.14	4.47	8.21	8.96	9.56	7.74	8.24	8.94	4.58	7.97	8.74	9.36
29			7.49	8	8.6	4.6	7.74	8.45	8.98	6.67	7.26	7.98	4.55	6.95	7.64	8.3	6.63	7.05	7.82	4.35	6.65	7.38	8.09
30			6.27	6.69	7.55	4.31	6.29	7.02	7.78	6.59	7.09	7.71	4.03	6.63	7.28	7.97	6.22	6.83	7.37	4.03	6.29	6.94	7.69
31			5.85	6.43	6.88	3.71	5.96	6.6	7.19	5.66	6.16	6.64	3.17	5.81	6.33	6.91	5.88	6.35	6.84	3.24	6.05	6.55	7.1
32			5.51	6.01	6.49	3.35	5.7	6.22	6.76	5.84	6.36	6.81	3.5	6.09	6.59	7.09	5.87	6.17	6.68	3.6	5.95	6.42	6.94
33			5.62	6.05	6.36	3.59	5.71	6.14	6.64	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
C26:W33	C26	=INDIRECT(ADDRESS((((ROW($C2)-1)*3)-ROW($C$2))+(CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)/7),COLUMN($C2)+(7-CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)+COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7))-1))

 

[CA_Punit]

What are you trying to do?

 

[AhoyNC]

Here is another option to get the quarter, but as CA_Punit asked. What are you trying to do?

Book1
	A	B
1	Date	Quarter
2	1/1/1992	3
3	2/1/1992	3
4	3/1/1992	3
5	4/1/1992	4
6	5/1/1992	4
7	6/1/1992	4
8	7/1/1992	1
9	8/1/1992	1
10	9/1/1992	1
11	10/1/1992	2
12	11/1/1992	2
13	12/1/1992	2
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=CHOOSE(MONTH(A2),3,3,3,4,4,4,1,1,1,2,2,2)

 

[ebea]

Based on Dossfm0q formula, with a little correction of he's formula (column number increased).
If the layout are what OP want, I don't know. Maybe he returns, and reply to some of the proposals

quarter-setup.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Quarter	CG_2 yrs	CG_3 yrs	CG_5 yrs	CG_10 yrs	CG_Indexed	NSWTSB_3 yrs	NSWTSB_5 yrs	NSWTSB_10 yrs		CG_2 yrs	CG_3 yrs	CG_5 yrs	CG_10 yrs	CG_Indexed	NSWTSB_3 yrs	NSWTSB_5 yrs	NSWTSB_10 yrs		CG_2 yrs	CG_3 yrs	CG_5 yrs	CG_10 yrs	CG_Indexed	NSWTSB_3 yrs	NSWTSB_5 yrs	NSWTSB_10 yrs
2	jul-69	0,00	0,00	0,00	5.8	0,00	0,00	0,00	0,00		0,00	0,00	0,00	5.79	0,00	0,00	0,00	0,00		0,00	0,00	5.81	0,00	0,00	0,00	0,00	0,00
3	okt-69	0,00	0,00	0,00	5.83	0,00	0,00	0,00	0,00		0,00	0,00	0,00	5.85	0,00	0,00	0,00	0,00		0,00	0,00	5.95	0,00	0,00	0,00	0,00	0,00
4	jan-70	0,00	0,00	0,00	5.95	0,00	0,00	0,00	0,00		0,00	0,00	0,00	5.96	0,00	0,00	0,00	0,00		0,00	0,00	6.05	0,00	0,00	0,00	0,00	0,00
5	apr-70	0,00	0,00	0,00	6.94	0,00	0,00	0,00	0,00		0,00	0,00	0,00	6.85	0,00	0,00	0,00	0,00		0,00	0,00	6.86	0,00	0,00	0,00	0,00	0,00
6	jul-70	0,00	0,00	0,00	6.89	0,00	0,00	0,00	0,00		0,00	0,00	0,00	6.87	0,00	0,00	0,00	0,00		0,00	0,00	6.87	0,00	0,00	0,00	0,00	0,00
7	okt-70	0,00	0,00	0,00	6.88	0,00	0,00	0,00	0,00		0,00	0,00	0,00	6.83	0,00	0,00	0,00	0,00		0,00	0,00	6.81	0,00	0,00	0,00	0,00	0,00
8	jan-71	0,00	0,00	0,00	6.85	0,00	0,00	0,00	0,00		0,00	0,00	0,00	6.85	0,00	0,00	0,00	0,00		0,00	0,00	6.84	0,00	0,00	0,00	0,00	0,00
9	apr-71	0,00	0,00	0,00	6.85	0,00	0,00	0,00	0,00		0,00	0,00	0,00	6.82	0,00	0,00	0,00	0,00		0,00	0,00	6.83	0,00	0,00	0,00	0,00	0,00
10	jul-71	0,00	0,00	0,00	6.81	0,00	0,00	0,00	0,00		0,00	0,00	0,00	6.82	0,00	0,00	0,00	0,00		0,00	0,00	6.78	0,00	0,00	0,00	0,00	0,00
Ark1

Cell Formulas
Range	Formula
L2:S10	L2	=INDIRECT(ADDRESS((((ROW($B2)-1)*3)-ROW($B$2))+(CEILING.PRECISE(COLUMNS($B2:B2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)/8),COLUMN($B2)+(8-CEILING.PRECISE(COLUMNS($B2:B2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)+COLUMNS($B2:B2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8))-1))
AF2:AK10,U2:AB10	U2	=INDIRECT(ADDRESS((((ROW($B2)-1)*3)-ROW($B$2))+(CEILING.PRECISE(COLUMNS($B2:J2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)/8),COLUMN($B2)+(8-CEILING.PRECISE(COLUMNS($B2:J2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)+COLUMNS($B2:J2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8))-1))
AD2:AE10	AD2	=INDIRECT(ADDRESS((((ROW($B2)-1)*3)-ROW($B$2))+(CEILING.PRECISE(COLUMNS($B2:R2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)/8),COLUMN($B2)+(8-CEILING.PRECISE(COLUMNS($B2:R2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)+COLUMNS($B2:R2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8))-1))
K3:K10	K3	=EDATE(K2,3)

 

[Dossfm0q]

Quarters in normal sequence

Month to Quarter.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Month
2	May-92			8.37	9.12	5.47	8.11	8.81	9.65
3	Jun-92		7.04	7.83	8.9	5.05	7.34	8.31	9.34
4	Jul-92		6.22	7.05	8.3	4.7	6.45	7.45	8.61
5	Aug-92		7.86	8.41	8.97	4.55	8.19	8.85	9.41
6	Sep-92		7.91	8.32	8.94	4.58	8.07	8.77	9.36
7	Oct-92		7.89	8.29	8.86	4.5	8.03	8.7	9.25
8	Nov-92		8.01	8.48	9.14	4.47	8.21	8.96	9.56
9	Dec-92		7.74	8.24	8.94	4.58	7.97	8.74	9.36
10	Jan-93		7.49	8	8.6	4.6	7.74	8.45	8.98
11	Feb-93		6.67	7.26	7.98	4.55	6.95	7.64	8.3
12	Mar-93		6.63	7.05	7.82	4.35	6.65	7.38	8.09
13	Apr-93		6.27	6.69	7.55	4.31	6.29	7.02	7.78
14	May-93		6.59	7.09	7.71	4.03	6.63	7.28	7.97
15	Jun-93		6.22	6.83	7.37	4.03	6.29	6.94	7.69
16	Jul-93		5.85	6.43	6.88	3.71	5.96	6.6	7.19
17	Aug-93		5.66	6.16	6.64	3.17	5.81	6.33	6.91
18	Sep-93		5.88	6.35	6.84	3.24	6.05	6.55	7.1
19	Oct-93		5.51	6.01	6.49	3.35	5.7	6.22	6.76
20	Nov-93		5.84	6.36	6.81	3.5	6.09	6.59	7.09
21	Dec-93		5.87	6.17	6.68	3.6	5.95	6.42	6.94
22	Jan-94		5.62	6.05	6.36	3.59	5.71	6.14	6.64
23
24
25	Quarter		First Month of Quarter							Second Month of Quarter							Third Month of Quarter
26	Quarter 2		0	0	0	0	0	0	0	0	8.37	9.12	5.47	8.11	8.81	9.65	7.04	7.83	8.9	5.05	7.34	8.31	9.34
27	Quarter 3		6.22	7.05	8.3	4.7	6.45	7.45	8.61	7.86	8.41	8.97	4.55	8.19	8.85	9.41	7.91	8.32	8.94	4.58	8.07	8.77	9.36
28	Quarter 4		7.89	8.29	8.86	4.5	8.03	8.7	9.25	8.01	8.48	9.14	4.47	8.21	8.96	9.56	7.74	8.24	8.94	4.58	7.97	8.74	9.36
29	Quarter 1		7.49	8	8.6	4.6	7.74	8.45	8.98	6.67	7.26	7.98	4.55	6.95	7.64	8.3	6.63	7.05	7.82	4.35	6.65	7.38	8.09
30	Quarter 2		6.27	6.69	7.55	4.31	6.29	7.02	7.78	6.59	7.09	7.71	4.03	6.63	7.28	7.97	6.22	6.83	7.37	4.03	6.29	6.94	7.69
31	Quarter 3		5.85	6.43	6.88	3.71	5.96	6.6	7.19	5.66	6.16	6.64	3.17	5.81	6.33	6.91	5.88	6.35	6.84	3.24	6.05	6.55	7.1
32	Quarter 4		5.51	6.01	6.49	3.35	5.7	6.22	6.76	5.84	6.36	6.81	3.5	6.09	6.59	7.09	5.87	6.17	6.68	3.6	5.95	6.42	6.94
33	Quarter 1		5.62	6.05	6.36	3.59	5.71	6.14	6.64	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
C26:W33	C26	=INDIRECT(ADDRESS((((ROW($C2)-1)*3)-ROW($C$2))+(CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)/7),COLUMN($C2)+(7-CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)+COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7))-1))
A26:A33	A26	="Quarter "&CEILING(MONTH(INDIRECT(ADDRESS((((ROWS(A$2:A2)-1)*3)+ROW(A$2))-IF(AND(ROWS(A$2:A2)>1,MOD(MONTH(A$2)+2,3)+1>1),((MOD(MONTH(A$2)+2,3)+1)-1),0),COLUMN(A2)))),3)/3

 

[ebea]

Quarters in normal sequence

Month to Quarter.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Month
2	May-92			8.37	9.12	5.47	8.11	8.81	9.65
3	Jun-92		7.04	7.83	8.9	5.05	7.34	8.31	9.34
4	Jul-92		6.22	7.05	8.3	4.7	6.45	7.45	8.61
5	Aug-92		7.86	8.41	8.97	4.55	8.19	8.85	9.41
6	Sep-92		7.91	8.32	8.94	4.58	8.07	8.77	9.36
7	Oct-92		7.89	8.29	8.86	4.5	8.03	8.7	9.25
8	Nov-92		8.01	8.48	9.14	4.47	8.21	8.96	9.56
9	Dec-92		7.74	8.24	8.94	4.58	7.97	8.74	9.36
10	Jan-93		7.49	8	8.6	4.6	7.74	8.45	8.98
11	Feb-93		6.67	7.26	7.98	4.55	6.95	7.64	8.3
12	Mar-93		6.63	7.05	7.82	4.35	6.65	7.38	8.09
13	Apr-93		6.27	6.69	7.55	4.31	6.29	7.02	7.78
14	May-93		6.59	7.09	7.71	4.03	6.63	7.28	7.97
15	Jun-93		6.22	6.83	7.37	4.03	6.29	6.94	7.69
16	Jul-93		5.85	6.43	6.88	3.71	5.96	6.6	7.19
17	Aug-93		5.66	6.16	6.64	3.17	5.81	6.33	6.91
18	Sep-93		5.88	6.35	6.84	3.24	6.05	6.55	7.1
19	Oct-93		5.51	6.01	6.49	3.35	5.7	6.22	6.76
20	Nov-93		5.84	6.36	6.81	3.5	6.09	6.59	7.09
21	Dec-93		5.87	6.17	6.68	3.6	5.95	6.42	6.94
22	Jan-94		5.62	6.05	6.36	3.59	5.71	6.14	6.64
23
24
25	Quarter		First Month of Quarter							Second Month of Quarter							Third Month of Quarter
26	Quarter 2		0	0	0	0	0	0	0	0	8.37	9.12	5.47	8.11	8.81	9.65	7.04	7.83	8.9	5.05	7.34	8.31	9.34
27	Quarter 3		6.22	7.05	8.3	4.7	6.45	7.45	8.61	7.86	8.41	8.97	4.55	8.19	8.85	9.41	7.91	8.32	8.94	4.58	8.07	8.77	9.36
28	Quarter 4		7.89	8.29	8.86	4.5	8.03	8.7	9.25	8.01	8.48	9.14	4.47	8.21	8.96	9.56	7.74	8.24	8.94	4.58	7.97	8.74	9.36
29	Quarter 1		7.49	8	8.6	4.6	7.74	8.45	8.98	6.67	7.26	7.98	4.55	6.95	7.64	8.3	6.63	7.05	7.82	4.35	6.65	7.38	8.09
30	Quarter 2		6.27	6.69	7.55	4.31	6.29	7.02	7.78	6.59	7.09	7.71	4.03	6.63	7.28	7.97	6.22	6.83	7.37	4.03	6.29	6.94	7.69
31	Quarter 3		5.85	6.43	6.88	3.71	5.96	6.6	7.19	5.66	6.16	6.64	3.17	5.81	6.33	6.91	5.88	6.35	6.84	3.24	6.05	6.55	7.1
32	Quarter 4		5.51	6.01	6.49	3.35	5.7	6.22	6.76	5.84	6.36	6.81	3.5	6.09	6.59	7.09	5.87	6.17	6.68	3.6	5.95	6.42	6.94
33	Quarter 1		5.62	6.05	6.36	3.59	5.71	6.14	6.64	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
C26:W33	C26	=INDIRECT(ADDRESS((((ROW($C2)-1)*3)-ROW($C$2))+(CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)/7),COLUMN($C2)+(7-CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)+COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7))-1))
A26:A33	A26	="Quarter "&CEILING(MONTH(INDIRECT(ADDRESS((((ROWS(A$2:A2)-1)*3)+ROW(A$2))-IF(AND(ROWS(A$2:A2)>1,MOD(MONTH(A$2)+2,3)+1>1),((MOD(MONTH(A$2)+2,3)+1)-1),0),COLUMN(A2)))),3)/3

If I look in the raw data from the OP, i can see, Dossfm0q, that you still miss a column in each Quarter. That's why I did make a "corrected" version of your good way of setting it up. When you look closer to the years from 95 and forwards, all 8 columns are in use!