Find a value for a particular weekday (Friday) then calculateinformation backwards from that date to the next date

I have a list of values in a range sorted by date and I want to calculate the minimum and maximum values in separate columns in that list for the previous week (5 days) and then calculate the same for the week that precedes that until the list and so on until the end of list. The day of the week from which I wish to calculate is Friday. There will be times due to holidays that the previous week may not have five days. I am using EXCEL 2010 and would like to have a VBA routine to perform this task. I am a novice VBA programmer. Can anyone help.
Who is Participating?
rspahitzConnect With a Mentor Commented:
Well, here's the code I came up with, which you can stick into the code window for the sheet.  If you're not familiar with this, I'll give instructions in the next message.
Also note that you should run the procedure called CalculateWeeklyMinAndMax, and you may want to add a button to your form to run it.

Private miNextSummaryColumn As Integer

Sub CalculateWeeklyMinAndMax()
    Dim iRow As Integer
    Dim iColumn As Integer
    Dim dtCalcDateCurrent As Date
    Dim dtCalcDateNext As Date
    Dim strCellValue As String
    Dim dblMin As Double
    Dim dblMax As Double
    miNextSummaryColumn = 10
    iRow = 2 ' skip header
    dtCalcDateCurrent = getCalcDate(iRow)
    dblMin = 99999
    dblMax = 0
    Do Until Cells(iRow, 1).Value = ""
        ' Find min and max for the row
        For iColumn = 2 To 7
            If iColumn <> 6 Then ' skip for "Volume" data
                strCellValue = Cells(iRow, iColumn).Value
                If Val(strCellValue) < dblMin Then
                    dblMin = Val(strCellValue)
                End If
                If Val(strCellValue) > dblMax Then
                    dblMax = Val(strCellValue)
                End If
            End If
        Next iColumn
        iRow = iRow + 1
        dtCalcDateNext = getCalcDate(iRow)
        If dtCalcDateNext <> dtCalcDateCurrent Then
            ' New date so show result in summary sheet
            ShowResults dtCalcDateCurrent, dblMin, dblMax
            dtCalcDateCurrent = dtCalcDateNext
            dblMin = 99999
            dblMax = 0
        End If
End Sub

Private Function getCalcDate(RowNumber As Integer)
    ' assumes that column 7 has this formula: =IF(WEEKDAY(A2) <6,A2+(6-WEEKDAY(A2)), A2+7-MOD(WEEKDAY(A2),7)-1)
    ' if not, then check the date in the first column of the row and figure out the next Friday
    getCalcDate = Cells(RowNumber, 8).Value
End Function

Private Sub ShowResults(CalcDate As Date, MinValue As Double, MaxValue As Double)
    Sheets("Summary").Cells(1, miNextSummaryColumn).Value = CalcDate
    Sheets("Summary").Cells(2, miNextSummaryColumn).Value = MinValue
    Sheets("Summary").Cells(3, miNextSummaryColumn).Value = MaxValue
    miNextSummaryColumn = miNextSummaryColumn + 1
End Sub

Open in new window

azhutch1Author Commented:
Any help I can get will be greatly appreciated!
I'm not quite sure of the question, but you can use the WEEKDAY function to check which day of the week the date is on.  Friday will be 5, Saturday will be 6, sunday will be 7, Monday will be 1, etc.

Based on that, you should be able to determine an offset from Friday to, for example, find the previous and next friday:


that should force the date in A1 to become a friday in the cell with the formula (like B1)

From there, you can subtract 7 for the previous Friday or add 7 for the next friday.

