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.
The output would be something like:
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.
Best Answer
In Excel, after creating a Pivot Table, on the
PivotTable Analyze
tab, go toFields, Items, & Sets
, thenInsert Calculated Field
, and add a new field with the followingFormula
:Click OK and exit the
Insert Calculated Field
window. Open up theField List
, using the newly created field asValues
as shown 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.