Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Bronze Lounger
    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 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. #2
    WS Lounge VIP sdckapr's Avatar
    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 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. #3
    Bronze Lounger
    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...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Bronze Lounger
    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>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    Bronze Lounger
    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 work-wanna try?
    Thanks,
    Jeff

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    Bronze Lounger
    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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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 (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)
    add the worksheet change macro

    Steve

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #12
    WS Lounge VIP sdckapr's Avatar
    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 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. #13
    Bronze Lounger
    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 e-mail me your address, as I have about 30 years of historical price data.
    Thanks,
    Jeff

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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>


  15. #15
    Bronze Lounger
    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?

Page 1 of 3 123 LastLast

Posting Permissions

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