# Avg call per hour per day, inbound/outbound

1. ## 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.

2. ## 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.

3. ## 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.

##### Users Browsing this Thread

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

#### 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