Excel inbuilt Average function returns the average(or Mean) of a range of a data. However in many occasions we are required to calculate weighted average. Though Excel do not comes with a straight function to calculate weighted average. This can be done with the use of SUMPRODUCT and SUM functions. Let us start learning about weighted average with an example.
Below is the sample data from a call center and let us calculate the Average Handle Time(AHT) for the day using different intervals calls and AHT. The below data shows that initial hours calls were less and the AHT too was less. But as the call volume increased the AHT also increased.,
The simple average formula =AVERAGE(C3:C9) returns 216.42. The problem with this is that it calculates average without considering the number calls. So we need to calculate average considering the number of calls received on each intervals. Ideally, the weighted average is the appropriate method to calculate like this calculations.
So weighted average formula for the above example is =SUMPRODUCT(C3:C9,B3:B9)/SUM(B3:B9)
This formula multiplies each AHT by its corresponding number calls and then adds all those products. The result is then divided by the number of days. This formula can be easily implemented to other types of calculations where weighted average is required.
Leave a Reply