+ Reply to Thread
Results 1 to 3 of 3

Avg call per hour per day, inbound/outbound

  1. #1
    Registered User
    Join Date
    07-15-2021
    Location
    Scottsdale, AZ
    MS-Off Ver
    Latest
    Posts
    4

    Avg call per hour per day, inbound/outbound

    I want to look at our call capacity, specifically, average calls per day of the month per hour. I've added a helper column to represent the day of the week numerically, but still cannot figure out how to calculate that average field when I need to look at month, number of "days", hours of the day, call total. Please help! I've tried many different calculated fields, but can never get the right answer.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-18-2021
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    5

    Re: Avg call per hour per day, inbound/outbound

    I've inserted a few Pivot tables, not sure if thats what you are looking for.
    I had to delete some rows because the file was too large.
    Attached Files Attached Files

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,243

    Re: Avg call per hour per day, inbound/outbound

    I'm not quite sure if this is what you want.

    First, I converted the data to an Excel Table called Table_Data. There are too many advantages to using tables not to use them. A couple of them are that tables "know" how big they are and they "remember" formulas and copy them down. So you could eliminate the data in this table and copy and paste new data in and it will compute properly. Also, you can use table column headers in the formulas and this makes them easier to understand.

    I also added a couple of helper columns of my own.

    I put the "answers" in another table. I only broke it out by month and day of the week across the columns. If you want averages also by hour of the day, you'll have to expand the orange table.

    I populated the first column with the first of each month because it was an easy way to get the field populated. I just had to drag the dates down.

    Column L has the formula: =TEXT([@Date],"mmm") - notice how the column header is used in the formula. The @ indicates current row, Without the @ sign it means whole column.

    Column N has the formula: =DAY(EOMONTH([@Date],0)) - EOMONTH gets the end of the month for the number of months shown after the comma. For example EOMONTH(12/10/2021,1) would be January 31, 2022. So if there is a zero after the comma, it returns the end of the current month. EOMONTH(1/1/2021) = January 31, 2021. Wrapping it in DAY gets the day value of the date.

    The rest of the formulas are of the nature: =COUNTIFS(Table_Data[Year],YEAR([@Date]),Table_Data[Month],[@Month],Table_Data[Day],N$1)/[@[Days in Month]]
    Count where the Years and Months Match and also Day of the Week matches the column header.

    Column U drops the requirement to match the day of the week, so it is the monthly average.

    I changed the file type to XLSB because it was too large to upload as a XLSX.
    Attached Files Attached Files
    Last edited by dflak; 01-14-2022 at 05:56 PM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to calculate lead times using inbound, outbound and stock data
    By MattLudlam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2020, 03:53 PM
  2. Inbound Matrix
    By borlach123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2017, 09:02 PM
  3. [SOLVED] Outbound Calls per hour by agent
    By Cocohantas in forum Excel General
    Replies: 4
    Last Post: 03-22-2016, 07:11 AM
  4. Call Center Help - Determining Calls per Hour by Day of the Week
    By emeraldgsl in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-12-2014, 06:48 PM
  5. COUNTIF Function using time to extract call stats per hour
    By MHayward in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2014, 08:45 AM
  6. Click Counter by Hour for Operator Call Log
    By gpjcole in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-04-2010, 11:59 AM
  7. [SOLVED] total work hours for 24 hour on-call schedules
    By Kate in forum Excel General
    Replies: 1
    Last Post: 02-06-2006, 11:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1