Hello,

I am trying to come up with a formula that will help in calculating
averages in my visitor tracking spreadsheet. My spreadsheet has a lot
of visitor demographics and head count data entered by day. Across the
top, I have dates as column labels. Down the side, I have different
visitor demographic age ranges as row labels. On the lowest row, I
have a total of all the visitors in any given day.

I've already figured out the formula to count the different age groups
and genders. Now what I need is a formula to count the total visitors
per day, but end up with totals for all Mondays, Tuesdays,
Wednesdays,etc.......

Other info: We are only open Monday - Saturday, so I only have those
dates across the top.

Here is a screenshot of the spreadsheet:
http://www.flickr.com/photos/jenkob/2628527135/


My ultimate end goal is to be able to get an average number of
visitors for each day of the week. (Example: Average Saturday
visitation- 12 people)

I have tried to modify this formula which I've used to count the
totals in my gender columns, for each age row:
=SUMPRODUCT(--(MOD(COLUMN(5:5),2)=0),5:5)

But it does not work because it only counts every *other* column, and
what I need to do is count every 12th column. I tried to modify the
above as follows:
MOD(...,12)

But when I checked it, the figure was incorrect. So there must be
something wrong.

Can anyone help?

You need an offset to account for the first non-data columns:

If your first number to use is in Column ???, use
=SUMPRODUCT(--(MOD(COLUMN(5:5)-COLUMN($???$1),12)=0),5:5)

Replace the ??? with the column letter, like this for column B:
=SUMPRODUCT(--(MOD(COLUMN(5:5)-COLUMN($B$1),12)=0),5:5)

So, to get the average, you need to count the filled in cells, so use

=SUMPRODUCT((MOD(COLUMN(5:5)-COLUMN($B$1),12)=0)*5:5)/SUMPRODUCT((MOD(COLUMN(5:5)-COLUMN($B$1),12)=0)*(5:5<>"")*1)

HTH,
Bernie
MS Excel MVP


<(E-Mail Removed)> wrote in message
news:d60726bf-ce79-4266-943d-(E-Mail Removed)...
> Hello,
>
> I am trying to come up with a formula that will help in calculating
> averages in my visitor tracking spreadsheet. My spreadsheet has a lot
> of visitor demographics and head count data entered by day. Across the
> top, I have dates as column labels. Down the side, I have different
> visitor demographic age ranges as row labels. On the lowest row, I
> have a total of all the visitors in any given day.
>
> I've already figured out the formula to count the different age groups
> and genders. Now what I need is a formula to count the total visitors
> per day, but end up with totals for all Mondays, Tuesdays,
> Wednesdays,etc.......
>
> Other info: We are only open Monday - Saturday, so I only have those
> dates across the top.
>
> Here is a screenshot of the spreadsheet:
> http://www.flickr.com/photos/jenkob/2628527135/
>
>
> My ultimate end goal is to be able to get an average number of
> visitors for each day of the week. (Example: Average Saturday
> visitation- 12 people)
>
> I have tried to modify this formula which I've used to count the
> totals in my gender columns, for each age row:
> =SUMPRODUCT(--(MOD(COLUMN(5:5),2)=0),5:5)
>
> But it does not work because it only counts every *other* column, and
> what I need to do is count every 12th column. I tried to modify the
> above as follows:
> MOD(...,12)
>
> But when I checked it, the figure was incorrect. So there must be
> something wrong.
>
> Can anyone help?

Thank you, that works!! The averages formula doesn't, but I think I
can work around that another way. (I know how many Saturdays are in a
season, so I can just do a formula based on the return result from the
total visitation per day.

The average formula will work if the cells without values are blanks, rather than filled with
zeroes.

HTH,
Bernie
MS Excel MVP


<(E-Mail Removed)> wrote in message
news:00bf57f7-a4e3-4d51-8bb5-(E-Mail Removed)...
> Thank you, that works!! The averages formula doesn't, but I think I
> can work around that another way. (I know how many Saturdays are in a
> season, so I can just do a formula based on the return result from the
> total visitation per day.