loops within a loop to sum blocks of data

[dgali001]

Hi all,

I'm working with large amounts of data coming from TV Rating Reports. I'm trying to come up with a Macro to automate some of the tedious work involved. I've come up with the following code to get the average rating for the movie in question using the Rating's time block. But so far I can only get it to work for one day. I need help looping to the other days.

[vba]
Sub AverageRating()
Dim i As Integer
Dim t As Integer
t = 1
Do
t = t + 1
MovieStartTime = Worksheets("calculations").Range("C" & t)
MovieEndTime = Worksheets("calculations").Range("D" & t)
StartMovieDate = Worksheets("calculations").Range("A" & t)
SumRating = 0
CountAvg = 0
On Error Resume Next
For i = 2 To 25
StartNielsonDate = Worksheets("calculations").Range("G" & i)
NielsonStartTime = Worksheets("calculations").Range("H" & i)
NielsonEndTime = Worksheets("calculations").Range("I" & i)
Rating = Worksheets("calculations").Range("J" & i)
If MovieStartTime < NielsonStartTime Then
If NielsonStartTime < MovieEndTime Then
Var1 = True
Else
Var1 = False
End If
ElseIf MovieStartTime >= NielsonStartTime And NielsonEndTime >= MovieStartTime And NielsonEndTime <= MovieEndTime Then
Var1 = True
Else
Var1 = False
End If
If Var1 = True Then
Var1 = 1
SumRating = (Rating + SumRating)
ElseIf Var1 = False Then
Var1 = 0
End If
CountAvg = Var1 + CountAvg
Next i
AvgRating = SumRating / CountAvg
Worksheets("calculations").Range("F" & t).Value = AvgRating
Loop Until Cells(t + 1, 1) <> StartMovieDate
End Sub
[/vba]

I'm also attaching the Excel file to better understand what I'm trying to do. Column E is the manual calculations I'm doing (see formulas to understrand the logic) and column F is what my code is getting. The code works fine until it needs to analyze a movie going into the next day. But aside from that it works but needs to be looped somehow to get the averages for the other days!!

Any ideas??

[mdmackillop]

I've added some helper columns and adjusted dates as these didn't match.

[dgali001]

Mdmackillop thanks so much!!!!!!

I thought this was only possible by creating a macro but I was wrong. I'm still trying to understand the logic behind those formulas..... but while I was checking the accuracy I manually checked the ratings for all the data and compared them with your data. However after rating a movie that crosses into the following day, the following three ratings are incorrect. And this pattern repeats (except for the last entries which were four).

*Edit: I just noticed that the errors are occurring because the formula uses the ratings block for the incorrect date/time block. For example cell K27 is getting the ratings from P20:P22 instead of getting them from P44:P46. See the matching colored cells in columns K and P to see where the averages are being pulled from.

Attached is the file with the maroon colored cells in columns A: D showing the time blocks with inconsistencies.

Is there a quick fix to this?

Thanks again for the help!

[dgali001]

*Edit: Sorry but your formulas were right all along, it was me! I didn't adjust the dates in column A!!

Now this will take me 5 minutes to organize the data as opposed to the painstaking 3 hours!!

Thanks again for the help!!!!!!!