Adding average column in pivot table to be used as avg. line in pivot chart

1
votes

I have a pivot table and chart that show data from an excel table. I would like to add a simple line to the chart that shows the avg. count for the column, but because it is a pivot table/chart I can't just add a line.

So my question is, Is there a way to add a "static entry in a pivot table and calculate the average outside of the table, or can I somehow come up with an AVG. number and add a third column with that avg?

Example Photo: enter image description here

I would like to have the average of complaints be a third column that just has 1.5 for each user so that it will show up on the chart as another series so that I can change that series to a line.

1
excelpivot-table

1 Answers

2
votes

Not sure how you would do it using a pivottable but here is another workaround. Assuming your data is in the format below:

enter image description here

Put this formula in cell F9 to get the user count:

=COUNTA(H:H)-3

F10:

="I" & (F9+4)

F11:

="J" & (F9+4)

F12:

=INDIRECT(F10)

F13:

=INDIRECT(F11)

F14:

=F12/F9

F15:

=F13/F9

You will get the average in in cells F14 and F15.