Wishing for AVERAGEIF

[Kerry Meline]

Hi! I'm looking for a shortcut to manage a big set of
data. Here's what I've got:

A B C D E F S
Store# Week DayofWeek 8am 9am 10am ... 9pm
1 Feb1 Sunday 1 4 8 3
1 Feb1 Monday 0 3 5 2
....
1 Feb4 Sunday 2 2 9 2
1 Feb4 Monday 1 3 6 3
....
200 Feb1 Friday 3 8 9 2
200 Feb1 Saturday 5 8 20 3
....
200 Feb4 Friday 2 7 5 4
200 Feb4 Saturday 4 5 12 5

The data is sales transactions by hour.

I need to create graphs for each store # which show
average sales by hour for each day of the week (7 lines, 1
graph per store). Getting to the averages is the problem.

Right now, my best solution is to just sort by store# and
day of week to get the data I want to average altogether
and then create average calcs down the weeks over and over
and over for each store by day. I'm filtering and
dragging to try and make it efficient as possible, but at
this pace my son might inherit the project.

It feels like I should be able to conditionally average
based on the data in columns Store# and Day of Week.

Any ideas?? THANKS in advance!

 

[Myrna Larson]

Sounds like a job for a pivot table.

 

[Kerry]

THANKS Myrna/Bill! The pivot table is great! Did in 15
minutes what would've taken hours. The conditional
average is excellent; I'll run into many ways to use the
example.

Cheers,

kerry

 

[Ken Wright]

For a neat intro to Pivot Tables, Debra Dalgleish has the following site:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm