Results 1 to 15 of 32
Thread: Match/Index/VLookup, etc. (2000)

20031124, 10:48 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Match/Index/VLookup, etc. (2000)
I have a spreadsheet that lists averages of daily prices for a commodity over a calendar year basis for 30 years. Inasmuch as these averages include a number of leap years, the spreadsheet includes February 29th.
I would like to set up a spreadsheet that will compare, say, daily prices for 2003 (a nonleap year) to this historical spreadsheet. Specifically, column A would list the dates (in MM/DD/YYYY format) for the year I am going to compare the historical spreadsheet to, column B the then current year prices of the commodity (on a daily basis), and column C the historical, 30year average of the prices on that particular date.
I am trying to figure out how to get the historical prices from a specific date to compare to a price from the current year in a nonleap year date. That is to say, even though the historical file has an average for February 29th, to compare 2003 prices with this historical data, I would need to "ignore" the historical average for February 29th. On the other hand, in a leapyear, I would need to pull the value for February 29th to compare. The only way I could think it would work would be to use some sort of combination of the match/index/vlookup functions, but I am not astute enough to figure the combination out.
Any thoughts?
Thanks,
Jeff

20031124, 12:45 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
I will assume your data is in Sheet1 with a header in row 1. So your data is in the range Sheet1!A2:B15000,A is date, B is Price (change as apropriate).
In another sheet:
Cell A1 holds the year of interest
A2, B2, C2 are the headers (Date, Price, Average price on this date)
A3 has formula, for Jan 1 of the year of interest:
<pre>=DATE(A1,1,1)</pre>
A4 has formula:
<pre>=A3+1</pre>
Copy A4 to A5:A367 to give the remaining dates. In case of leap year A368 is:
<pre>=IF(DAY(A367)=31,"",A367+1)</pre>
In B3 enter the formula:
<pre>=VLOOKUP(A3,Sheet1!$A$2:$B$15000,2,FALSE)</pre>
which looks up the price given the date
Copy B3 to B4:B367 to give the remaining dates. In case of leap year B368 is:
<pre>=IF(A368="","",VLOOKUP(A368,Sheet1!$A$2:$B$15 000,2,FALSE))</pre>
In C3 enter the ARRAY formula (confirm with ctrlshift enter)
=AVERAGE(IF((MONTH(Sheet1!$A$2:$A$15000)=MONTH(A3) )*(DAY(Sheet1!$A$2:$A$15000)=DAY(A3)),Sheet1!$B$2: $B$15000))
which looks up the avg price given the date
Copy C3 to C4:C367 to give the remaining dates. In case of leap year C368 is:
=IF(A368="","",AVERAGE(IF((MONTH(Sheet1!$A$2:$A$15 000)=MONTH(A368))*(DAY(Sheet1!$A$2:$A$15000)=DAY(A 368)),Sheet1!$B$2:$B$15000)))
Be aware the spreadsheet will be "sluggish" and calc slowly, since you have 365 lookups in 15,000 items and 366 arrays cals thru multiple columns of 15,000 pieces of data.
Change A1 (could be from pulldown if desired, or use a spinner).
You might consider creating a macro tochoose date and generate the sheet, it might be faster.
Steve

20031125, 13:32 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Yes, it works fine, but wow is it ever slow...

20031125, 14:26 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
I did warn you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
<hr>Be aware the spreadsheet will be "sluggish" and calc slowly, since you have 365 lookups in 15,000 items and 366 arrays cals thru multiple columns of 15,000 pieces of data<hr>
A macro might be quicker and need only run when you change the year.
Steve

20031126, 13:07 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Did I hear you volunteer? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

