Forum Discussion

CRami6's avatar
CRami6
Copper Contributor
Apr 05, 2024

How to calculate Visitors per Week Day average for large data

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 c...
excel
Formulas and Functions
mathetes's avatar
mathetes
Silver Contributor
Apr 05, 2024

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,

 

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.

Resources