We need to calculate the average price in various business sectors or in normal life frequently. Excel has made it easy to calculate the average price. There are various formulas in Excel by which we can calculate the average very easily. By learning how to calculate the average price, we can use those formulas in our practical work. In this article, weâ€™ll try to discuss how to calculate the average price in Excel.

## Download Practice Workbook

## 7 Methods to Calculate Average Price in Excel

There are several methods in Excel for the calculation of the average. To do this, we have made a dataset of** Prices of Different Fruits Per Ton** from 3 different** Shops**. The dataset is like this.

Weâ€™ll now try the following seven different methods to calculate the average price.

**1. Applying AVERAGE Function to Calculate Average Price in Excel**

The **AVERAGE function** is the simplest function for calculating the average. We can use it when we need to find the direct average without any criteria. We can write the formula in cell **D17** like this.

`=AVERAGE(D5:D16)`

Here, **D5:D16 **is the price of fruits from cells** D5** to **D16**.

After pressing** ENTER,** we find the output as **23.25**.

**Read More: How to Calculate Average Selling Price in Excel (3 Easy Methods)**

**2. Using AVERAGEA Function to Calculate Average PriceÂ **

We can use the **AVERAGEA function** when we have text in our selected argument cells. The **AVERAGEA** function evaluates text as **zero**, logical value **TRUE** as **1,** and logical value **FALSE** as** zero**. We have changed the** Price of Banana** to **Not Available** in **Shop B** to show how **AVERAGEA** function works. We can write the formula in the **D17** cell like this.

`=AVERAGEA(D5:D16)`

**Â **Similarly, by pressing **ENTER**, we find the calculated **Average Price** as **21.00**.

**Similar Readings**

**How to Calculate Price Increase Percentage in Excel (3 Easy Ways)****Calculate Selling Price from Cost and Margin in Excel****How to Calculate Price Per Pound in Excel (3 Easy Ways)****Calculate Unit Price in Excel (3 Quick Methods)****How to Calculate Cost per Unit in Excel (With Easy Steps)**

**3. Utilizing AVERAGEIF Function to Calculate Conditional Average Price**

The **AVERAGEIF** function searches the provided range for cells that fulfill particular criteria, then calculates the arithmetic mean of those cells. If we want to calculate the **Average Price **of** Apples** only, we can write the formula in cell** D17** like this.

`=AVERAGEIF(C5:C16,â€ťAppleâ€ť,D5:D16)`

Here, **C5:C16 **refers to the** Name of Products** from cells** C5** to **C16**. **Apple** is the criteria here, and this function will calculate the average of **Apples **only.

Lastly, by pressing **ENTER**, we find the following output as **20.75**.

**4. Applying SUM and COUNTA Functions**

Another simple method to calculate the average is the use of the **SUM** and **COUNTA** functions. We can apply this formula to cell **D17** like this.

`=SUM(D5:D16)/COUNTA(D5:D16)`

Here, the **SUM **function first calculates the summation from cells **D5** to **D16, **which we have put in the formula. The **COUNTA** function actually counts the argument number i.e. calculates how many arguments are there from cell **D5** to** D16**. Then the output from the **SUM **function is divided by the output of the **COUNTA** function to calculate the average.

As a result. after pressing** ENTER**, we find the calculated **Average Price** is **23.25**.

**5. Using SUBTOTAL Function**

The **S****UBTOTAL function** is an extremely effective function in Excel. We can use this function for various purposes. To calculate the average, we can write this formula like this.

`=SUBTOTAL(1,D5:D16)`

After putting** SUBTOTAL** in the formula, we will see different function names in the string. Among them, **AVERAGE** is the first function. Here, **1** refers to mainly this** AVERAGE** function.

Subsequently, by clicking **ENTER,** we find the output as **23.25**.

**6. Applying AVERAGE and LARGE OR SMALL Functions**

We can use either the **LARGE function** or the **SMALL function** with the **AVERAGE **function to calculate certain largest or smallest valuesâ€™ average.

**6.1 Finding Average of 3 Largest Prices**

When we want to calculate the average of certain limited large valuesâ€™ we need to apply the combination of **AVERAGE **and **LARGE** functions. To find the average of the **three **largest prices, we need to write the formula in cell** D17 **like this.

`=AVERAGE(LARGE(D5:D16,{1,2,3}))`

Here, the formula** LARGE(D5:D16,{1,2,3})** will first find the largest** 3** values from cell **D5** to **D16**. Then the **AVERAGE** function calculates the average of the outputs of the first formula.

As a result, after clicking **ENTER**, we find the **Average of the 3 Largest Price** as **29**.

**6.2 Finding Average of 3 Smallest Prices**

The **SMALL** function works similarly to the **LARGE** function. We need to write the formula in the cell **D18** like this.

`=AVERAGE(SMALL(D5:D16,{1,2,3}))`

Here, the formula** SMALL(D5:D16,{1,2,3})** will first find the smallest** 3** values from cell **D5** to **D16**. Then the **AVERAGE** function calculates the average of the outputs of the formula like the previous one.

As a result, by clicking **ENTER**, we get the output as **16.33 **like this.

**7. Using SUMPRODUCT Function to Calculate Weighted Average**

In a dataset, a **weighted average** is the average of numbers with variable degrees of relevance. All of the weighting variables or percentages in this below example add up to **100%**. As a result, when computing the average marks, we donâ€™t need to divide the sum of all weightage elements. All we have to do now is multiply all of the marks by their respective weightage factors and add all of the products together.

The sum of the products of related ranges or arrays is returned by the **SUMPRODUCT function**. In order to use this function, we must provide the range of cells in the first argument that includes all marks. All of the weighting elements will be accommodated in the second argument. So, to determine the weighted average marks, weâ€™ll need to use the **SUMPRODUCT **function. We can write the formula in the** C12 **cell like this.

`=SUMPRODUCT(C5:C11,D5:D11)`

Here, **D5:D11** refers to the **Weighting Factors** of different fruits for prices in the market from cells **D5 **to **D11**.

Eventually, after pressing **ENTER**, we find the *Weighted Average Price* as **28.30**.

## Things to Remember

- We can use the
**AVERAGE**function, the combination of**SUM**and**COUNTA**functions, and**SUBTOTAL**functions to calculate the average price directly without any conditions. - If we need to add some conditions, we must apply the
**AVERAGEIF**function or a combination of**LARGE**or**SMALL**functions with the**AVERAGE** - Again, we can apply the
**AVERAGEA**function if there is a mixture of values and texts in the selected cells.

## Conclusion

In this article, we have discussed almost all of the formulas that are related to calculating the average price. We hope, this article will be very beneficial for you.

## Related Articles

**How to Calculate Production Cost in Excel (3 Effective Ways)****Calculate Price Per Square Meter in Excel (3 Handy Methods)****How to Calculate Selling Price Per Unit in Excel (3 Easy Ways)****Calculate Selling Price in Excel (4 Easy Methods)****How to Calculate Variable Cost Per Unit in Excel (with Quick Steps)****Calculate Bond Price in Excel (4 Simple Ways)****How to Calculate Discount Price in Excel (4 Quick Methods)**