ExcelInsider Team
Dividing the sum by the count is one of the most used calculations in the area of mathematics. Suppose you have the sum of the money spent on buying something, and the number of items you purchased. You would need to divide the sum of the money by the count of items to get the amount spent on each item. In this article, we will learn the methods of pivot table calculated field sum divided by count.
➤ Add a helper column to the source table with 1 in every row.
➤ Refresh the pivot table and add that column
➤ Add a calculated field to the pivot table that divides the sum by the helper column. The formula should be like the following:
=’Satisfaction Score’ /Helper
➤ Replace ‘Satisfaction Score‘ and Helper with the sum and the helper columns.
That much instruction without full guidelines will be confusing for a lot of people. Don’t worry, we have included the step-by-step instructions in detail in this tutorial for you. Let’s jump right into it so that you can understand what we did and why we did so.
Dividing Sum by Count Using Calculated Field
Today, we have some customer feedback data on our sheet. There are the names of the customers, their locations, their satisfaction scores, and whether they will recommend the service to others or not. We want to find out the average satisfaction score by dividing the sum of satisfaction scores by the count of customers.
Unfortunately, a pivot table does not work like that. Even if we move the customer names in the Values area, the calculated field will refuse to work simply because it does not accept text as input.
Moreover, even if we managed to use an integer as input, pivot tables still work by aggregation. As a result, it will keep dividing using the sum instead of the count. But there is a way to make this work. Take a look at the pivot table at the current state, and then we will begin.
Step 1: Adding a Helper Column
To do the count, we will add a helper column to the source data and import it into the pivot table.
➤ Open the source table and add a column next to the table.
➤ On the first row after the heading, write 1.
➤ The column should be added to the table by now.
➤ Go to the Data tab and select the Flash Fill icon from the group called Data Tools.
➤ Rename Column1 to Helper, and the table should look like this:
➤ Go back to the pivot table, right-click, and select Refresh
➤ Select the Helper field from the PivotTable Fields panel on the right.
Step 2: Add the Calculated Field
Now that we have the helper column with us, we can add the calculated field to do the calculation.
➤ Click on a cell of the pivot table so that the PivotTable Analyze tab becomes visible.
➤ Select Calculated Field from the drop-down menu of Fields, Items, & Sets in the Calculations group
➤ Add this formula for the calculated field:
='Satisfaction Score' /Helper
➤ Change the name to Average Satisfaction Score.
➤ Click Add and OK to finish.
➤ Now, check the pivot table to see the result (We have collapsed the fields to enhance visibility here)
Frequently Asked Questions
How to divide in PivotTable calculated field?
You can use the regular division sign “/” to do so. Insert the fields in the formula that you want to divide, and put the slash in the middle to divide.
Why is my PivotTable counting instead of summing?
Probably your input data is considered as text data, as pivot tables only sum up the numeric data automatically. Recheck the source table, and change the Value Field Settings to use the Sum calculation instead of Count.
Can you reference a cell in a PivotTable calculated field?
Not directly. You can reference the field name (or the heading of the cells) as a reference. But absolute references do not work in a calculated field.
How to add count values to PivotTable?
Go to the Value Field Settings of the desired field and select Count from the new window. If you want the Distinct Count, you will need a pivot table that is added to the data model.
How to divide values in Excel formula?
Use the forward slash (/) to divide values in a formula. An example will be like the following: =A1/B1. This formula will divide A1 by the B1 cell.
Wrapping Up
In this article, we have learned how to use a calculated field to divide the sum by the count in Excel. We hope that you understand the process and will be able to apply the method to your own dataset. Leave a comment under this tutorial if you found the article helpful. Lastly, download the workbook we used and follow the tutorial if you have any issues understanding the method.
