# How to calculate the weighted average in Excel

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 ?
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Director, Practice Manager and Computing ConsultantCommented:
For A1:B3, you can use the following:

=SUMPRODUCT(A1:A3,B1:B3)/SUM(A1:A3)
0
Data AnalystCommented:
Please find attached excel has your result and some useful average formulas...
Average.xlsx
0
Author Commented:
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
Director, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Formula is refused by Excel
Maybe a problem of , or ;
I don't know
0
Director, Practice Manager and Computing ConsultantCommented:

Also, is your computer set up to be in standard English, or a different language?
0
Author Commented:
It is standard french
Will prepare a spreadsheet with data
0
Author Commented:
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 Commented:
Found it :

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