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.

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 value...you may need to test for that separately or it may be a week off.)

0

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.

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

0

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?

0

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!

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.

0

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

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 IntegerSub 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 LoopEnd SubPrivate 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).ValueEnd FunctionPrivate 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 + 1End 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.

0

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.

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.

0

Featured Post

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.