(And I'll need to check on the Sunday may need to test for that separately or it may be a week off.)
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

azhutch1Author Commented:
I have the Weekday formula already. I use this to generate the closest Friday from today lets say. I then import a list of daily information for the previous five weeks from that Friday's date in a table, sorted from oldest date to newest. What I want is to programatically calculate minimum and maximum values (which are in adjacent columns for each date for that particular week only and then do so for the remaining previous five weeks. I dont know if I am describing the issue well or not. Thank you though for the suggested formula.
So there are a lot of values to the right of each date, and you want min and max (there are the min and max functions for that.)

What I read so far is a column of dates, sorted.  You already know the closest (or desired) Friday.

Where do the min and max parts come in?  Is there data somewhere else on the sheet?

Is this an example?

1/1 12 17 23 14
1/2 18 25 16
1/5 22 17 8
1/8 42 1 16 23 18

And (assuming that 1/1-1/5 are all part of the same week), you want to show min=8 and max=25?  Spanning multiple rows?
If so, I'll send you some VBA for that.
azhutch1Author Commented:
The list with the data to the right of the date is exactly the problem. The issue that there may or may not be exactly five dates between one Friday and the next previous Friday compounds the request. Thank you for your understanding.
so did I describe it right? is there a varaible number of columns of values, or fixed?
and I guess you can have any number of rows for a given week?

and how do you want to show the min and max? In a cell on the same sheet? save in a separate file?

The general strategy I would use is:
* Start at row 1 and repeat until first date cell in a row is blank
* For each date in a given week:
*   Check from next column until empty cell found
*     If value is less than previous min, save it; if more than prev max, save it
* When new week found, or end of list, put the min and the max for the previous block into a designated location (e.g. column B, next to the last date in the group.)

Does that sounds right?
azhutch1Author Commented:
You did describe it correctly. I would like to return the result max and min for each week on a sheet named "Summary"  in separate rows in the column headed by the date in question Row 9 for the Max and Row 11 for the Min.
There are seven different columns on the sheet with the data. These are labeled: "Date"  "Open" "High" "Low" "Close", "Volume" and "Adj Close". Each and every row has data corresponding to the respective columns. The maximum high and the minimum low would be calculated  saved and placed separately on the Summary sheet. Thank you!
Just a thought while I work on it...

Will anything ever be higher than the "High" column or lower than the "Low" column?  And should Volume be skipped?

It seems that if you added one more column to calculate the Friday for the date, you could probably throw the data into a pivot table to get all of the information, summarized on the same sheet or a Summary sheet, just about the way you want it.  Then you only need to change the data source when new data arrives.
azhutch1Author Commented:
I was going to calculate the average daily volume for each respective period, but thought that asking would be a little too presumptuous. I am not really that familiar with pivot tables, but if you think it might work without too much maintenance, I would be willing to try it. Thank you
Oh, sorry...change this subroutine to point to the correct rows for the high and low:

Private Sub ShowResults(CalcDate As Date, MinValue As Double, MaxValue As Double)
   ' Sheets("Summary").Cells(1, miNextSummaryColumn).Value = CalcDate
    Sheets("Summary").Cells(11, miNextSummaryColumn).Value = MinValue
    Sheets("Summary").Cells(9, miNextSummaryColumn).Value = MaxValue
    miNextSummaryColumn = miNextSummaryColumn + 1
End Sub
As for the pivot tables, it's all a matter of selecting all of the pieces for the pivot table box.
If you want to try, you'll probably want one for each Min and Max

* menu tab Insert | Tables group | Pivot Table
* Select the data block (probably something like A1: G999
* Select a destination where the pivot table box will appear (usually to the right of all data or on a different sheet
* Click OK and a set of boxes will appear on the sheet, along with a set of fields to choose to drop into the boxes.
* Drag "Date" from the list and drop along the top
* Drag "High" and drop into the Values section
* Change to show max by going to the top-left corner of the box (Sum of High) and right-click then pick  "Summarize Values By" then select Max.
* Finally, go to the Date dropdown (near the top-left) and right-click and select Group
* Change the grouping to Days then the number to 7.

You should now have your Max summary.

Do the same for Min.
when done, you should be able to copy these and paste them into your summary page if all the date groups match.

If the data changes, right-click on the pivot table and select Refresh.

To delete the pivot table, select a range of cells that surrounds the entire table and delete the cells.
azhutch1Author Commented:
Wow. Thank you very much! Great work and I will definitely work on the pivot tables as this might be a needed addition to the workbook.
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.