Calculate Weighted Average in Excel

Calculating Average is part of analysis, to analyze the average performance of the range of data set. Today we will try to focus method of calculating accurate averages. Normally, in Excel we use in-built function to calculate average by defining range of cells values in function. To calculate accurate average in excel we need to create nested function. There are two methods to carry out average calculation

• Simple Average
• Weighted Average

AVERAGE Function-

        Syntax: =AVERAGE(number1, [number2],....)

        example: =AVERAGE("A2:A10")

Average performs calculating mathematical mean value in given range or array. As you know mean is statistical method to calculate middle value. 

Concept of Average calculation.

     Sum of Values    

Count of Values

Convert this formula using Excel sum & count function

	A	B	C	D	E
1	Month	Stocks	NAV	Total Stocks	Total Price
2	Jan	MF - Magnum	$22.72	10	$227.20
3	Feb	MF - Magnum	$23.89	12	$286.68
4	Mar	MF - Magnum	$17.78	34	$604.52
5	Apr	MF - Magnum	$22.72	12	$272.64
6	May	MF - Magnum	$17.78	23	$408.94
7	Jun	MF - Magnum	$23.89	12	$286.68
8	Jul	MF - Magnum	$17.78	23	$408.94
9	Aug	MF - Magnum	$20.95	12	$251.40
10	Sep	MF - Magnum	$20.95	15	$314.25
11	Oct	MF - Magnum	$13.92	16	$222.72
12	Nov	MF - Magnum	$19.69	12	$236.28
13	Dec	MF - Magnum	$17.78	23	$408.94

Apply SUM and COUNT Function in Excel to evaluate average of above stocks in table

=SUM(C2:C13)/COUNT(C2:C13)

Result: $19.9875

Now to calculate Weighted Average, We need to carry out the Total price of stocks for each month and calculate by dividing total stocks.

Here SUM or COUNT function did'nt work. Using SUMPRODUCT Function with SUM should work.

SUMPRODUCT Function - Returns the sum of product of corresponding ranges of arrays.
    Syntax: =SUMPRODUCT(Array1, [array2], [array3],...)

    =SUMPRODUCT(C2:C13*D2:D13)/SUM(D2:D13)

Result: $19.2607

This is how Simple Average Calculation and Weighted Average calculation is differentiated.

Let us try another example to evaluate the Sales Representative performance of last three years.

In above example, see the difference of Simple Average and Weighted Average. Always use weighted average to calculate or gauge the performance

Hope you found this useful

Subscribe for more updates 

Download example workbook Weighted Average in Excel  on Google Drive