How to Calculate Weighted Average Price in Excel (3 Easy Ways)

Weighted Average is one kind of average that includes the varying degrees of importance of numbers in a dataset. In order to calculate weighted average price in Excel, each number is multiplied by the predetermined weight before the final calculation.

For more clarification, we are going to use a Dataset containing Product, Price, and Quantity (as Weight) columns.

How to Calculate Weighted Average Price in Excel


Download Practice Workbook


3 Easy Ways to Calculate Weighted Average Price in Excel

1. Employing Generic Formula to Calculate Weighted Average Price

We can calculate weighted average price quite easily by employing the Generic Formula. Actually, Generic Formula is a mathematical operation. It does not use any in-built functions or processing either.

Steps:

  • Select a cell to have the Weighted Average. Here, I selected cell C11.
  • Input the following formula.
=(C5*D5+C6*D6+C7*D7+C8*D8+C9*D9)/(D5+D6+D7+D8+D9)

Here, the Price with the connected Quantity is multiplied and the summation of them is calculated. Then, the summation is divided by the summation of Weights which is mentioned in the Quantity column.

  • Press ENTER.

How to Calculate Weighted Average Price in Excel

We can see the result on the selected cell.

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


2. Using SUM Function to Calculate Weighted Average Price

The use of the SUM Function is another easy way to calculate Weighted Average Price.

Steps:

  • Firstly, select a cell to have the Weighted Average. Here, I selected cell C11.
  • Employ the SUM Function.
=SUM(C5:C9*D5:D9)/SUM(D5:D9)

Here, I selected the Price range C5 to C9 and the Quantity range D5 to D9 to multiplicate. Finally, the added result of the multiplications is divided with the summation of Quantity ranging from D5 to D9.

  • Then, press ENTER if you are using OFFICE 365/2021. Otherwise, press CTRL + SHIFT + ENTER.

How to Calculate Weighted Average Price in Excel

We can have the desired result in front of our eyes.

Read More: How to Calculate Retail Price in Excel (2 Suitable Ways)


Similar Readings


3. Applying SUM & SUMPRODUCT Functions to Calculate Weighted Average Price

Application of the SUMPRODUCT Function along with the SUM function is another cool way to calculate the weighted average price.

Steps:

  • Choose a cell to have the Weighted Average. Here, I chose cell C11.
  • Apply the SUMPRODUCT Function.
=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)

Here, I selected the Price range C5 to C9 and Quantity range D5 to D9 to apply the SUMPRODUCT Function. Finally, the result is divided with the summation of Quantity ranging from D5 to D9.

  • Hit ENTER to have the result.

How to Calculate Weighted Average Price in Excel

Read More: How to Calculate Weighted Moving Average in Excel (3 Methods)


Practice Section

You can practice here for more expertise.


Conclusion

I have tried to articulate 3 ways on how to calculate weighted average price in Excel. I hope it will be helpful for Excel users. For any more questions, comment below.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo