Need to select rows to average based on a value in a different column

[Randy K]

Below is an example of my spreadsheet. I'd like to find the average
number of users for each "Day of the week" hour combination but I'm
not even sure where to start.


Date Hour DoW # Users
10/27/2003 10 Mon 11
10/27/2003 11 Mon 11
10/27/2003 13 Mon 10
10/27/2003 14 Mon 11
10/27/2003 15 Mon 9
10/27/2003 16 Mon 9
10/28/2003 8 Tue 5
10/28/2003 9 Tue 6
10/28/2003 10 Tue 4
10/28/2003 11 Tue 8
10/28/2003 13 Tue 7
10/28/2003 14 Tue 7
10/28/2003 15 Tue 7
10/28/2003 16 Tue 10
10/29/2003 8 Wed 7
10/29/2003 9 Wed 10
10/29/2003 10 Wed 11
10/29/2003 11 Wed 10
10/29/2003 13 Wed 10
10/29/2003 14 Wed 9
10/29/2003 15 Wed 11
10/29/2003 16 Wed 10

 

[Pete_UK]

I have put your example data in cells A1 to D23, using the top row for
headings. In E2 to E4 I have entered "Mon", "Tue" and "Wed" (without
the quotes), and in F2 I have entered this formula:

=SUMIF(C$2:C$23,E2,D$2$23)/COUNTIF(C$2:C$23,E2)

and copied down to F4. You don't have an entry for 12 noon - I've
assumed that is lunchbreak, so each entry in the table is equivalent to
1 hour.

Hope this helps.

Pete