Forum Discussion
Pivot Table Summarize values
I am working in efficiency reports for WC in the plant. We calculate efficiency by Standard Labor Hours divided by Actual Labor hours. So here is the report:
As you can see multiple employees can work at the same time in the same Work Oder. But the software gives me the Standard Labor hours in each of the employees, and Std Labor Hrs should not add up. the value that I want to add up is the Actual Labor hours.
So when I create the pivot table:
The std labor hours are being added.
I need the red values not added, and the green fields added.
Is it possible?
In your scenario, it seems you want to create a Pivot Table in Excel to summarize Actual Labor Hours without adding up the Standard Labor Hours for multiple employees working on the same Work Order. You can achieve this by customizing the aggregation function for the Standard Labor Hours field in your Pivot Table.
Here is how you can do it:
- Create the Pivot Table:
- Start by creating your Pivot Table with the relevant fields, including Standard Labor Hours and Actual Labor Hours.
- Add the Standard Labor Hours Field:
- Add the "Standard Labor Hours" field to the "Values" area of your Pivot Table.
- Change the Aggregation Function:
- By default, Excel will sum the values in the "Values" area. To change this to a different aggregation function (in your case, you want to avoid adding up Standard Labor Hours), do the following:
- Click on the drop-down arrow next to "Sum of Standard Labor Hours" in the "Values" area of the Pivot Table.
- Select "Value Field Settings."
- Modify the Value Field Settings:
- In the "Value Field Settings" dialog box that appears, under "Summarize value field by," choose "Max" (or any other suitable aggregation function based on your requirements). This will use the maximum value for each Work Order's Standard Labor Hours instead of summing them.
- Add the Actual Labor Hours Field:
- Now, add the "Actual Labor Hours" field to the "Values" area of your Pivot Table.
- Adjust Aggregation for Actual Labor Hours:
- If you want to sum the Actual Labor Hours, you do not need to make any changes since Excel typically sums numeric fields by default.
Now, your Pivot Table should display the maximum Standard Labor Hours for each Work Order (as you mentioned, these should not be added up) and the summed Actual Labor Hours for each Work Order.
This approach allows you to customize how Excel aggregates data for different fields within the same Pivot Table, so you can apply different aggregation functions to suit your reporting needs. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
1 Reply
In your scenario, it seems you want to create a Pivot Table in Excel to summarize Actual Labor Hours without adding up the Standard Labor Hours for multiple employees working on the same Work Order. You can achieve this by customizing the aggregation function for the Standard Labor Hours field in your Pivot Table.
Here is how you can do it:
- Create the Pivot Table:
- Start by creating your Pivot Table with the relevant fields, including Standard Labor Hours and Actual Labor Hours.
- Add the Standard Labor Hours Field:
- Add the "Standard Labor Hours" field to the "Values" area of your Pivot Table.
- Change the Aggregation Function:
- By default, Excel will sum the values in the "Values" area. To change this to a different aggregation function (in your case, you want to avoid adding up Standard Labor Hours), do the following:
- Click on the drop-down arrow next to "Sum of Standard Labor Hours" in the "Values" area of the Pivot Table.
- Select "Value Field Settings."
- Modify the Value Field Settings:
- In the "Value Field Settings" dialog box that appears, under "Summarize value field by," choose "Max" (or any other suitable aggregation function based on your requirements). This will use the maximum value for each Work Order's Standard Labor Hours instead of summing them.
- Add the Actual Labor Hours Field:
- Now, add the "Actual Labor Hours" field to the "Values" area of your Pivot Table.
- Adjust Aggregation for Actual Labor Hours:
- If you want to sum the Actual Labor Hours, you do not need to make any changes since Excel typically sums numeric fields by default.
Now, your Pivot Table should display the maximum Standard Labor Hours for each Work Order (as you mentioned, these should not be added up) and the summed Actual Labor Hours for each Work Order.
This approach allows you to customize how Excel aggregates data for different fields within the same Pivot Table, so you can apply different aggregation functions to suit your reporting needs. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.