Running AVERAGE in pivot table

[bill9376]

Is there any way to get a running average data field in a pivot table? (I don't understand why this isn't one of the additional calculation options.) I know how to fudge it outside the table with GETPIVOTDATA but that's a real kludge. I'm looking for something integrated with the table. I generating everything with VBA so using VBA to implement is not a problem.

Thanks, Bill

 

[Slape]

Can you add a column to your data to calculate running average and then alter your Pivot table to pull it in?

 

[bill9376]

You would need some aggregate function for the data field such a column. What would it be?

 

[Slape]

Can you use the Average Function? For example if you place the formula in column E (shown below) it would allow you to add columns as you needed and recalculate the average. If I am misunderstanding your request, repost because between the two of us, we could probably figure it out.

=AVERAGE($A$4:$D$4)

 

[bill9376]

OK.. assume we have a worksheet with this detail:

Mon Widgets
Jan08 2
Feb08 3
Mar08 4

I would like a pivot table with running total and running avg:

------ RT RA
Jan08 2 2 2
Feb08 3 5 2.5
Mar08 4 9 3

I know how to do RT but not RA with RA *in* the pivot table.

Thanks for your interest... bill

 

[Slape]

I think the only way to achieve what you are after is to add some columns to your spreadsheet. You can either add these columns to the right or left of your raw data or you can set up a separate summary sheet. You want the month in one, Widgets in the second column, Running Total in column C, Running Count in D, and Running Average in E.

Column A and B don't need formulas, but column C should be the previous month's total + Total Widgets during current month. Cell C3 would be: =(C2+B3)

Column D should have a 1 for the first month and then increase by one for each month going forward. The is a running count of months used when computing the Running Average. The first cell in the column can be entered as 1 manually, but the formula for cell D3 would be =(D2+1)

Column E should be a formula taking the Running Total and dividing by Running Count. =C2/D2


A B C D E

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Month</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=64>Wigets</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=64>RT</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=64>RC</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=64>RA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'Jan 08">Jan 08</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=B2">2</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=C2/D2">2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'Feb 08">Feb 08</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=C2+B3">5</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=D2+1">2</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=C3/D3">2.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="'Mar 08">Mar 08</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=C3+B4">9</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=D3+1">3</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" x:num x:fmla="=C4/D4">3</TD></TR></TBODY></TABLE>


After you complete this you can use these new columns for creating a new PIVOT Table or add them to an existing table, so the pivot table won't perform these calculations itself, but will present the information you have summarized.

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=345 border=0 x:str><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" span=3 width=59><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 71pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=94 height=17> </TD><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 44pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=59>Month</TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 44pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=59> </TD><TD class=xl25 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 44pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=59> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; WIDTH: 56pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=74> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Data</TD><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Feb 08</TD><TD class=xl30 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Jan 08</TD><TD class=xl30 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Mar 08</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">Grand Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Sum of Wigets</TD><TD class=xl24 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD class=xl30 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD class=xl30 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Sum of RA</TD><TD class=xl27 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>2.5</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>7.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Sum of RT</TD><TD class=xl31 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl32 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD class=xl32 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD><TD class=xl33 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>16</TD></TR></TBODY></TABLE>

I know this is alot of direction to follow through post directions, but if you want I can send you a small example.

 

[bill9376]

I came to a similar conclusion but since I am importing 10-30k rows and creating pivot tables programatically from these worksheets it just seemed too complicated. I ended up using pivots to do totals and running totals and then just wrote VBA code to build running averages outside the pivot. This has significant drawbacks since the r/avg data is not part of the pivot table. You can't rearrange columns easily, can't autoformat, etc. I can't believe there isn't an special running average calc like running total -all they would have to do for running average is divide running total by the position in the range! A running average is obviously useful for trending etc. Perhaps excel 2007 has this? Anyway thanks for brainstorming with me.... bill