While working with Excel, you are likely to face a scenario where you need to compute the average price of a product. With this intention, this article hopes to provide a quick and convenient guide on how to calculate the average selling price in Excel. Lastly, you’ll see a dedicated method to find the weighted average selling price.
What Is the Average Selling Price?
 As the name implies, the average selling price refers to the arithmetic mean of the selling price of a product or service. Generally speaking, the formula for calculating the average selling price is as follows.
= Selling Prices of Products / Number of Units Sold
How to Calculate Average Selling Price in Excel: 3 Methods
Fortunately, Microsoft Excel offers built-in functions to calculate the average values. So, without further ado, let’s start.
Let’s consider the following dataset shown in B4:D14 cells. Here, the first column shows the Exhibit number, next the second column displays the House Type, and finally, the third column refers to the Selling Price of the house respectively.
1. Calculating Average Selling Price with Arithmetic Formula
The first method utilizes two Excel functions COUNT, and SUM to manually calculate the Average Selling Price in a step-by-step manner.
Steps:
- Firstly, count the number of entries with the help of the COUNT function.
- Additionally, the COUNT function returns the number of entries in a range.
=COUNT(D5:D14)Â
Here, the range D5:D14 refers to the (value 1 argument) which is the Selling Price.
- Secondly, we calculate the summation of all the Selling Prices with the SUM function.
- Moreover, the SUM function adds all the values in a selected range.
=SUM(D5:D14)
Here, the range D5:D14 represents the (number 1 argument) which is the Selling Price.
- In the last step, we divide the Total Selling Price in the G5 cell by the number of Counts in the G4 cell to obtain the average selling price.
Read More: How to Calculate Selling Price Per Unit in Excel
2. Using AVERAGE Function to Calculate the Average Selling Price
If the first method is too much work and you’re in a hurry then, our next method calculates the average selling price using the AVERAGE function. In short, the AVERAGE function computes the arithmetic mean of the arguments. Let’s see it in action.
=AVERAGE(D5:D14)
As shown above, the range D5:D14 refers to the (number 1 argument) which corresponds to the Selling Price.
Read More: How to Calculate Selling Price in Excel
3. Applying SUBTOTAL Function to Calculate Average Selling Price
For those of you who want to learn about more techniques, there is a third way to calculate the average. Simply put, it involves the use of the SUBTOTAL function in Excel. Just follow along. The SUBTOTAL function returns the total value from a list or database.
=SUBTOTAL(1,D5:D14)
In the formula above, the number 1 represents the (function_num argument) which is the Excel-assigned number for calculating the average. In contrast, the D5:D14 cells refer to the (ref 1 argument) which is the list of Selling Price.
Read More: How to Calculate Selling Price from Cost and Margin in Excel
Calculating Weighted Average Selling Price
Sometimes, you have to find the weighted average selling price instead of getting the simple average selling price. Unfortunately, Microsoft Excel does not offer any built-in functions to calculate weighted average however, we can make our formula for weighted average using the SUMPRODUCT function with the widely used SUM function.Â
Considering the dataset (in B4:D13) shown below where we have the Brand name, the Selling Price of Cell Phone, and the number of Units Purchased respectively.
The SUMPRODUCT function performs multiplication with the given arrays and returns the summation of those products.
=SUMPRODUCT(C5:C13,D5:D13)/SUM(D5:D13)
As shown in the expression above, the C5:C13 and, the D5:D13 arrays refer to the array1 and array2 arguments respectively. Then, dividing the sum of the products by the sum of the number of Units Purchased gives the weighted average selling price of the cell phones.
Download Practice Workbook
Conclusion
I hope these simple and useful methods mentioned above will answer your queries on how to calculate the average selling price in Excel. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles on the Exceldemy website.
