Results 1 to 14 of 14

20131116, 13:22 #1
 Join Date
 Dec 2000
 Location
 Calgary, Alberta, Canada
 Posts
 818
 Thanks
 6
 Thanked 1 Time in 1 Post
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 12May13
MIN 125,456.89 15July13
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

20131116, 13:53 #2
 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

20131116, 14:39 #3
 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

20131116, 15:38 #4
 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

20131116, 18:07 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,936
 Thanks
 139
 Thanked 510 Times in 486 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

20131116, 18:11 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,936
 Thanks
 139
 Thanked 510 Times in 486 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

20131116, 20:46 #7
 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

20131116, 21:44 #8
 Join Date
 Dec 2000
 Location
 Calgary, Alberta, Canada
 Posts
 818
 Thanks
 6
 Thanked 1 Time in 1 Post

20131116, 21:52 #9
 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

20131117, 03:45 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,936
 Thanks
 139
 Thanked 510 Times in 486 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

20131117, 07:19 #11
 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 rangevalued). 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

The Following User Says Thank You to sdckapr For This Useful Post:
zeddy (20131118)

20131118, 14:51 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,936
 Thanks
 139
 Thanked 510 Times in 486 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

20141021, 14:38 #13
 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.Last edited by Razvan Arthur Ionel; 20141021 at 14:44.

20141023, 20:03 #14
 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 ctrlshiftenter):
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))))
SteveLast edited by sdckapr; 20141023 at 20:12. Reason: corrected miscopied formulas