Calculate a weighted average for a SLA and a conversation time with a formula in an excel report

In one of my experiences, I had a tool that gave me the weighted average for the SLA and the conversation time but I needed to spend a little time to extract the data and to make the thing “easier”, I needed to put the data manually because the tool didn’t allow to extract the data in excel. To speed up and to save me time, I started to work on a formula to make my life better.

formula weighted average

 

When I use the formula ?

When I need to know the weighted average instead of a simple average because I need to take in account different factors.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formulas ?

=SUMPRODUCT()

=SUM()

To match the result of the tool with the formula, I needed to figure out an additional input for the conversation time which is “0.0003”. Put this formula in an empty cell and format it in “time”:

=SUMPRODUCT(B4:M4,B5:M5)/SUM(B5:M5)+0.0003

Explanation:

  • B4:M4, monthly attended call time (format in time)
  • B5:M5, monthly attended call numbers (format in number)

For the SLA, put this formula in an empty cell and format it in “percentage”:

=SUMPRODUCT(B2:M2,B3:M3)/SUM(B3:M3)

Explanation:

  • B2:M2, monthly SLA percentage (format in percentage)
  • B3:M3, monthly call numbers (format in number)
formula weighted average

Interesting Management