Calculated Field Sum Divided by Count in Pivot Table

Pivot Table Calculated Field Sum divided by Count is one kind of average operation for Pivot Tables. Often, we calculate the average in Excel to understand data trends. In that case, a Pivot Table Calculated Field Sum divided by Count offers an efficient solution. In this article, we demonstrate how to calculate Average using Pivot Table Calculated Field Sum divided by a Count.

Suppose we have Sales in amounts of different cities in a dataset. And we want to calculate the average sale amounts of each city with or without imposed conditions. We can achieve this using Pivot Table Calculated Field Sum then divide the Sum by the Sum of Total Sale Count.

What is the Calculated Field in Pivot Table?

For any dataset or range of data, we can insert a Pivot Table by going simply to the Insert tab > selecting Pivot Table from the Tables section.

In the Pivot Table, all the entries are either in text or number values. If we place the cursor on any of the entries the PivotTable Analyze ribbon shows up. Follow the below sequences to insert a calculated field in the Pivot Table.

Issues with Calculating Field

Pivot Table Calculated Field feature inbuilt calculates the sum of other values. Nonetheless, the other values are the result of another execution, the Calculated Field considers them as summable.

For example, we have a Pivot Table where for each city there are multiple Order Dates. Now, we want to insert a Calculated Field that obeys the formula =’Order Date’ >3 as shown in the following image.

However, after applying the formula we get the same result as True (i.e., 1) for each city depicted in the below screenshot. Clearly, this is an issue. Test results should be 0 for Los Angeles and New York cities as they don’t satisfy the condition.

Pivot Table Calculated Field does that because the date field uses the COUNT function and dates are added whereas they should be counted.

To overcome this limitation of Calculated Field, we have to use a helper column as a count. Then if we use the count column instead of date, we’ll face no issue regarding the average calculation using the Calculated Field.

Procedures to Achieve Calculated Field Sum Divided by Count in Pivot Table

🔁 Inserting a Helper Column

We mentioned earlier that Calculated Field is a feature of a Pivot Table. Also, we discussed the issue regarding the Calculated Field. So, to achieve Pivot Table Calculated Field Sum divided by Count, we have to add a helper column to count each entry as unique.

⏩ Insert an extra column adjacent to the main dataset then enter 1 in each cell. Use the Fill Handle to insert 1 in cells instantly.

🔁 Inserting a Pivot Table

The next step to do the job is to insert a Pivot Table.

Step 1: Go to the Insert tab then Select Pivot Table (from Tables section).

Step 2: Executing Step 1 will automatically select the table range. If you want to modify it, Select a different range according to your needs.

Mark the New Worksheet option.

Click OK.

The above two steps make the Pivot Table appear as shown in the following image.

🔁 Adding Fields to the Pivot Table

As we inserted the Pivot Table in the earlier section, now we add different fields required in the calculation.

⏩ Tick the fields (i.e., City, Total, and Count) you want in the Pivot Table placing them in the Rows or Values section.

⏩ You can drag any fields around the Rows, Columns, or Values section as depicted in the following picture.

🔁 Inserting Calculated Field

Now, the most important element in the entire calculation is the Calculated Field. Previously, we just added Pivot Table fields which exist in the dataset. Calculated fields are those fields that we get after a calculation using a formula and existing Pivot Table fields.

Step 1: Click on any values in the Pivot Table. The PivotTable Analyze ribbon appears. Afterward, Go to PivotTable Analyze ribbon > Select Fields, Item & Sets (from Calculations section).

Step 2: The Insert Calculated Field window pops up.

In the Insert Calculated Field window, Give the field a suitable Name (i.e., Target Total price).

Start writing a formula (i.e., IF formula).

To enter any existing field, Select any field under the Fields section then Click on Insert Field.

Step 3: Type the following formula in the Insert Calculated field.

In the formula, we impose a condition using the IF function that we want Total Price amounts that are larger than 500.

Click OK.

⏩ After clicking OK, Target Total Price amounts appear as the Sum of Target Total Price calculated pivot table field depicted in the below screenshot.

🔁 Calculated Field Sum Divided by Count

As we did in the earlier section, we have to insert another calculated field to calculate the average, or we call Calculated Field Sum Divided by Count.

⏩ Give this calculated field a name (i.e., Average) then Enter the basic Average formula (i.e., Average=Target Total price/Count) as in the screenshot.

Click OK.

⏩ In a moment, you see the Average values as we divided the calculated field sum with the count. In the latter illustration, we show the Checked fields with their outcomes in a Pivot Table.

We can achieve the average or Total Sum divided by Count in multiple alternative ways in the Pivot Table. However, Calculated Field Sum divided by Count knowingly calculable adding a helper column making each entry as unique countable by the Pivot Table. Otherwise, the Issue with Calculated Field happens.

Read more: How to Get a Count in Excel Pivot Table Calculated Field

Alternative Way to Calculate Average in Pivot Table (without Calculated Field)

We can calculate the mere Average using multiple ways in Pivot Tables. But as in this article, we are focusing on the Calculated Field. For this reason, we are showing only one alternative to calculate the Average or Sum of Total Price modified as Average in the Pivot Table using the Value Field Setting. Let’s do that.

⏩ Insert a Pivot Table following Inserting a Pivot Table‘s sequences. The below picture shows a typical Pivot Table with fields.

⏩ Add an extra identical field of Sum of Total Price field in the Values section. Simply dragging the field to the Values section adds the field to the Values section.

⏩ Right-click on the extra added field in the Values section (i.e., Sum of Total Price).

Select Value Field Settings (from the Context Menu options).

⏩ Choose any type of calculation (i.e., Average) under Summarize value field by.

Click OK.

Within a moment, the Average of Sum Total Price value appears.

Read More: How to Calculate Weighted Average in Excel Pivot Table

⧭ Note

🔁 Keep in mind that Calculated Fields and typical Pivot Table Fields are not the same.  Calculated Fields are inserted fields that don’t exist in the Dataset.

Download Excel Workbook

Conclusion

We discuss Calculated Fields of Pivot Tables and calculate Calculated Field Sum divided by Count. Also, differentiate between typical Pivot Table fields and Calculated Fields. I hope this article clarifies confusion around calculated fields and their usage issue in calculations. Feel free to comment if you have further queries or something to add. See you in my other articles.

Related Articles

• Pivot Table Calculated Field for Average in Excel
• How to Apply Excel COUNTIF with Pivot Table Calculated Field
• How to Calculate Variance Using Pivot Table in Excel

<< Go Back to Calculated Field in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel