• 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.

Automatically updating a purposely created circular reference

J

JCTalk

Member
Hi All,

I have been trying to create a formula for when a call centre's client contracts are failing SLA that tells me the number of calls that are required within SLA to bring the failing contract back to the required SLA.

For example; Calls required are 80% in 20 seconds. Offered calls so far 8659. Calls handled within 80% 2000. Which makes the current SLA 23.10%.

I created a circular reference to iterate through the shortfall totalling until the New SLA reaches 80% (or the Excel iterations loop finishes, whichever comes first). The total calls required number should equal the number of calls required within SLA to bring the contract SLA back to 80%. I have uploaded an example.

This does work. However, I want the Total Calls Req number to update automatically when the Offered and Handled figures change (This updated raw data will be dropped in automatically each day). At the moment, to get the Total Calls Req cell to update I have to click in the formula bar and press return again which I assume runs the iteration again from scratch.

What would be the best way ensure this automatic update of the circular reference? Maybe a VBA script to look for changes in those cells? If this is the best way, please can anyone help with the script I'd need?

Is there a better way that I can achieve the overall result, other than using a circular reference?

Many thanks
 

Attachments

JCTalk

Firstly, Welcome to the Chandoo.org Forums

I used some simple math to rearrange the Formula in G2: to get what F2 should be

F2: =(C2-A2*B2)/(A2-1)
G2: =(C2+F2)/(B2+F2)

No iterations required
 
In case your wondering how I got the above?
in G2: =(C2+F2)/(B2+F2) = 80%

0.8 = (C2+F2)/(B2+F2)
0.8*(B2+F2) = (C2+F2)
0.8*B2 + 0.8*F2 = C2+F2
0.8*F2 - F2 = C2 - 0.8*B2
-0.2*F2 = C2 - 0.8*B2
F2 = (C2 - 0.8*B2)/-0.2

Now 80% = 0.8 = A2
so:

F2 = (C2 - A2*B2)/(A2-1)

Enjoy
 
Hi Hui,

That is absolutely fantastic! I have absolutely no idea how you did it though haha. I saw your first post this morning about 12 hours ago but I've not been able to get on a machine to test. It works flawlessly and is so much more efficient than my rambling mess lol. :-(

Many thanks for stepping through it. I think I need to spend some time with it to fully understand what's happening. Unfortunately math waved bye bye to me a long time ago haha.

Thank you again so much Hui. I owe you a pint, else a small donation as per your signature which will be realised shortly. Thank you sir. :-)
 
Hi Hui,

I've made a small donation as promised. Many thanks for your help again, I'd never have reached that on my own lol.

I've noticed that when the target SLA is 100%, the formula appears to break and give a #DIV/0! error. If I change it to 99.9% its fine. Can you see at all why that might happen?

In addition to the last calculation, I've realised that I also need to calculate the same but an abandoned figure.

The scenario is; Abandoned calls must be BELOW a target (e.g. say 6%). Offered calls so far 100. Abandoned calls so far 10. Which makes the current abandon SLA 10.00%.

I've managed to get it working to show me the number of calls required to bring the abandon SLA back under the target 6% ((AbandonedCalls-TargetSLA*OfferedCalls)/TargetSLA), but I'm having difficulty with the formula for when you are already under the target SLA, to show how many calls you can afford to abandon before you will exceed the target SLA.

I imagine the formula must be largely very similar, but I can't seem to get to it.

Do you have any thoughts on how I can achieve this?

Many thanks
 

Attachments

Hi ,

The problem you are facing when the target is 100 % is not an issue of the formula breaking ; it is a matter of logic.

If a target is 100 % , and I have fallen behind , no matter by what amount , the fact is I can never catch up.

If a target is below 100 % , when I fall behind , I can always catch up because there is room for me to go above the target ; when the target is 100 % , this room is not available.

Narayan
 
NARAYANK991 said:
If a target is 100 % , and I have fallen behind , no matter by what amount , the fact is I can never catch up.
Click to expand...

Oh my gosh. I genuinely cannot believe I have been that dumb I am so sorry. What this tells me is that I have spent entirely too much time on this spreadsheet haha. :)

Thank you Narayan.
 
JCTalk said:
The scenario is; Abandoned calls must be BELOW a target (e.g. say 6%). Offered calls so far 100. Abandoned calls so far 10. Which makes the current abandon SLA 10.00%.

I've managed to get it working to show me the number of calls required to bring the abandon SLA back under the target 6% ((AbandonedCalls-TargetSLA*OfferedCalls)/TargetSLA), but I'm having difficulty with the formula for when you are already under the target SLA, to show how many calls you can afford to abandon before you will exceed the target SLA.
Click to expand...

Not sure if my addon question was seen so I've quoted it above.

Does anyone have any thoughts how this might be achieved?

Many thanks
 
Hi ,

Can you see this file ?

The cell colored Peach is the one which will need to be entered , since for different values entered in this cell , there can be different values for the Abandoned Calls.

Or are you looking to get the minimum number of calls required to achieve the target SLA ?

Narayan
 

Attachments

Hi Narayan,

The calculation is to do with an Abandon SLA that by the end of the month the percentage of total abandons must be EQUAL TO OR LOWER than the target (6%).

I have since come up with...
ROUND(OfferedCalls*(6%-CurrentAbanSLA),0)

This seems to work. The number it shows me, if I follow it through and project another day based on the figure the formula gave, as long as you don't abandon any more than that figure, the AbanSLA is always under the 6%.

Does this look right to you?

Many thanks
 
Hui said:
I used some simple math to rearrange the Formula in G2: to get what F2 should be

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

Hi Hui,

I'm trying to work the opposite situation now, but it doesn't seem to follow the original formula. I've tried to work it back using the same method you did, but I'm afraid it's not getting me any closer.

What I'm trying to do now; Using the exact same previous information, if the SLA is over performing, how many calls can I afford to miss my target before my SLA slips below the aforementioned target.

So example; I'm at 89.58%, using the same data from before, how many calls can I miss the answered in 20 seconds SLA (80% target) until my actual SLA drops to the 80% target.

Could you please can you help me with that?

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