Need help with streamlining formulas

[Belzarak]

Greetings Excel Gurus,

I was asked to put together something to provide call statistics from call log data provided by our phone system. I was able to put something together the accomplishes the goal, but it is very clunky and as the agents take more calls, it becomes increasingly slow for this workbook to process the data. I've had my computer seem to freeze with all 8 cores pegged out at 100% CPU usage for over an hour as it processes all of the formula. I know this has to be because of the formula that I'm using and there has to be a way to accomplish the goal of this workbook using different formula or using formula in a different manner that will not be quite so processor intensive. This is where I turn to you guys and gals to provide me some direction. I'm linking a sanitized sample sheet so that you can look it over. For those of you that aren't able to instantly grasp what each part of the workbook does, I'll explain each section below.

http://bladedancer.net/Belzarak/SLA Sample.xlsx

Calculation Tabs = Tabs Mon - Fri and Week Total

Calculation Tabs: Cells A5 - G5 - this is where the raw data from the phone system is pasted. This raw data can vary from 25 lines to 25k+ lines. Column A - System Storage ID - ignored for all calculations. Column B - Date/Time - Date and time of the call. Column C - Agent Name - name of the call taker. Columns D and E (Ext and From) are ignored for all calculations. Column F - Duration - the length of the call. Column G - Wait Time - the time the call was in queue before being answered.

Calculation Tabs: Cells I5 - M5 - Breaks apart some of the raw data to make it easier for other sections to work. Column I - Checks to see if the Agent Name field is blank, if it is, it puts the wait time of the call. If it is not blank, it puts a blank in this field. Column J - checks to see if the Agent Name field is blank, if it is, it puts a blank here. If it is not blank, it puts the hold time in this field. Columns K and L - Duration and Wait time - values of the Duration and Wait time in the raw data. These are here because the system occasionally outputs a blank space in front of the numbers. Column M - Time of Call - breaks the time out from the raw data Date/Time field.

Calculation Tabs: Cells P18 - AN18 - Checks to see if the Time of Call is between the targets and puts the Wait Time if it is.

Calculation Tabs: Cells P2 - AN16 - Various calculations on the call data per time interval, including total number of calls offered, calls abandoned, maximum wait time, average wait time, average duration and overall SLA (percentage of calls answered within target answer time).


Calculation Tabs: Cells AR6 - BO6 - Checks to see if the Time of Call is between the targets and puts the Duration if it is.

Calculation Tabs: Cells AR4 - BO4 - Provides the Average Duration of calls that occur during the target time period.

Calculation Tabs: Cells BR6 - CO6 - Checks to see if the Time of Call is between the targts and puts the Agent Name if it is.

Calculation Tabs: Cells BR4 - CO4 - provides the number of different agents that took calls during the target time period.

Data Tab: This is where all of the calculations done on the Calculations Tabs are displayed in easier to understand format to be presented to the managers and agents.

Variables Tab: I added this to allow us to set different SLA targets and to possibly ignore calls that abandon within a certain period of time. Our standard SLA target is 2 minutes, so if you are providing steam-lined formula and can't incorporate the variables, please use a 2 minute target. If I can get this stream-lined, I have no problem with having separate workbooks for the few things that would have a different SLA target.

Week Total Tab - The raw data from Mon-Fri are also pasted onto Week Total to get the running total calculations each week. I'm sure this can be done easier, but I'm not certain how to go about doing it.


TL;DR



Need help cleaning up a workbook so that it doesn't use as much system resources. Look at the linked workbook and help find a way to do things more cleanly. Any suggestions will be much appreciated.

http://bladedancer.net/Belzarak/SLA Sample.xlsx

Thanks to you all for looking at this.

-Belz

 

[jakeman]

Ok, Belz. As far as I can tell, the big problems are with the Week Totals sheet. The Answer Rate calculations for <5 minutes and >5 minutes are overkill. The same is true for total abandoned calls >0 and <0.

Here are my recommendations for how you might improve your calculation time.

1. go to the Variables sheet and enter this formula in cell C4, to the right of the Service level targets in B4. This will give you the time along with the target numbers: =IF(B4=5,TIME(0,5,0),IF(B4=4,TIME(0,4,0),IF(B4=3,TIME(0,3,0),IF(B4=2,TIME(0,2,0),IF(B4=1,TIME(0,1,0),IF(B4=0.5,TIME(0,0,30)))))))
2. Change the formatting for cell C4 to [hh]:mm:ss under Format --> Custom.
3. Then go to the Week total sheet and in cell P8 (answer rate <5), enter this formula instead of the really long one you have there currently: =COUNTIFS($M$6:$M$5000,">=9:00 AM",$M$6:$M$5000,"<9:30 AM",$J$6:$J$5000,"<="&Variables!$C$4)
4. In P9 (answer rate <5) of Week total, enter this formula: =COUNTIFS($M$6:$M$5000,">=9:00 AM",$M$6:$M$5000,"<9:30 AM",$J$6:$J$5000,">="&Variables!$C$4)
5. You'll need to change the formulas for Q8:AM9 to match.

Repeat these steps for total abandonded calls >0 and <0. Add a if statement in cell C2 of Variables sheet, etc. You get the picture, I hope.

Bottom line is you want to remove all of those If Statements and simply replace them with the matched value from the Variables sheet. You follow what I'm saying?

 

[jakeman]

Wow, I just noticed all of the formulas after column AN down to row 5000. Yikes...good luck with that.

 

[Belzarak]

@jakeman - That is exactly what I was looking for. I knew the variables had to be contributing to the processing load. Your solution greatly reduces that load. Thank you.

I know there are probably further ways to improve this sheet, so if you or anyone else out there have further suggestions, feel free to add them. I want to get this thing streamlined so that it will run smoothly and simply while still providing all of the information required.

Thanks,

-belz