1. ## 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 non-leap 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, 30-year 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 non-leap 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 leap-year, 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

2. ## 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 ctrl-shift 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

3. ## Re: Match/Index/VLookup, etc. (2000)

Yes, it works fine, but wow is it ever slow...

4. ## 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

5. ## Re: Match/Index/VLookup, etc. (2000)

Did I hear you volunteer? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

6. ## 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
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
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

7. ## Re: Match/Index/VLookup, etc. (2000)

Steve,
I can't seem to get it to work-wanna try?
Thanks,
Jeff

8. ## 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

9. ## Re: Match/Index/VLookup, etc. (2000)

Here's an abbreviated version of the file.

10. ## Re: Match/Index/VLookup, etc. (2000)

The worksheet change macro goes in the Summary sheet object not in a module.
In VB (alt-F11)
In Project explorer (ctrl-R) (left pane usually)
"Open" (if not opened) Microsoft excel objects "folder"
dbl-click on "summary" sheet
In Macro pane (usually right pane)

Steve

11. ## Re: Match/Index/VLookup, etc. (2000)

Steve-works 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!

12. ## 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 non-existent 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

13. ## 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 e-mail me your address, as I have about 30 years of historical price data.
Thanks,
Jeff

14. ## 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

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>

15. ## Re: Match/Index/VLookup, etc. (2000)

Substitute this code for all of the previous?

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•