Help - Trying to chart average calls per hour with data from YTD 2016

[reem01]

I have a attached spreadsheet that has 2100 lines of data from our call queue system. What I would like to do is get data to show during each our, how many calls on average do we get? I would love this at a monthly view, select Jan from the drop down Sheet1 A1 and have it populate that average for each hour period by month. I have not been able to figure it out. As you can see on Sheet1, I am getting the total for each month. I tried AVERAGEIFS with no luck. The Data sheet is where the data is held. I added columns K, L, and M. Column B is where I am getting that data. I couldn't figure out how to skip the date info in those fields to only use the time data, and vice versus on ignoring the time to get only the data information. Sorry if this is confusing, tried to explain it the best I could. Any help would be greatly appreciated.

Thanks

[JeteMc]

The formula in Sheet1!B2 is =AVERAGEIFS(Data!E:E,Data!$M:$M,">"&A2,Data!$M:$M,"<"&A3) with the average_range or range to be averaged being Data!E:E. That column contains text, whereas the average_range needs to contain numeric data for which an arithmetic mean can be taken. Notice that the first entry under the 'Remarks' in help states: If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
Let us know if you have any questions.
Edit: Thinking on this a little further it seems that what you might be looking for is the average number of calls made during each hourly period per work day from the earliest date of the time range in column B (Jan 10) on the data sheet through the most recent date (Nov 14). If that is a correct assumption then try pasting the following formula in Sheet1! C2 and double click the fill handle to copy it down:

Please Login or Register  to view this content.

Note: there seem to be some duplicate entries in column B of the data sheet, such as in B2 and B3, not sure if that is the case, but thought it would help to point it out just in case.

[reem01]

JeteMc, thank you very much, the second formula is what I was looking for.

Appreciate the assistance!