Add a average column to a pivot table working off max

[eddster]

Hi All,

I am currently running a PIVOT TABLE on a data set, the data output I require is the highest value for each group (A – G) against each period (1 – 20) I am therefore using “Summarize values by Max” in the value field settings.

However, I then need to have a final column in my pivot table that determines the average value for the periods; and I wish this to be dynamic in-so-far as when I use the PIVOT TABLE filter to only choose certain groups i.e.(A,C,E).

Currently I am doing this analysis by pulling out the data into a new sheet and then adding the necessary Average column but this take up unnecessary time.

Row Labels	A	B	C	D	E	F	G	Grand Total	average
1​	0.0​	0.0​	0.0​	0.0​	0.0​	0.0​	0.0​	0.0​	???
2​	0.0​	1.7​	0.0​	0.0​	0.0​	0.8​	0.1​	2.6​	???
3​	0.6​	1.1​	0.2​	0.0​	0.3​	0.5​	1.0​	3.8​	???
4​	1.3​	2.5​	0.3​	0.3​	0.4​	1.2​	2.1​	8.1​	???
5​	1.8​	6.6​	0.5​	0.4​	0.6​	1.6​	2.7​	14.2​	???
6​	2.3​	8.9​	1.0​	0.6​	1.2​	2.6​	3.4​	19.9​	???
7​	3.0​	10.9​	1.5​	1.1​	2.1​	3.9​	4.0​	26.5​	???
8​	3.8​	12.4​		1.7​		4.5​	4.5​	27.0​	???
9​	4.8​	12.7​				5.2​	4.9​	27.6​	???
10​	5.5​	13.0​				6.2​	4.8​	29.6​	???
11​	6.2​	12.7​				6.5​	5.6​	31.0​	???
12​	7.4​	14.2​				7.1​	6.5​	35.2​	???
13​	7.9​	14.8​				7.7​	8.4​	38.8​	???
14​	9.1​	15.4​				8.6​	9.5​	42.6​	???
15​	9.3​	15.9​				10.1​	9.9​	45.2​	???
16​	9.8​	16.3​				12.1​	10.0​	48.2​	???
17​	9.9​					13.4​	10.1​	33.5​	???
18​	10.1​					14.1​		24.2​	???
19​						14.6​		14.6​	???
20​						15.1​		15.1​	???
Grand Total	10.11​	16.32​	1.52​	1.72​	2.10​	15.07​	10.11​	18.55​	???

<tbody>
</tbody>

 

[eddster]

Anyone?

 

[eddster]

i have found a solution that seems to work

Sub test()
Dim pvt As PivotTable
Dim pvf As PivotField

Set pvt = ActiveSheet.PivotTables(1)
On Error Resume Next
For Each pvf In pvt.VisibleFields
pvf.Function = xlAverage
Next pvf
On Error GoTo 0
End Sub

 

[eddster]

scrap that does NOT work just changes whole PIVOT to AVG not MAX

 

[RasGhul]

You could add a Calculated Field and enter =Average(A,B,C,D,E,F,G), but the averages include the zeros so not the true average for each row and not dynamic.

A workaround would be to use VBA to add a column to calculate the average as worksheet change function on visible cells on your pivot.

One of the VBA guru's would have to look at that for you...