Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to calculate weighted average in Excel?

For example, you have a shopping list with prices, weights, and amounts. You can easily calculate average price with the AVERAGE function in Excel. But what if weighted average price? In this article, I will introduce a method to calculate the weighted average, as well as a method to calculate weighted average if meeting specific criteria in Excel.

Calculate weighted average in Excel

Calculate weighted average if meeting given criteria in Excel

Easily batch average/sum/count columns if meeting criteria in another column

Kutools for Excel prides an powerful utility of Advanced Combine Rows which can help you quickly combine rows with criteria in one column, and average/sum/count/max/min/product other columns at the same time. Full Feature Free Trial 60-day!
ad advanced combine rows



arrow blue right bubbleCalculate weighted average in Excel

Supposing your shopping list is as below screenshot shown. You can easily calculate the weighted average price with combining the SUMPRODUCT function and SUM function in Excel, and you can get it done as follows:

Step 1: Select a blank cell, says Cell F2, enter the formula =SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18) into it, and press the Enter key.

doc weighted average 2

Note: In the formula =SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18), C2:C18 is the Weight column, D2:D18 is the Price column, and you can change both based on your needs.

Step 2: The weighted average price may include too many decimal places. To change the decimal places, you can select the cell, and then click the Increase Decimal button or Decrease Decimal button  on the Home tab.

doc weighted average 3


arrow blue right bubbleCalculate weighted average if meeting given criteria in Excel

The formula we introduced above will calculate the weighted average price of all fruits. But sometimes you may want to calculate the weighted average if meeting given criteria, such as the weighted average price of Apple, and you can try this way.

Step 1: Select a blank cell, for example Cell F8, enter the formula =SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18) into it, and press the Enter key.

doc weighted average 6

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

Note: In the formula of =SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18), B2:B18 is the Fruit column, C2:C18 is the Weight column, D2:D18 is the Price column, "Apple" is the specific criteria you want to calculate weighted average by, and you can change them based on your needs.

Step 2: Select the cell with this formula, and then click the Increase Decimal button or Decrease Decimal button on the Home tab to change the decimal places of weighted average price of apple.


arrow blue right bubbleRelated articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Andy · 2 years ago
    I am trying to calculate a weighted average on a subset of data that meets certain criteria. Lets use the following as the data;

    Name (Column A) Length (Column B) Value (Column C)
    Red Oak 10 ft $100
    Red Oak 15 ft $150
    Red Oak 21 ft $210
    Birch 5 ft $35
    Birch 8 ft $50
    Birch 11 ft $85

    I am trying to calculate the weighted average length per Tree name based on Value as the measure you use to weight it. So for red oak the weight on the first line is .217391 (100/460). So my weighted average length based on value of inventory is 16.65217 ft. I believe I would use SUMPRODUCT like in your example above, but I can't get it to work.