I'm looking to get a day and hourly average of chats for Mon, Tue, Wed, etc

Hello-- I have data that is representation of online chats received and would like to be able to show an average of the number of chats I receive on a daily bases as the year progresses.


1. I'm looking to get a day numeric average of chats for Mon, Tue, Wed, etc.
2. I'd also like to know how to get an average of chats received on an hourly basis. ex 6:00-7:00am, 7:00-8am etc.

I have attached a document which has one tab with data and one tab with a pivot table started.

I assume that you would like the Count of Day of week2 column to put 715.2 in each row.
My thought, as you are using the 2007 version of Excel, would be to use formulas.
For count per day: =COUNTIFS(Table7[Day of week],B18)
For average: =SUM(C$18:C$22)/COUNTA(B$18:B$22)
The chart shows the count per day as columns and the average as a line.
If this isn't what you want, then please provide a small file that illustrates (using manually calculated values if necessary) your desired outcome.
Let us know if you have any questions.

This is close. What I need to know is if there are 4 Mondays and the total for chats on Mondays is divided by 4. Example if it is a sum count of 406 then can I get the average column to show 101.5?

Another column has been added to Table7.
The added column is populated using: =IF(D2<>D1,1,"")
The formula count per day for is: =COUNTIFS(Table7[Day of week],B18,Table7[Month],B$15)
The formula for average is: =C18/SUMIFS(Table7[WD/M],Table7[Day of week],B18,Table7[Month],B$15)
Note that cell B15 contains the drop down for month.
If you are using a version newer than 2007 we may be able to utilize Power Pivot to make a Pivot Table/Chart that shows the average.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.