macro for pivot calculated item

[vexcelmacro]

would like to have calculate Average for the below table through macro.

Region Subregion QTR Revenue 10Q4 11Q1 11Q2
AAA QAZ 10Q1 1,000
AAA WSX 10Q1 2,000
EEE EDC 10Q1 3,000
EEE EDC 10Q2 4,000
HHH WSX 10Q3 5,000
SSS TGB 10Q4 6,000
QQQ YHN 10Q4 7,000
YYY UJM 11Q1 8,000
TTT Nordics 11Q1 9,000
UUU QW 11Q2 10,000
III ER 11Q2 11,000


The QTR column data will get updated on every new quarters for example now we have till 11Q2 after june the QTR column will get updated with new QTR as 11Q3 (which will continue for three months)

I need a macro to add four quarter and divide by 4 and the heading should be the QTR name for example :

when the table is updated with new quarter the calcuation should change its running total as, now we are in 11Q2 so the calculation should be like this

11Q2(heading of the row)=11Q2+11Q1+10Q4+10Q3)/4 when the table is updated with 11Q3 the calculation should change as shown below

11Q3(heading of the row)=11Q3+11Q2++11Q1+10Q4)/4
11Q2(heading of the row)=11Q2+11Q1+10Q4+10Q3)/4

I have a data till 2007(07Q1,07Q2,07Q3,07Q4) every year will have four quarters, and i need my macro to calculate the running total based on the new quarters as the example shown above.

If you have any better suggestion kindly share...



I used the pivot logic (formula calculated item) over here and below is my macro, but unfortunately the avg variable is not working in my macro.

Sub test()
a = Sheets("Pivot").Range("A1").Value
Sheets("List").Select
Cells.Find(what:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'lastrow = Range("A65000").End(xlUp).Select

a = ActiveCell
b = ActiveCell.Offset(-1)
C = ActiveCell.Offset(-2)
d = ActiveCell.Offset(-3)
e = 4

AVG = "(" & a & "+" & b & "+" & C & "+" & d & ")" & "/" & e
'AVG = """avg11q1""" & "," & "=" & "(" & a & "+" & b & "+" & c & "+" & d & ")"
MsgBox (AVG)


Sheets("Pivot").Select
Range("c4").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("QTR").CalculatedItems.Add Name:="AVGQTR11", Formula:="=" & AVG


End Sub

 

[GlennUK]

Why don't you use real dates instead of text items for the quarters, and then group the date by quarter and year, and set the field setting as average, like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Region	Subregion	Date	Revenue		Average of Revenue
2	AAA	QAZ	01/01/2010	1,000		Years	Date	Total
3	AAA	WSX	01/01/2010	2,000		2010	Qtr1	2000
4	EEE	EDC	01/01/2010	3,000			Qtr2	4000
5	EEE	EDC	01/04/2010	4,000			Qtr3	5000
6	HHH	WSX	01/07/2010	5,000			Qtr4	6500
7	SSS	TGB	01/10/2010	6,000		2011	Qtr1	8500
8	QQQ	YHN	01/10/2010	7,000			Qtr2	10500
9	YYY	UJM	01/01/2011	8,000		Grand Total		6000
10	TTT	Nordics	01/01/2011	9,000
11	UUU	QW	01/04/2011	10,000
12	III	ER	01/04/2011	11,000
Sheet3

?

 

[vexcelmacro]

Thanks for the suggestion, but my real data is in the below format and my manager would like to know the average of each quarter based on region and subregion, this works fine in pivot calculateditems but since its a dashboard every quarter manually intervention is good. so i would like to automate this.


source data
Region Subregion QTR Revenue
AAA QAZ 10Q1 1,000
AAA WSX 10Q1 2,000
EEE EDC 10Q1 3,000
EEE EDC 10Q2 4,000
HHH WSX 10Q3 5,000
III ER 11Q2 11,000
QQQ YHN 10Q4 7,000
SSS TGB 10Q4 6,000
TTT Nordics 11Q1 9,000
UUU QW 11Q2 10,000
YYY UJM 11Q1 8,000

Result next to the region and subregion qtr column

11Q3(heading of the row)=11Q3+11Q2++11Q1+10Q4)/4 (next quarter will come)
11Q2(heading of the row)=11Q2+11Q1+10Q4+10Q3)/4
11Q1(heading of the row)=11Q1+10Q4+10Q3+10Q2)/4
10Q4(heading of the row)=10Q4+10Q3+10Q2+10Q1)/4

