• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating number from positive SLA to target

J

JCTalk

Member
Hi,

I mis-read the forum notes and didn't realise I had to post a new thread for a different question. As it was related, I wrongly assumed it should be kept in the same thread. My apologies.

My question. Using a telephony call centre as the situation. I have a formula kindly provided by Hui to calculate the required number of calls within SLA to reach a target SLA.

Hui said:
F2: =(C2-A2*B2)/(A2-1)
Click to expand...

I'm now trying to calculate, if the service is over performing for the SLA, how many calls can I afford to miss before my SLA slips below the target.
So example; My SLA is currently at 87.50%. How many calls can I miss SLA on, before my actual SLA drops to the 80% target.

Many thanks in advance for your help.
 

Attachments

NARAYANK991 said:
I think this thread is the same as an earlier thread , so I will upload the same workbook.
Click to expand...

Hi Narayan,

Many thanks for your reply. I didn't see the early thread I apologise.

May I ask two questions...
  1. Since the formula will be placed in a column titled required calls, ideally I would like the over performing result to appear as a negative figure so I can use conditional formatting to differentiate. Do you know how I can convert that over performing figure to show as a negative (without including an add-on pack)?
  2. The over performing part of the formula relies on a separate bit (formula in cell column G which comes out as a 1 or a 0). Is there a way of including that in the main formula?
Many thanks
 
Hi ,

Can you clarify by using worksheet cell references i.e. which cell or column reference are you meaning when you use the term over performing result ?

Which cell has what you call main formula ?

If you can specify these two with respect to any uploaded file , I will find it easier to understand.

Narayan
 
Hi Narayan,

Absolutely, no problem. My apologies for not clarifying.

On the Excel template you uploaded...

I refer to the main formula being the column labelled Additional Calls (highlighted orange). So all those E5, E6, E7 etc. That's the cell that works out how many additional calls is required if you are underperforming SLA, and also if the SLA is over performing (by way of IF statement I see to determine which to use).

So for my first question, I'd like to know if it's possible to display the result of the formula that works out when over performing, as a negative figure.

For my second question. The over performing part of the cell e.g. E5 refers to another cell that isn't part of my workbook G5, G6, G7 etc. I could make it part of my workbook, but ideally I wondered if the calculation in cell e.g. G5, could be included in the main formula for over performing SLA's (cell E5, E6, etc.) to save me adding another column.

Many thanks Narayan.
 
Hi ,

See this file for the conditional formatting part ; regarding your second question I have not understood what is the additional cell you mention ; all of the formulae in the uploaded file are referring to cells in this same workbook ; there is no external reference.

Can you post your workbook with the external reference and the external formula which you want incorporated into this workbook ?

Narayan
 

Attachments

Hi Narayan,

Sorry I have confused the issue. My apologies.

All of the information was contained in the Abandon Calculation workbook you have worked from already.

In my mind, if the SLA is over performing, the formula in column D calculates the number of calls that can missed but still reach the SLA target (80%). But it doesn't quite work as it can be 1 or 2 calls out.

In your example, 188 calls can be missed, but when you add 188 to 2000 and work out Handled So Far (1750) / Actual Offered So Far (2188) it comes to 79.98%. The correct column D "Additional Calls" figure needed to be 187. to make sure the SLA was still above 80% (In that case it would have been 80.02%).

Is it possible to get the calculation in column E to reach the correct figure, without having to add on column F "Additional Handled" to "Handled So Far" figure?

To make it easier, I have uploaded another version of the example. It needs to only use these columns. Not add anymore columns. Is that possible?

Many thanks Narayan.
 

Attachments

Hi ,

There is some misunderstanding ; the columns E , F , G and H are there to substantiate the fact that the figure in D is correct ; the figure in D does not depend on them.

However , if you do eliminate them , then how will you arrive at the number of additional calls missed in the cases shown in the uploaded file ?

Narayan
 

Attachments

Hi Narayan,

Whatever has changed is now showing the cell in column D correctly as I would expect it. I threw it a few tests and it seems to pass them all.

The formula is going to be put into a different worksheet that will populate with new data everyday. All I need the formula to do is provide the information in cell D. I don't need any other calculation from the remaining E, F, G & H cells.

You've done a fantastic job there Narayan, I most sincerely appreciate all your help. Do you have any links like Hui does for small donations to show my gratitude?

Thank you again.
 
Hi ,

Thanks for the feedback , and the appreciation.

I do not have any link for a donation , but it's the thought that counts ; I appreciate your goodwill and generosity. Thanks.

Narayan
 
You must log in or register to reply here.
Back
Top