Averageifs- calculate TV programs rating from various channels

[Shay Z]

Hi all,
I'm trying to use averageifs on the following situation:
I have 3 criteria columns- date, start time of a program and finish time of a program (no problem with this of course) and 10 columns of rating data to 10 different TV channels in any time of the day.

In anoter sheet I've got a table whice includes records like:
date, TV channel, program name, start time, finish time.
I need to calculate the average rating for every program and my problam is- how can I produce a formula in which the excel will "understand" which channel rating data it needs to calculate the answer from...
hope you'll understand...
10x
shay


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF GridView Sample To Insert, Update, and Delete Records
http://www.eggheadcafe.com/tutorial...a-c9a46fd3aeb2/wpf-gridview-sample-to-in.aspx

 

[Bob Phillips]

Nope.

Give an example of the data and expected results.

 

[Shay Z]

OK, I'll try to be more specific.
Here is an example for the 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

thanks!
shay



Bob Phillips wrote:

Nope.Give an example of the data and expected results.--HTHBob
28-Apr-10

Nope

Give an example of the data and expected results

-

HT

Bob

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Effective C#, Second Edition [Addison Wesley]
http://www.eggheadcafe.com/tutorial...7af-c38852b3b455/book-review-effective-c.aspx

 

[Shay Z]

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



Shay Z wrote:

Averageifs- an example of the problem
28-Apr-10

OK, I'll try to be more specific.
Here is an example for the 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

thanks!
shay

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Reflection Effect
http://www.eggheadcafe.com/tutorial...-beab-49bd76e20b9b/wpf-reflection-effect.aspx