How do I calculate call center "service level 45 seconds" Q1 SLA "weighted average" please

Question

How do I calculate call center "service level 45 seconds" Q1 SLA "weighted average" please

Carrie Ward 80

How do I calculate call center "service level 45 seconds" Q1 and Q2 SLA "weighted average" please. I need these weighted averages for Q1 and Q2 highlighted in green. Not certain if my calculations are correct but I do know that the answer for Q1 SLA weighted avg=17.20% and for Q2 SLA weighted avg=29.38%. How do I get the 17.20% and the 29.38%?

User's image

Accepted answer

0 additional answers

Your answer

Answer 1

Ian-Ng 2,450 Microsoft External Staff Moderator

Hi @Carrie Ward,

Good day! Thank you for reaching out to the Microsoft Q&A forum and sharing the details of your issue.

Regarding your question, let me clarify how the Q1 weighted average for the “Service Level 45 seconds” metric is calculated, and confirm why the result is 17.20%.

We use a weighted average formula so that each month’s SLA is adjusted for its share of call volume:

Weighted Average SLA = (Sum of (Service Level × Contacts Offered)) ÷ (Sum of Contacts Offered)

Note: I use Contacts Offered (or Contacts Queued) as the weight in the formula because not every month has the same call volume. This ensures that months with more calls have the right influence on the quarter’s SLA result.

In your table, the “Weighted Volume” column already represents the relative share of calls in each month. That means you can either:

• Multiply each SLA% by its Weighted Volume% (as you did), then divide by the sum of those weights.

• Or multiply SLA% by the actual Contacts Queued, then divide by the total calls in the quarter.

Both approaches lead to the same result.

Step-by-step example (Q1):

• Jan: 18.58% × 15.78% = 2.93

• Feb: 16.96% × 18.42% = 3.12

• Mar: 16.39% × 21.72% = 3.56

• Apr: 17.31% × 18.64% = 3.23

Therefore,

• Total numerator = 12.84

• Total denominator = 74.56%

• Weighted SLA = 12.84 ÷ 74.56% = 17.20%

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to figure out.

Thank you for your patience and your understanding. If you have any questions, please feel free to reach out.

I'm looking forward for your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Carrie Ward 80 Reputation points

2025-08-28T18:23:35.3966667+00:00

Perfect! Now I have updated my table with current data. Can you tell me how I calculate weighted SLA highlighted in yellow now? My formula for calculating weighted SLA Avg column is =(C2*J2)/J5
Ian-Ng 2,450 Reputation points Microsoft External Staff Moderator

2025-08-28T19:20:27.11+00:00

Hi @Carrie Ward,

Thank you for sharing the updated table with us. I reviewed the data and the way the weighted SLA is being calculated. The suitable approach is to take each service level percentage and weight it by the number of contacts queued, then divide by the total contacts queued. This ensures that each queue’s SLA contributes fairly according to its size.

For your table, the formula in Excel would be this:

=SUMPRODUCT(C2:C4, J2:J4) / SUM(J2:J4)

Using your values, the weighted SLA comes out to about 36.1%. This means the highlighted total represents the combined performance across all queues rather than just a simple average.

I appreciate you providing the details so clearly, and I can confirm this is the expected method to calculate the weighted SLA average.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable email notifications in case you would like to receive notifications about this thread.
Carrie Ward 80 Reputation points

2025-08-28T19:25:47.64+00:00

Your answer and the way you communicate is very clear for me, thank you! Last question is do we want to take the "contacts handled" and NOT "contacts queued"??
Ian-Ng 2,450 Reputation points Microsoft External Staff Moderator

2025-08-28T19:36:13.6966667+00:00

Hi @Carrie Ward,

I appreciate your kind words and really happy to hear that. If you find my answer helpful, please consider accepting it so others with similar concerns can see that it has been validated by you.

Regarding your question, it really depends on what you want the weighted SLA to represent.

If you use contacts queued as the weight, you measure SLA against the total demand (all contacts that entered the queue). This is often preferred because SLA is typically defined as a measure of service across all incoming contacts, including those that abandoned.

If you use contacts handled as the weight, you only measure SLA based on the contacts that were actually answered. This can make the SLA look slightly better, since it excludes the abandoned contacts that never got a chance to be served.

Some people choose contacts queued as the denominator because it gives the true picture of how the entire workload was managed. Using contacts handled is sometimes used internally to measure agent efficiency, but it doesn’t reflect the customer experience as fully as using queued.

I hope it will help address your concerns.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable email notifications in case you would like to receive notifications about this thread.
Carrie Ward 80 Reputation points

2025-08-28T19:38:41.9333333+00:00

Perfectly stated for my understanding!
Carrie Ward 80 Reputation points

2025-08-28T19:45:56.2733333+00:00

Question: =SUMPRODUCT(C2,J2)/SUM(J5) Is this the right formula to do it the "preferred" way?
Carrie Ward 80 Reputation points

2025-08-28T20:08:53.8466667+00:00

I think that the cells D2 thru D4 are weighted averages and that D5 is the real weighted avg=SUMPRODUCT(D2:D4,J2:J4)/J5. Thoughts?

I appreciate you!
Ian-Ng 2,450 Reputation points Microsoft External Staff Moderator

2025-08-28T20:24:16.94+00:00

Hi @Carrie Ward,

Thank for reaching out.
Cells D2–D4 are not weighted averages but each month’s weighted contribution to the quarter. Since they’re already adjusted by call volume, using SUMPRODUCT(D2:D4,J2:J4)/J5 double-weights them, which is why the result looks too low.

The correct quarterly SLA can be calculated as either:

• =SUM(D2:D4)

• or directly: =SUMPRODUCT(C2:C4,J2:J4)/SUM(J2:J4)

Both give the correct Q1 weighted SLA of about 36.28%.

Share via

How do I calculate call center "service level 45 seconds" Q1 SLA "weighted average" please

0 additional answers

Your answer