20031126, 19:17 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
I assumed your data (cols A&B is in a sheet called "Data". A is the dates and B is the "price". Row 1 has header and the data starts in row 2 and goes to whatever (the macro figures it out. (change as appropriate)
You have a "summary sheet" named "Summary.
In A1 of the summary sheet is the Year of interest.
In row 2 is headers: Date, Lookup Value (for that date), Average (all years of the "day"), number of points for average
Rows 3 367 (or 368 if year is leap year) is the summary data
(change as appropriate)
Add this macro the the summary sheet object in VB:
<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
Application.EnableEvents = False
UpdateSheet
Application.EnableEvents = True
End If
End Sub</pre>
When you change the value in A1 the UpdateSheet macro is called
These macros (UpdateSheet, IsLeapYear function) go in a module:
<pre>Option Explicit
Sub UpdateSheet()
Dim x As Long
Dim y As Long
Dim vDataArray
Dim vSummaryArray()
Dim iYearSelected As Integer
Dim wksSummary As Worksheet
Dim wksData As Worksheet
Dim dblTempSum As Double
Dim iLastDateRow As Integer
Dim bLeapYearSelected As Boolean
Dim bLeapYearData As Boolean
Dim bIncludeMe As Boolean
Set wksData = Worksheets("Data")
Set wksSummary = Worksheets("Summary")
iYearSelected = wksSummary.Range("a1").Value
bLeapYearSelected = IsLeapYear(iYearSelected)
iLastDateRow = 367 + IIf(bLeapYearSelected, 1, 0)
ReDim vSummaryArray(2 To iLastDateRow, 1 To 4)
vSummaryArray(2, 1) = "Date"
vSummaryArray(2, 2) = "Value"
vSummaryArray(2, 3) = "Average"
vSummaryArray(2, 4) = "Num Points"
vSummaryArray(3, 1) = DateSerial(iYearSelected, 1, 1)
For x = 4 To iLastDateRow
vSummaryArray(x, 1) = vSummaryArray(x  1, 1) + 1
vSummaryArray(x, 2) = 0
vSummaryArray(x, 3) = 0
Next
With wksData
vDataArray = .Range(.Range("A2"), .Range("B65536").End(xlUp))
End With
For x = 1 To UBound(vDataArray)
bIncludeMe = True
y = vDataArray(x, 1)
bLeapYearData = IsLeapYear(Year(y))
y = y + 1  DateSerial(Year(y), 1, 1)
If bLeapYearData <> bLeapYearSelected And y > 59 Then
If bLeapYearData Then
If y = 60 Then bIncludeMe = False
y = y  1
Else
y = y + 1
End If
End If
y = y + 2
If Not IsEmpty(vDataArray(x, 2)) And bIncludeMe Then
If Year(vDataArray(x, 1)) = iYearSelected Then _
vSummaryArray(y, 2) = vDataArray(x, 2)
If IsNumeric(vDataArray(x, 2)) Then
dblTempSum = vSummaryArray(y, 3) * vSummaryArray(y, 4)
dblTempSum = dblTempSum + vDataArray(x, 2)
vSummaryArray(y, 4) = vSummaryArray(y, 4) + 1
vSummaryArray(y, 3) = dblTempSum / vSummaryArray(y, 4)
End If
End If
Next
With wksSummary.Range("A2")
.Resize(65535, 4).ClearContents
.Resize(iLastDateRow  1, 4).Value = vSummaryArray
End With
End Sub
Function IsLeapYear(iYear As Integer) As Boolean
IsLeapYear = Month(DateSerial(iYear, 2, 29)) = 2
End Function</pre>
This routine is much much faster than the multiarray formulas. I loaded the data into vb arrays and did not work with the spreadsheet data to speed up looking through all the data. I tested with over 150 years of "pretend data" and It calc's in a between 1 and 2 secs.
Steve

20031202, 12:01 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Steve,
I can't seem to get it to workwanna try?
Thanks,
Jeff

20031202, 13:15 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
Could you be more specific? What doesn't work?
If my assumptions on your datasetup are not "valid" you might have to make changes to the code.
Steve

20031202, 13:43 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Here's an abbreviated version of the file.

20031202, 14:41 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
The worksheet change macro goes in the Summary sheet object not in a module.
In VB (altF11)
In Project explorer (ctrlR) (left pane usually)
"Open" (if not opened) Microsoft excel objects "folder"
dblclick on "summary" sheet
In Macro pane (usually right pane)
add the worksheet change macro
Steve

20031203, 10:12 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Steveworks great with one quirk. If I change A1 to a year not included in the data (i. e., 1980 in the file I sent you), I'm not sure what the data returns!

20031203, 11:13 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
If you pick a year that is not in the data,
A) the "date column" is still correct
[img]/forums/images/smilies/cool.gif[/img] The value should give a zero since I "seeded it with a zero"
C) Average for the date should give the correct average
D) Number of points should be correct.
The only one that is "wrong" is Column B, giving zero instead of something like #NA
What do you want to Display?
Modify the code staring here:
<pre> vSummaryArray(3, 1) = DateSerial(iYearSelected, 1, 1)
'This is new (forgot to initialize the first data row)
vSummaryArray(3, 2) = CVErr(xlErrNA)
vSummaryArray(3, 3) = 0
vSummaryArray(3, 4) = 0
'This is the same with one modification
For x = 4 To iLastDateRow
vSummaryArray(x, 1) = vSummaryArray(x  1, 1) + 1
vSummaryArray(x, 2) = CVErr(xlErrNA) 'This is modified
vSummaryArray(x, 3) = 0
vSummaryArray(x, 4) = 0
Next</pre>
If you modify you will get #n/a errors if you pick a nonexistent year (or you have missing data). You could choose a different error, a null string, or if you prefer do not initilize that column (eliminate those 2 lines) and if there is no data it will remain blank.
Columns C and D should be the same no matter what date you enter, it just reads the average for that date in all the history.
Steve

