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

Question

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.

MLDev · Accepted Answer · 2014-04-30 07:41:31Z

2

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.

answered Apr 30, 2014 at 7:41

MLDev

1,2774 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chandler R Reeves Over a year ago

Thanks for the responce VBA Programmer. I do need the data in the pivot table, but this is a great way to get the average.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related