I have two columns in a pivot table. Count of Work orders, and Sum of the Cost.
I want to insert a calculated field that simply divides the sum of cost by count of work orders to get an average per work order.
When I put I insert a calculated field with the following formula, it yields the total cost, not the average. You'll see the fields are subtotal (cost) and WO#(work order)
And here is what the output looks like in my pivot table.
Best Answer
When you add a calculated field in a pivot table, you need to only add the reference, not a calculation inside of it, so you don't need to add Sum or Count in your definition. However, Excel works calculated fields in a very infuriating manner - first it adds your values and then performs the calculation - if, for example, I have a calculated field that's simply
field3=field2/field1, when I want to display the SUM of these values, instead ofsum(field3), it doessum(field2)/sum(field1)I would recommend doing this calculation outside of the pivot.
For example, see my results when I have the following table as input for a pivot