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.
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!
here is the data I am working with https://docs.google.com/spreadsheets/d/17QQApWUn0-aW8r3G9Sm_iunaNAoyI_fo/edit?usp=sharing&ouid=11793...
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,
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 @HansVogelaar has said: divide those numbers by the number of weeks in your data sample.