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

`=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**.

**⏩ **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 Field**s 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**