How to Calculate Average Selling Price in Excel (3 Easy Methods)

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.


Download Practice Workbook


What Is 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


3 Methods to Calculate Average Selling Price in Excel 

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.

Dataset 1


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.

Using COUNT Function

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

Using SUM Function

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

How to Calculate Average Selling Price in Excel Using Arithmetic Method

Read More: How to Calculate Selling Price Per Unit in Excel (3 Easy Ways)


Similar Readings


2. Using AVERAGE Function to Calculate 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.

How to Calculate Average Selling Price in Excel Using AVERAGE Function

Read More: How to Calculate Selling Price in Excel (4 Easy Methods)


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.

How to Calculate Average Selling Price in Excel Using SUBTOTAL Function

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.

Dataset 2

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.

How to Calculate Average Selling Price in Excel Using SUMPRODUCT AND SUM Functions


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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo