Using AVERAGEIFS to calculate average rating for programs-Reposted (wasunclear)

[Shay Z]

Here is an example for the Rating database:

Date Start Time End Time Channel 1 Channel 2 Channel 3 Channel 4 Channel 5 Channel 6 Channel 7 Channel 8 Channel 9 Channel 10

1/2/2010 06:00 06:15 0 0 0 0 0 0 0 0 0 0

1/2/2010 06:15 06:30 0 0 0 0 0 0 0 0 0 0

1/2/2010 06:30 06:45 0.1 0.1 0.1 0.1 0.1 0 0.1 0.2 0.2 0.1

1/2/2010 06:45 07:00 0.2 0.2 0.1 0.2 0.2 0.1 0.2 0.1 0.1 0.1

1/2/2010 07:00 07:15 0.2 0.1 0.2 0.2 0.2 0.2 0 0 0.1 0.1

1/2/2010 07:15 07:30 2.5 0.1 0.2 0.2 0.2 0.2 0 0 0.1 0.3

1/2/2010 07:30 07:45 2 0.1 0.2 0.2 0.2 0.2 0 0 0.1 0.2

1/2/2010 07:45 08:00 3 4 0.2 4 0.2 0.2 4 0.2 0.2 0.2

1/2/2010 08:00 08:15 2 0.1 0.1 0.1 0.2 0.2 0.2 0.2 0.2 0.2

1/2/2010 08:15 08:30 6 2 7 2 0.1 3 0.1 0.1 0.1 0.1

1/2/2010 08:30 08:45 5 1 5 3 3 3 4 5 4 1

1/2/2010 08:45 09:00 4 3 5 3 5 2 4 2 3 1

1/2/2010 09:00 09:15 4 2 6 3 5 4 3 7 2 2



and an example of the expected results:

Date Channel Program Name Start Time End Time Average Rating

1/2/2010 channel 1 aa 07:50 08:50

1/2/2010 channel 1 bb 06:30 08:30

1/2/2010 channel 1 cc 08:30 09:10

1/2/2010 channel 2 dd 06:00 09:00

1/2/2010 channel 3 ee 07:05 08:15

1/2/2010 channel 10 ff 07:15 08:03

1/2/2010 channel 6 gg 09:00 09:06

1/2/2010 channel 7 hh 07:52 08:32


This is only an example of course. I need to calculate the average rating for each program. dont refer to the mistake of 3 different programs from channel 1 in parallel...

thanks!!!
Shay


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# And The Little Iterator That Could
http://www.eggheadcafe.com/tutorial...32-0ae26adaa533/c-and-the-little-iterato.aspx