Shajratul Alam Towhid
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 SUBTOTAL 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)
