Subtotal weekly data

[Tom Malinski]

I'm a cyclist and I'm tracking ride data. I have many columns of data which I
have defined and named using =Offset and CountA functions. Utilizing my
named ranges such as "Date" and "Distance" Here is my challenge, I need a
formula that will calculate the greatest distance traveled in a week. I only
track actual ride days, and my weeks run Sun thru Sat

For Example:
Day Date Distance
Sun 8/29/04 55.0
Wed 9/1/04 25.0
Fri 9/3/04 28.0
Sun 9/5/04 70.0
Tue 9/7/04 33.0
Fri 9/10/04 28.0
Sun 9/12/04 104.0
Thur 9/16/04 21.0

In this example the formula would look at all the data and calculate that
Sun 9/5/04 thru Friday 9/10/04 has the greatest distance traveled of 130
miles. Thats all I'm looking for!!!

Probably simple, but I'm stumped
Thanks for your help
Tom..

 

[duane]

I will adapt another macro written for stock prices - I have not teste
it though for this application

Sub Macro1()
'
' Calculate weekly mileage from daily data
'
' Define right data set size!
'
' 10 days data allowed here
'
Dim day(10) As Integer
Dim Dat(10) As Date
Dim daymiles(10) As Double
'
' 10 weeks data allowed here
'
Dim weekdate(10) As Date
Dim weekmiles(10) As Double

'
'read in all daily data assume date in column 2 and miles in column
starting in row 7 - this example is for 6 days of daily data
'
For i = 1 To 6
Dat(i) = Cells(i + 6, 2).Value
day(i) = WorksheetFunction.WeekDay(Cells(i + 6, 2), 1)
daymiles(i) = Cells(i + 6, 3).Value
Next i
'
j = 0
For i = 1 To 6
If i = 1 Then GoTo firstday
If day(i) > day(i - 1) Then GoTo sameweek Else GoTo newweek
firstday:
newweek:
'
' if day number < previous day number then establish new week
'
If j = 10 Then GoTo done
j = j + 1
weekdate(j) = Dat(i)
weekmiles(j) = daymiles(i)
GoTo nexti
sameweek:
'
' if day number > previous day number (therefore same week)
' recalculate weekly miles
'
weekmiles(j) = weekmiles(j) + daymiles(i)
nexti:
Next i
done:
'
'this figures out how many weeks to summarize and plunks in columns
and 8
'
lastj = j
For j = 1 To lastj
Cells(j + 7, 7) = weekdate(j)
Cells(j + 7, 8) = weekmiles(j)
Next j
End Su

 

[Tom Malinski]

Wow duane, thanks for your input, I was hoping I could do this with simple
formulas, but I appreciate the time you took to help me. I'll try to adapt
this to see if it will do it for me.

Tom..