I need an excel formula to get the average handle time for emails that are being answered.
it will then be 450 minutes / # of emails answered : this is on a daily basis
i need a formula for the daily, weekly and monthly.
please help me. excel sheet i'm creating is becoming a real mess now.
i will really appreciate your help.
I have created a spreadsheet for you to a) use and b) to learn from.
It is an Automated spreadsheet (as they should be) which calculates the number of minutes in a working week or month and calculates the average time per email giving Daily, Weekly and Monthly Outputs. It takes into account Public Holidays (or for time off). You can use the Output to create Graphs etc to visually display the Output.
It also allows you to calculate a Part Month average.
I have displayed it as it was CONSTRUCTED and as it would be USED.
The As Used worksheet is Protected and the only Inputs that can be done are in the Green Boxes (also the Saturday and Sunday boxes but you will need to Unhide the Validation List to include these and then to add 2 more columns titled Is Saturday? and Is Sunday? with the appropriate If Statement.
To unprotect the sheet go to Tools - Protection - Unprotect. There is no password so leave this blank.
All the workings are still there, the columns are just Hidden. To Unhide them, highlight the columns to the left and right of the hidden columns, click on Format - Columns - Unhide. To hide them again, highlight the columns that you want hidden, click on Format - Columns - Hide.
The LOGIC used (as in Functions) may seem complex but if you read the Descriptions in the first row you should be able to work out what and why it was done that way. Click on a cell to see what Function was used where.
You said that your spreadsheet was becoming a real mess, well I have created a monster for you (but not a mess).
I have uploaded the file to here:
http://users.tpg.com.au/lesliecl/
Hope this gives you the push to really start using Excel.
Posted on Apr 08, 2008
Sorry, I should have added: The minimum number of emails must be 1, NOT 0, otherwise the logic will not work (Divide by Zero errors). There are Functions used to remove false data such as 1 email.
Hi
I have just uploaded a final version of the spreadsheet. I have fixed a few bugs and added auto Day Naming feature.
As said previously, feel free to use this spreadsheet as your own, but please take the time to reverse engineer it to learn how it was created. This will help you immensely in thinking about how to create spreadsheets in the future.
Note: Keep a MASTER copy so that you can go back to the original whenever you have either changed Day Name to Public Holiday or Time Off (this field is a formula so overwriting it will remove the automatic features, even by selecting an input from the drop down list) or where you have changed any formulas.
By the way, I enjoyed doing this one, brings back memories.
Good luck.
×
May 13, 2013 | Microsoft Excel for PC
Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.
Formulas used in Excel must follow a certain syntax.
Aug 19, 2011 | Microsoft EXCEL 2004 for Mac
Oct 01, 2010 | Microsoft Office Excel 2007 Upgrade:...
Aug 26, 2009 | Microsoft Office Excel 2007
May 07, 2009 | Microsoft Excel 2007 Home and Student...
Mar 10, 2009 | Microsoft Excel for PC
Mar 09, 2009 | Microsoft Office Professional 2007 Full...
Dec 30, 2007 | Business & Productivity Software
Oct 25, 2007 | Microsoft Excel for PC
Sep 17, 2007 | Microsoft Office Standard for PC
Jan 28, 2016 | Microsoft Excel for PC
1,082 people viewed this question
Usually answered in minutes!
i need an excel formula to get the average handle time for emails that are being answered.
it will then be 450 minutes / # of emails answered : this is on a daily basis
i need a formula for the daily, weekly and monthly.
please help me. excel sheet i'm creating is becoming a real mess now.
i will really appreciate your help.
×