Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to calculate the weighted average in Excel

Posted on 2014-11-11
9
200 Views
Last Modified: 2014-11-14
In an Excel spreadsheet, I need to calculate the weighted average of a column B regarding weights specified in A
This mean A1*B1 + A2*B2 + ... An*Bn / SUM(A1:An)
Is there a formula to do that easily ?
0
Comment
Question by:LeTay
  • 5
  • 3
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434682
For A1:B3, you can use the following:

=SUMPRODUCT(A1:A3,B1:B3)/SUM(A1:A3)
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40434690
Please find attached excel has your result and some useful average formulas...
Average.xlsx
0
 

Author Comment

by:LeTay
ID: 40434707
Many thanks but I forgot to tell you this : some cells are empty
And so if for example B3 is empty (but A3 not), A3*B3 and A3 should not participated in the calculation
So ?
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40434719
So...

=SUMPRODUCT(A1:A3,B1:B3)/SUMIF(B1:B3,"<>0",A1:A3)

It's OK for A3*B3 to participate, because if B3 is empty, then A3*B3 = 0.

It's only the SUM that needs to change.

So.
0
 

Author Comment

by:LeTay
ID: 40436077
Formula is refused by Excel
Maybe a problem of , or ;
I don't know
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40436156
Can you please post your spreadsheet here.

Also, is your computer set up to be in standard English, or a different language?
0
 

Author Comment

by:LeTay
ID: 40443035
It is standard french
Will prepare a spreadsheet with data
0
 

Author Comment

by:LeTay
ID: 40443060
Problem was indeed french, and ;
But the test you propose ("<> 0") is not the good one.
Irrelevant cells are empty, not zero
So ?
0
 

Author Comment

by:LeTay
ID: 40443063
Found it :

=SUMPRODUCT(A1:A3,B1:B3)/SUMIF(B1:B3,"<>",A1:A3)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question