Calculating average rating for programs from different channels

[shay z]

Hi everyone!

I know I need to use the AVERAGEIFS function but something is missing. as you can see below, in the second table, each time the average needs to be calculated on another channel.

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 9 bb 06:30 08:30
1/2/2010 channel 8 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.



thanks for the help!!!

Shay

[DonkeyOte]

Shay, I think you will need to provide more info. - it's difficult to determine how you arrived at your result table from your database table (esp. given no program values exist in the database records)

I would suggest you post a sample file (use the paperclip icon in the reply window - if you can't see it click Go Advanced)

[shay z]

Hi!
I attached a sample file.
I have to explain- the only field that have to be calculated is "average rating".
You can refer to the data in the second (smaller) table as actual broadcasts- this is also data and not calculated fields.
hope you will understand...
shay

[DonkeyOte]

could you also provide the expected results ? (calculated manually of course)

Without expected results it is hard for anyone to validate the logic to be used.

[shay z]

Hi again,
attached is a new sample file in addition to your reply.

thank u for the quick replies.
as u see I know how to calculate with AVERAGEIFS. The Problem is that I have to define the Average array for each different channel. I hope we can find one formula that I could drag down.

shay

[DonkeyOte]

OK - I see you're interested only in whole periods - I had presumed you would want partial periods or where partial you would apportion based on 15 min %.

Using your AVERAGEIFS, you can make a single formula using INDEX, eg:

=IFERROR(AVERAGEIFS(INDEX($D$1:$M$14,0,ROWS(F$18:F18)),$A$1:$A$14,A18,$B$1:$B$14,">="&D18,$C$1:$C$14,"<="&E18),"no avg.")
copied down

the IFERROR is to account for instances where no whole 15 min period instance exists, eg: channel 6.

(if you have start/end times crossing midnight then things will become quite complex I suspect)

[shay z]

thanks! it was very helpful!

shay