20040114, 13:56 #13
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Steve,
I've made the changes you suggested. However, regardless of what year I plug in to compare, the data points always return 40 or 39. I thought, for example, if I inserted 1998, and I had data going back to 1996, the data points (i. e., the years that were being averaged to compare to 1998) would equal 2, inclusive of 1998, if there was data for the respective date. Am I correct?
You might want to email me your address, as I have about 30 years of historical price data.
Thanks,
Jeff

20040114, 15:27 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Match/Index/VLookup, etc. (2000)
You said you had 30 years of data and I figured you wanted the average of all the data. You must have 40 years of data, not 30.
This will limit it (at most) only the previous 30 years of the year selected. (change the variable iMaxYears if you want different)
Steve
<pre>Sub UpdateSheet()
Dim x As Long
Dim y As Long
Dim vDataArray
Dim vSummaryArray()
Dim iYearSelected As Integer
Dim wksSummary As Worksheet
Dim wksData As Worksheet
Dim dblTempSum As Double
Dim iLastDateRow As Integer
Dim bLeapYearSelected As Boolean
Dim bLeapYearData As Boolean
Dim bIncludeMe As Boolean
Dim iMaxYears As Integer
Dim iYear As Integer
iMaxYears = 30
Set wksData = Worksheets("Data")
Set wksSummary = Worksheets("Summary")
iYearSelected = wksSummary.Range("a1").Value
bLeapYearSelected = IsLeapYear(iYearSelected)
iLastDateRow = 367 + IIf(bLeapYearSelected, 1, 0)
ReDim vSummaryArray(2 To iLastDateRow, 1 To 4)
vSummaryArray(2, 1) = "Date"
vSummaryArray(2, 2) = "Value"
vSummaryArray(2, 3) = "Average"
vSummaryArray(2, 4) = "Num Points"
vSummaryArray(3, 1) = DateSerial(iYearSelected, 1, 1)
vSummaryArray(3, 2) = CVErr(xlErrNA)
vSummaryArray(3, 3) = 0
vSummaryArray(3, 4) = 0
For x = 4 To iLastDateRow
vSummaryArray(x, 1) = vSummaryArray(x  1, 1) + 1
vSummaryArray(x, 2) = CVErr(xlErrNA)
vSummaryArray(x, 3) = 0
vSummaryArray(x, 4) = 0
Next
With wksData
vDataArray = .Range(.Range("A2"), .Range("B65536").End(xlUp))
End With
For x = 1 To UBound(vDataArray)
bIncludeMe = True
y = vDataArray(x, 1)
bLeapYearData = IsLeapYear(Year(y))
y = y + 1  DateSerial(Year(y), 1, 1)
If bLeapYearData <> bLeapYearSelected And y > 59 Then
If bLeapYearData Then
If y = 60 Then bIncludeMe = False
y = y  1
Else
y = y + 1
End If
End If
y = y + 2
If Not IsEmpty(vDataArray(x, 2)) And bIncludeMe Then
If Year(vDataArray(x, 1)) = iYearSelected Then _
vSummaryArray(y, 2) = vDataArray(x, 2)
If IsNumeric(vDataArray(x, 2)) Then
iYear = iMaxYears + IIf(vSummaryArray(y, 1) < Date, 0, 1)
If Year(vDataArray(x, 1)) > _
Year(vSummaryArray(y, 1))  iYear And _
Year(vDataArray(x, 1)) <= iYearSelected Then
dblTempSum = vSummaryArray(y, 3) * vSummaryArray(y, 4)
dblTempSum = dblTempSum + vDataArray(x, 2)
vSummaryArray(y, 4) = vSummaryArray(y, 4) + 1
vSummaryArray(y, 3) = dblTempSum / vSummaryArray(y, 4)
End If
End If
End If
Next
With wksSummary.Range("A2")
.Resize(65535, 4).ClearContents
.Resize(iLastDateRow  1, 4).Value = vSummaryArray
End With
End Sub</pre>

20040114, 17:17 #15
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Index/VLookup, etc. (2000)
Substitute this code for all of the previous?