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.

[TABLE="width: 555"]
<tbody>[TR]
[TD="width: 128"]Row Labels
[/TD]
[TD="width: 64"]A
[/TD]
[TD="width: 64"]B
[/TD]
[TD="width: 64"]C
[/TD]
[TD="width: 64"]D
[/TD]
[TD="width: 64"]E
[/TD]
[TD="width: 64"]F
[/TD]
[TD="width: 64"]G
[/TD]
[TD="width: 100"]Grand Total
[/TD]
[TD="width: 64, bgcolor: transparent"]average
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 100, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.7​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.1​

[/TD]
[TD="width: 100, bgcolor: transparent"]

2.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.0​

[/TD]
[TD="width: 100, bgcolor: transparent"]

3.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

4​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.4​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2.1​

[/TD]
[TD="width: 100, bgcolor: transparent"]

8.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]

6.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.4​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2.7​

[/TD]
[TD="width: 100, bgcolor: transparent"]

14.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2.3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

8.9​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

0.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

3.4​

[/TD]
[TD="width: 100, bgcolor: transparent"]

19.9​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

7​

[/TD]
[TD="width: 64, bgcolor: transparent"]

3.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]

10.9​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

1.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

3.9​

[/TD]
[TD="width: 64, bgcolor: transparent"]

4.0​

[/TD]
[TD="width: 100, bgcolor: transparent"]

26.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

8​

[/TD]
[TD="width: 64, bgcolor: transparent"]

3.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]

12.4​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

1.7​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

4.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

4.5​

[/TD]
[TD="width: 100, bgcolor: transparent"]

27.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

9​

[/TD]
[TD="width: 64, bgcolor: transparent"]

4.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]

12.7​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

5.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

4.9​

[/TD]
[TD="width: 100, bgcolor: transparent"]

27.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

10​

[/TD]
[TD="width: 64, bgcolor: transparent"]

5.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

13.0​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

6.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

4.8​

[/TD]
[TD="width: 100, bgcolor: transparent"]

29.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

11​

[/TD]
[TD="width: 64, bgcolor: transparent"]

6.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]

12.7​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

6.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]

5.6​

[/TD]
[TD="width: 100, bgcolor: transparent"]

31.0​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

12​

[/TD]
[TD="width: 64, bgcolor: transparent"]

7.4​

[/TD]
[TD="width: 64, bgcolor: transparent"]

14.2​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

7.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

6.5​

[/TD]
[TD="width: 100, bgcolor: transparent"]

35.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

13​

[/TD]
[TD="width: 64, bgcolor: transparent"]

7.9​

[/TD]
[TD="width: 64, bgcolor: transparent"]

14.8​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

7.7​

[/TD]
[TD="width: 64, bgcolor: transparent"]

8.4​

[/TD]
[TD="width: 100, bgcolor: transparent"]

38.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

14​

[/TD]
[TD="width: 64, bgcolor: transparent"]

9.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

15.4​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

8.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]

9.5​

[/TD]
[TD="width: 100, bgcolor: transparent"]

42.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

15​

[/TD]
[TD="width: 64, bgcolor: transparent"]

9.3​

[/TD]
[TD="width: 64, bgcolor: transparent"]

15.9​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

10.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

9.9​

[/TD]
[TD="width: 100, bgcolor: transparent"]

45.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

16​

[/TD]
[TD="width: 64, bgcolor: transparent"]

9.8​

[/TD]
[TD="width: 64, bgcolor: transparent"]

16.3​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

12.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]

10.0​

[/TD]
[TD="width: 100, bgcolor: transparent"]

48.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

17​

[/TD]
[TD="width: 64, bgcolor: transparent"]

9.9​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

13.4​

[/TD]
[TD="width: 64, bgcolor: transparent"]

10.1​

[/TD]
[TD="width: 100, bgcolor: transparent"]

33.5​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

18​

[/TD]
[TD="width: 64, bgcolor: transparent"]

10.1​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

14.1​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 100, bgcolor: transparent"]

24.2​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

19​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

14.6​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 100, bgcolor: transparent"]

14.6​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent"]

20​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

15.1​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 100, bgcolor: transparent"]

15.1​

[/TD]
[TD="width: 64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="width: 128"]Grand Total
[/TD]
[TD="width: 64"]

10.11​

[/TD]
[TD="width: 64"]

16.32​

[/TD]
[TD="width: 64"]

1.52​

[/TD]
[TD="width: 64"]

1.72​

[/TD]
[TD="width: 64"]

2.10​

[/TD]
[TD="width: 64"]

15.07​

[/TD]
[TD="width: 64"]

10.11​

[/TD]
[TD="width: 100"]

18.55​

[/TD]
[TD="width: 64, bgcolor: transparent"]???

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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...