2

How can I conduct the following calculation in pivot table? Sum the Total Hours and divide it by the sum of the Ticket Count for each Year-Month. Here's a snapshot of my spreadsheet.

enter image description here

The output would be something like:

enter image description here

If I use the Average function in the Pivot Table option, it would just average the total count of the data occurrence, not the sum of the Ticket Count.

Improve this question

1 Answer 1

Reset to default
9

In Excel, after creating a Pivot Table, on the PivotTable Analyze tab, go to Fields, Items, & Sets, then Insert Calculated Field, and add a new field with the following Formula:

`=SUM('Total Hours')/SUM('Ticket Count')`

Click OK and exit the Insert Calculated Field window. Open up the Field List, using the newly created field as Values as shown here:

enter image description here

You will need to do some extra cleaning up of the values to make it look exactly like your example output, but that would be outside the scope of this question.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .