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.

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 is/are the formula(s) ?

=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)