i have till 2007 data

 

[GlennUK]

Look at this example of formulas to do high level summing for rolling 4 quarter SUM ( not average, but the same logic applies ... this is so that you can see what can be done ... you'd simply divide by another SUMPRODUCT that does a count of the same matches ). Addition logic can be applied to the SUMPRODUCT to limit calcs to a particular region and subregion:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Region	Subregion	Date	Revenue	10Q1	10Q2	10Q3	10Q4	11Q1	11Q2
2	AAA	QAZ	10Q1	1,000	24933	26109	26110	28000	39000	56000
3	AAA	WSX	10Q1	2,000
4	EEE	EDC	10Q1	3,000		08Q3
5	EEE	EDC	10Q2	4,000		08Q4
6	HHH	WSX	10Q3	5,000		09Q1
7	SSS	TGB	10Q4	6,000		09Q2
8	QQQ	YHN	10Q4	7,000		09Q3
9	YYY	UJM	11Q1	8,000		09Q4
10	TTT	Nordics	11Q1	9,000		10Q1
11	UUU	QW	11Q2	10,000		10Q2
12	III	ER	11Q2	11,000		10Q3
13	BBB	QWS	09Q2	1,500		10Q4
14	DSD	RRF	09Q2	1,324		11Q1
15	TRE	QAS	09Q3	1,555		11Q2
16	GTH	KJL	09Q3	3,444		11Q3
17	HHJ	LKL	09Q4	4,555		11Q4
18	JUJ	OLO	09Q4	6,555		12Q1
Qtr calc

 

[vexcelmacro]

Hi
Thanks for the formula, but will this work based on each region and subregion, same what the pivot tool does ?

I need average of quarter based on each region and subregion.
for example:
i use excel 2007, if you create a pivot from the data provided by me, and just pull the region and subregion column to 'Row Labels' of pivot tool and QTR to 'Column Labels' and revenue to 'Values', after that go to pivot formulas calculated item and create new formula manually for the current quarter as shown below
11Q2=(11Q2+11Q1+10Q4+10Q3)/4 the result what ever you get here is my request.
Previous quarter is fine i can do it manually for first time but is it possible to create macro for the new quarter example
11Q3=11Q3+11Q2++11Q1+10Q4)/4
i have a code with me but i was unable to pass variable on the calculated item , i listed all the quarter to a list sheet.
Sub test()
a = Sheets("Pivot").Range("A1").Value
Sheets("List").Select
Cells.Find(what:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'lastrow = Range("A65000").End(xlUp).Select

a = ActiveCell
b = ActiveCell.Offset(-1)
C = ActiveCell.Offset(-2)
d = ActiveCell.Offset(-3)
e = 4

AVG = "(" & a & "+" & b & "+" & C & "+" & d & ")" & "/" & e
'AVG = """avg11q1""" & "," & "=" & "(" & a & "+" & b & "+" & c & "+" & d & ")"
MsgBox (AVG)


Sheets("Pivot").Select
Range("c4").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("QTR").CalculatedItems.Add Name:="AVGQTR11", Formula:="=" & AVG


End Sub

 

[GlennUK]

Yes, it will work based on a region and subregion that you give it, like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Region	Subregion	Date	Revenue		Region	Subregion	10Q1	10Q2	10Q3	10Q4	11Q1	11Q2
2	AAA	Nordics	10Q1	1,000		AAA	Nordics	15323	13999	15555	16000	21000	32000
3	BBB	Iberics	10Q1	2,000
4	AAA	Nordics	10Q1	3,000					08Q3
5	BBB	Iberics	10Q2	4,000					08Q4
6	AAA	Nordics	10Q3	5,000					09Q1
7	BBB	Iberics	10Q4	6,000					09Q2
8	AAA	Nordics	10Q4	7,000					09Q3
9	BBB	Iberics	11Q1	8,000					09Q4
10	AAA	Nordics	11Q1	9,000					10Q1
11	BBB	Iberics	11Q2	10,000					10Q2
12	AAA	Nordics	11Q2	11,000					10Q3
13	BBB	Iberics	09Q2	1,500					10Q4
14	AAA	Nordics	09Q2	1,324					11Q1
15	BBB	Iberics	09Q3	1,555					11Q2
16	AAA	Nordics	09Q3	3,444					11Q3
17	BBB	Iberics	09Q4	4,555					11Q4
18	AAA	Nordics	09Q4	6,555					12Q1
Qtr calc