Help with Offset function

Posted by David on December 06, 2001 5:55 AM

I am working with a small data set consisting of Date, Total1 and Total2 (web
server hits for the past years) information is added daily. I need to
automatically grab the last week, month, 3 months of data from the Total1 and
Total2 columns and come up with a daily averages with out having to
individually select the cells. I have been using the following formula

=OFFSET(C$117,COUNTA(C$117:C$5000)-($B97),0)

Where C117 is the start of the data and C5000 is the random end (I am up to
C1450)
For a week worth of data B97 will equal 1 to 7 for a month 1 to 30.

There are several problems with this approach. Although it is semi
automated I have to have an entry for each "day" that I want to grab which I
then average is a separate cell.

Is there a way to specify a range of cells to grab?

Here is what the data sheet looks like.

Date Total1 Total2
1-Nov-00 1,865,476 1,188,997
2-Nov-00 1,860,732 1,178,239
3-Nov-00 1,665,276 1,089,830
4-Nov-00 1,882,132 1,259,914
5-Nov-00 2,181,684 1,435,451

Help would be much appreciated

-- David

Posted by Rick E on December 06, 2001 7:48 AM

Here is the marco program...

Cut and paste the macro below into a module and give it a try. It will ask you to enter the date you want to end with to get the averages. (If you want 7 days ending at 11/10/00, enter that date and it will give you the 7 days averages for column B & C from 11/4/00 to 11/10/00, then the 30 day and then the 90 day averages ending on that date. Good Luck, Rick E.

Sub GetAverage()
' 12/6/01 by Rick E.
Range("A6").Select ' Must have atleast 7 days of data
k = InputBox("Enter the date - mm/dd/yy", "Get Date")
k = Format(k, "mm/d/yy")
i = 0
Do Until i = 1
ActiveCell.Offset(1, 0).Select
If k = Format(ActiveCell.Value, "mm/d/yy") Then
i = 1
End If
If ActiveCell.Value = "" Then
MsgBox "Date not found."
Exit Sub
End If
Loop
j = 0
k = ActiveCell.Row - 6
For i = k To k + 6
str1 = "B" & i
j = j + Range(str1)
Next i
str1 = "Seven day average for column B is " & Format(j / 7, "#,###")
i = MsgBox(str1, vbInformation + vbOKOnly, "Averages")
j = 0
For i = k To k + 6
str1 = "C" & i
j = j + Range(str1)
Next i
str1 = "Seven day average for column C is " & Format(j / 7, "#,###")
i = MsgBox(str1, vbInformation + vbOKOnly, "Averages")
' Months now
k = ActiveCell.Row - 29
If k &LT; 1 Then
str1 = "Not enought data for 30 day averages."
i = MsgBox(str1, vbCritical + vbOKOnly, "Need More Data")
Exit Sub
End If
For i = k To k + 29
str1 = "B" & i
j = j + Range(str1)
Next i
str1 = "Thirty day average for column B is " & Format(j / 30, "#,###")
i = MsgBox(str1, vbInformation + vbOKOnly, "Averages")
j = 0
For i = k To k + 29
str1 = "C" & i
j = j + Range(str1)
Next i
str1 = "Thirty day average for column C is " & Format(j / 30, "#,###")
i = MsgBox(str1, vbInformation + vbOKOnly, "Averages")
' 3 months now
k = ActiveCell.Row - 89
If k &LT; 1 Then
str1 = "Not enought data for 90 day averages."
i = MsgBox(str1, vbCritical + vbOKOnly, "Need More Data")
Exit Sub
End If
For i = k To k + 89
str1 = "B" & i
j = j + Range(str1)
Next i
str1 = "Ninty day average for column B is " & Format(j / 90, "#,###")
i = MsgBox(str1, vbInformation + vbOKOnly, "Averages")
j = 0
For i = k To k + 89
str1 = "C" & i
j = j + Range(str1)
Next i
str1 = "Ninty day average for column C is " & Format(j / 90, "#,###")
i = MsgBox(str1, vbInformation + vbOKOnly, "Averages")
End Sub

Posted by David on December 06, 2001 6:33 PM

Re: Here is the marco program...

Thanks - I will give it a try.