How to calculate Visitors per Week Day average for large data

Copper Contributor

Hello,

 

I have a large data set that includes the location a visitor was checked in at, the date and time of check in and Day of the Week column. I am trying to figure out the Average of visitor check-ins per weekday for the months of September 2023 - April 2024.

CRami6_0-1712343883280.png

 

All I can manage to get it the total of check in's per weekday by location. Every time I change the Value Field Settings to Average, it gives me crazy numbers that do not make sense. I appreciate any help! 

 

CRami6_1-1712343986497.png

here is the data I am working with https://docs.google.com/spreadsheets/d/17QQApWUn0-aW8r3G9Sm_iunaNAoyI_fo/edit?usp=sharing&ouid=11793... 

2 Replies

@CRami6 

Simply divide the count by the number of weeks in the data (27 in your example).

@CRami6 

Every time I change the Value Field Settings to Average, it gives me crazy numbers that do not make sense.

 

Those numbers--45nnn-- do make sense when you realize what you're putting into the Values field: dates. Those "numbers" are the way Excel stores dates. Today's date, for example, 4/5/24, is stored in Excel as 45387. (That's how Excel can do date math) So you're getting the average of all the Sundays, Mondays (, etc) dates, not of the number of people visiting. To get that, you need to put "Count of Location" into the Values field.

 

Doing that, I am able to get total counts for each location and day of the week,

mathetes_0-1712347714954.png

 

but when I switch to average I get a #DIV/0! error message. I'd be happy to spend more time trying to figure out why, but I think it's what @Hans Vogelaar has said: divide those numbers by the number of weeks in your data sample.