How to Calculate Average Price in Excel (7 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

We need to calculate the average price in various business sectors or 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.


How to Calculate Average Price in Excel: 7 Methods

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.

how to calculate average price in excel

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.

how to calculate average price in excel using AVERAGE function

After pressing ENTER, we find the output as 23.25.

how to calculate average price in excel using AVERAGE function

Read More: How to Calculate Average Share Price in Excel


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)

how to calculate average price in excel using AVERAGEA function

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

how to calculate average price in excel using AVERAGE function

 


3. Utilizing AVERAGEIF Function to Calculate Conditional Average Price

The AVERAGEIF function searches the provided range for cells that fulfill particular criteria, and 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.

how to calculate average price in excel using AVERAGEIF function

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

how to calculate average price in excel using AVERAGEIF function


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

how to calculate average price in excel using SUM and COUNTA function

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

 SUM and COUNTA function


5. Using SUBTOTAL Function

The SUBTOTAL function is extremely effective 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.

how to calculate average price in excel using SUBTOTAL function

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

 SUBTOTAL function


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.

 AVERAGE and LARGE function

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

 AVERAGE and LARGE function


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.

 AVERAGE and SMALL function

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

how to calculate average price in excel using AVERAGE and SMALL function


7. Using SUMPRODUCT Function to Calculate Weighted Average

In a dataset, calculating weighted average is the average of numbers with variable degrees of relevance. All of the weighting variables or percentages in the 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.

how to calculate average price in excel using SUMPRODUCT function

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

SUMPRODUCT function


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.

Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo