Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Question Finding date to match MAX and Min values (Excel 2010)

    This is probably something that is fairly simple, but it is currently beyond my knowledge of Excel.

    I maintain a spreadsheet of investments and part of that is tracking the MAX and MIN values over the year. This helps me isolate things that are happening and I can, hopefully, trace these occurrences back to what was going on in the market at the time. I can calculate the MAX and MIN values okay, but what I would like to do is display the dates that these occurred beside them in the spreadsheet to look something like this:

    MAX 150,769.45 12-May-13
    MIN 125,456.89 15-July-13

    How do display the dates? I suspect that it has something to do with a "Lookup" function, but I am not sure how this all works. The dates of the occurrence of the maximum/minimum are available in row 2 above the data itself which is in row 24. The tabular display of the MAX/MIN are in a different part of the spreadsheet, below where the tabular data is displayed. Any help or insight that anyone would care to provide will be graciously appreciated. Thanks.

    Ron M

  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
    If the dates are in column A and the values in Column B, this will display the date for the min and max respectively:
    =INDEX(A:A,MATCH(MIN(B:B),B:B,0))
    =INDEX(A:A,MATCH(MAX(B:B),B:B,0))

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Thanks Steve, I will give this a try and see what happens.

    Ron M

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    I tried the formulas that you posted and they gave me an answer. The only problem was that it was the wrong answer and the "date" that I got for both the MIN and MAX result was the same date, both of which were wrong.

    My dates are in row 2 and run from column B to column AR (B2:AR2). The financial values are in row 24 and cover the same columns, from B24 to AR24, so I translated the formulas as follows

    =INDEX(B2:AR2),MATCH(MIN(B24:AR24),B24:B24,0))

    The MAX formula was the same with the appropriate substitution. Am I missing something, or am I translating these the wrong way? Thanks.

    Ron M

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Ron

    You are almost there. But you should reference the whole row (as opposed to the whole column as per Steve's method).
    So use these:
    =INDEX($24:$24,MATCH(MIN($2:$2),$2:$2))
    =INDEX($24:$24,MATCH(MAX($2:$2),$2:$2))

    ..for the min and max values respectively.
    Don't forget to change the row number references if you change your data layout.

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Ron,

    Just to be clear, the previous formulas in my post#5 are 'fetching' the values from row24, using the corresponding column locations found (i.e. index positions) of the min (and max) dates found in row2.

    zeddy

  7. #7
    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
    Ron,
    It should be:
    =INDEX(B2:AR2),MATCH(MIN(B24:AR24),B24:AR24,0))

    Zeddy,
    It should not require the whole row (and it may not work if column A or anything after AR has a number in it). Ron's setup should also work.

    If one does use the whole row, unless the data is in ascending order, your formulas should use the exact match, an inexact match with data that is not ordered can yield odd results:
    =INDEX($24:$24,MATCH(MIN($2:$2),$2:$2,0))
    =INDEX($24:$24,MATCH(MAX($2:$2),$2:$2,0))

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Quote Originally Posted by sdckapr View Post
    Ron,
    It should be:
    =INDEX(B2:AR2),MATCH(MIN(B24:AR24),B24:AR24,0))

    Zeddy,
    It should not require the whole row (and it may not work if column A or anything after AR has a number in it). Ron's setup should also work.

    If one does use the whole row, unless the data is in ascending order, your formulas should use the exact match, an inexact match with data that is not ordered can yield odd results:
    =INDEX($24:$24,MATCH(MIN($2:$2),$2:$2,0))
    =INDEX($24:$24,MATCH(MAX($2:$2),$2:$2,0))

    Steve
    Thanks Steve. I think there was a typo in my posting, but I will definitely check it out and make sure there isn't a typo in my spreadsheet..

    Ron M
    Last edited by Ron M; 2013-11-16 at 21:49.

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Problem SOLVED!

    Thanks Steve, I fixed a typo and it all seems to work, now, if I just knew what it was doing .

    Zeddy, thanks for your input. I will definitely remember to change any references in the formulas should they change.

    Ron M

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Steve/Ron

    OK, I was not properly awake when I posted#5.
    My previous posted formulas had the rows the wrong way round.
    You noticed that of course.

    To check for the min and max values of data, you need to check the values in row24 (i.e. not row2).
    To fetch the corresponding dates, you fetch the date values from row2 (i.e. not row24).

    So my formulas should have been..
    =INDEX($2:$2,MATCH(MIN($24:$24),$24:$24))
    =INDEX($2:$2,MATCH(MAX($24:$24),$24:$24))

    Now, as Steve pointed out, unless the data is in ascending order, you should use the MATCH function with ,0) or ,False) to get an exact match.
    But, in my defence, since we are first finding the Minimum value, and then searching for it, we know that it MUST find it.
    Of course, if the minimum value is duplicated in row24, the MATCH function will find the first one. Ditto for the max value.

    The main reason I would recommend using the whole row in the formula is
    1. it allows for data to expand beyond column [AR] without requiring changes to the formula (but Steve's point about other data which may be in the row that could affect this is well noted).
    2. the MATCH part of the formula will return the actual column number
    (i.e. similar to using the MATCH function on a whole column, the return value (if found) will be the actual row number, which makes confirmation checking much easier.)

    zeddy

  11. #11
    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
    Zeddy,
    There is some logic to using the whole row for the index and match, based on what you said (confirmation and expansion) but as I pointed out, if there are other numbers outside the range you may pick up those other numbers.

    But note about the zero, if the list is not sorted, and you do not do an exact match, checking for the MIN may actually NOT find any value and the max may give the wrong value. I have attached a workbook demonstrating with some random numbers (that I range-valued). An exact match checks EVERY value from the start until it finds a match, the ascending (or descending) match is a more efficienct search since it seems to "check by halves" so (for example)a 1000 records can be search with just 7 checks, but can (and very often does) give erroneous results.

    Steve
    Attached Files Attached Files

  12. The Following User Says Thank You to sdckapr For This Useful Post:

    zeddy (2013-11-18)

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Steve

    Point taken.
    Your demo file proved how unwise it is to skip specifying the exact match ,0) parameter when you are working with unsorted data.
    I will never forget that from now on!

    zeddy

  14. #13
    New Lounger
    Join Date
    Oct 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello

    I need a solve for my problem .

    i need to find the closest date for an item , my probelm is, all the date of my items are in the same row.

    So , i need to find the closest date for each item, thanks in advance.
    Attached Files Attached Files
    Last edited by Razvan Arthur Ionel; 2014-10-21 at 14:44.

  15. #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
    You need to test 2 criteria: the absolute date difference and the item code value in Col B

    In I14, J14 and K14 enter the respective array formulas (confirm with ctrl-shift-enter):
    Code:
    =VLOOKUP(H14,$A$15:$B$500,2,0)
    =INDEX(D:D,MIN(IF(($B$15:$B$500=I14)*(ABS($D$15:$D$500-$I$12)=MIN(IF(($B$15:$B$500=I14),ABS($D$15:$D$500-$I$12)))),ROW($B$15:$B$500))))
    =INDEX(E:E,MIN(IF(($B$15:$B$500=I14)*(ABS($D$15:$D$500-$I$12)=MIN(IF(($B$15:$B$500=I14),ABS($D$15:$D$500-$I$12)))),ROW($B$15:$B$500))))
    Copy to row 15 (or as far as desired).

    Steve
    Last edited by sdckapr; 2014-10-23 at 20:12. Reason: corrected miscopied formulas

Posting Permissions

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