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.

Dataset-Pivot Table Calculated Field Sum Divided by Count


Download Excel Workbook


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.

Caculated field-Pivot Table Calculated Field Sum Divided by Count


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.

Issues with Calculated field

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.

Faulty Result

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.

Helper column-Pivot Table Calculated Field Sum Divided by Count


🔁 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).

Inserting Pivot table

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

Mark the New Worksheet option.

Click OK.

Inserting Pivot table

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

Pivot Table


🔁 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.

Adding fields in Pivot Table

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

Fields Checked

Read more: Pivot Table Field Name Is Not Valid


Similar Readings


🔁 Inserting Calculated Field

Now, the most important element in the entire calculation is the Calculated Field. Previously, we just added Pivot Table fields which are existing 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).

Calculated field-Pivot Table Calculated Field Sum Divided by Count

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.

Inserted Calculated Field window

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

=IF('Total Price'>500,'Total Price',"")

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

Click OK.

Formula Insertion

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 insertion

Read more: How to Use Calculated Field in Excel Pivot Table


🔁 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.

Calculated Field Sum Divided by Count

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.

Pivot Table Calculated Field Sum Divided by Count

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.

Field Setting-Pivot Table Calculated Field Sum Divided by Count

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.

Field insertion

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).

Value Field Setting-Pivot Table Calculated Field Sum Divided by Count

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

Click OK.

Average-Pivot Table Calculated Sum Divided by Count

 

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

Average of Sum Total Price


⧭ 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.


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.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo