Home |
Search |
Today's Posts |
#1
|
|||
|
|||
find date in Col A corresponding to min value in Col B
2 columns of data:
Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) |
#2
|
|||
|
|||
Hi
try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100)=2004,B1: B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) |
#3
|
|||
|
|||
That doesn't work if there are duplicate hdcp's.
1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . |
#4
|
|||
|
|||
Hi
and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . |
#5
|
|||
|
|||
That doesn't work if there are duplicate hdcp's.
The following ARRAY formula (Ctrl-Shift-Enter): aYear: 1972 Dates: Your range of Dates hdcps: Your range of handicaps. =INDEX(hdcps,MATCH(1,(YEAR(Dates)=aYear)* (hdcps=MIN(IF(YEAR(Dates)=aYear,hdcps))),0)) Regards, Daniel M. |
#6
|
|||
|
|||
Hi-
Based on the sample data posted the correct returned value would be 6/5/1972. That represents the date of lowest hdcp for the year 1972. The problem with that formula is in the match function. It correctly identifies 10 as the lowest hdcp in 1972 but since there is a duplicate it indexes the value of the first duplicate found and therefore returns the date of 1/1/1970. There can be many duplictes throughout the data. Thanks -----Original Message----- Hi and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . . |
#7
|
|||
|
|||
Hi-
That formula returns the hdcp. I wanted the date that corresponds to that hdcp, 6/5/1972. Thanks -----Original Message----- That doesn't work if there are duplicate hdcp's. The following ARRAY formula (Ctrl-Shift-Enter): aYear: 1972 Dates: Your range of Dates hdcps: Your range of handicaps. =INDEX(hdcps,MATCH(1,(YEAR(Dates)=aYear)* (hdcps=MIN(IF(YEAR(Dates)=aYear,hdcps))),0)) Regards, Daniel M. . |
#8
|
|||
|
|||
Hi
so you want the minimum value for a specific year??? -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- Based on the sample data posted the correct returned value would be 6/5/1972. That represents the date of lowest hdcp for the year 1972. The problem with that formula is in the match function. It correctly identifies 10 as the lowest hdcp in 1972 but since there is a duplicate it indexes the value of the first duplicate found and therefore returns the date of 1/1/1970. There can be many duplictes throughout the data. Thanks -----Original Message----- Hi and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . . |
#9
|
|||
|
|||
Hi-
The corresponding date of the minimum value for a specific year. There can be multiple dates witin the same year that also have the same hdcp - 6/5/1972 10 7/2/1972 10 Since this is a very complex formula I would expect the returned value be the earliest date by default. Thanks -----Original Message----- Hi so you want the minimum value for a specific year??? -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- Based on the sample data posted the correct returned value would be 6/5/1972. That represents the date of lowest hdcp for the year 1972. The problem with that formula is in the match function. It correctly identifies 10 as the lowest hdcp in 1972 but since there is a duplicate it indexes the value of the first duplicate found and therefore returns the date of 1/1/1970. There can be many duplictes throughout the data. Thanks -----Original Message----- Hi and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . . . |
#10
|
|||
|
|||
That formula returns the hdcp. I wanted the date that
corresponds to that hdcp, 6/5/1972. =INDEX(Dates,MATCH(... Regards, Daniel M. |
#11
|
|||
|
|||
Hi
try the following array formula (entered with cTRL+SHIFT+ENTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100)=1972,B1: B100)),IF(YEAR(A1:A10 0)=1972,B1:B100),0)) -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- The corresponding date of the minimum value for a specific year. There can be multiple dates witin the same year that also have the same hdcp - 6/5/1972 10 7/2/1972 10 Since this is a very complex formula I would expect the returned value be the earliest date by default. Thanks -----Original Message----- Hi so you want the minimum value for a specific year??? -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- Based on the sample data posted the correct returned value would be 6/5/1972. That represents the date of lowest hdcp for the year 1972. The problem with that formula is in the match function. It correctly identifies 10 as the lowest hdcp in 1972 but since there is a duplicate it indexes the value of the first duplicate found and therefore returns the date of 1/1/1970. There can be many duplictes throughout the data. Thanks -----Original Message----- Hi and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . . . |
#12
|
|||
|
|||
Golfer Wrote: Hi- The corresponding date of the minimum value for a specific year. There can be multiple dates witin the same year that also have the same hdcp - 6/5/1972 10 7/2/1972 10 Since this is a very complex formula I would expect the returned value be the earliest date by default. ... I'd think you'd want them all, not just one earliest or not. There is a formula system I posted at numerous occasions which is capable of returning a Top N list (N = 1, 2, ...) when no additional conditions involved (like year in your case). The system delivers the same results as an appropriately built pivot table. Approaching the problem of the dates list corresponding to the minimum handicap in a given year or every year in the data using pivot tables is a route you could take. The formula system I mentioned can be adapted in order to create a conditional list of dates (all dates in a year associated with the minimum handicap in that year), but it would be quite unnerving. If interested, we can take up the formula route. Just to recap the expected product: Given... Date Handicap 1/1/1970 10 3/5/1970 9 3/7/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 8/7/1972 10 we should get a llist consisting of: 3/5/1970 3/7/1970 for 1970... 5/1/1971 for 1971... 6/5/1972 8/7/1972 for 1972. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=278211 |
#13
|
|||
|
|||
Thanks for stickin' in there with this. Yes, that formula
works. Thanks -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+ENTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =1972,B1:B100)),IF(YEAR(A1:A10 0)=1972,B1:B100),0)) -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- The corresponding date of the minimum value for a specific year. There can be multiple dates witin the same year that also have the same hdcp - 6/5/1972 10 7/2/1972 10 Since this is a very complex formula I would expect the returned value be the earliest date by default. Thanks -----Original Message----- Hi so you want the minimum value for a specific year??? -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- Based on the sample data posted the correct returned value would be 6/5/1972. That represents the date of lowest hdcp for the year 1972. The problem with that formula is in the match function. It correctly identifies 10 as the lowest hdcp in 1972 but since there is a duplicate it indexes the value of the first duplicate found and therefore returns the date of 1/1/1970. There can be many duplictes throughout the data. Thanks -----Original Message----- Hi and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . . . . |
#14
|
|||
|
|||
I could have sworn that I tried a version of that and it
did not work. Yes, that does work. Thanks -----Original Message----- That formula returns the hdcp. I wanted the date that corresponds to that hdcp, 6/5/1972. =INDEX(Dates,MATCH(... Regards, Daniel M. . |
#15
|
|||
|
|||
Hi-
Yes, I am interested in seeing your formula method. I can probably learn something from it. Thanks -----Original Message----- Golfer Wrote: Hi- The corresponding date of the minimum value for a specific year. There can be multiple dates witin the same year that also have the same hdcp - 6/5/1972 10 7/2/1972 10 Since this is a very complex formula I would expect the returned value be the earliest date by default. ... I'd think you'd want them all, not just one earliest or not. There is a formula system I posted at numerous occasions which is capable of returning a Top N list (N = 1, 2, ...) when no additional conditions involved (like year in your case). The system delivers the same results as an appropriately built pivot table. Approaching the problem of the dates list corresponding to the minimum handicap in a given year or every year in the data using pivot tables is a route you could take. The formula system I mentioned can be adapted in order to create a conditional list of dates (all dates in a year associated with the minimum handicap in that year), but it would be quite unnerving. If interested, we can take up the formula route. Just to recap the expected product: Given... Date Handicap 1/1/1970 10 3/5/1970 9 3/7/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 8/7/1972 10 we should get a llist consisting of: 3/5/1970 3/7/1970 for 1970... 5/1/1971 for 1971... 6/5/1972 8/7/1972 for 1972. -- Aladin Akyurek ---------------------------------------------------------- -------------- Aladin Akyurek's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=278211 . |
#16
|
|||
|
|||
And thanks for following this all the way through. I'll bet people think
we're the same person. "Golfer" wrote: Thanks for stickin' in there with this. Yes, that formula works. Thanks -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+ENTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =1972,B1:B100)),IF(YEAR(A1:A10 0)=1972,B1:B100),0)) -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- The corresponding date of the minimum value for a specific year. There can be multiple dates witin the same year that also have the same hdcp - 6/5/1972 10 7/2/1972 10 Since this is a very complex formula I would expect the returned value be the earliest date by default. Thanks -----Original Message----- Hi so you want the minimum value for a specific year??? -- Regards Frank Kabel Frankfurt, Germany "Golfer" schrieb im Newsbeitrag ... Hi- Based on the sample data posted the correct returned value would be 6/5/1972. That represents the date of lowest hdcp for the year 1972. The problem with that formula is in the match function. It correctly identifies 10 as the lowest hdcp in 1972 but since there is a duplicate it indexes the value of the first duplicate found and therefore returns the date of 1/1/1970. There can be many duplictes throughout the data. Thanks -----Original Message----- Hi and what would be your expected result in these cases? "Golfer" wrote: That doesn't work if there are duplicate hdcp's. 1/1/1970 10 3/5/1970 9 1/1/1971 15 5/1/1971 12 1/1/1972 11 6/5/1972 10 using 1972 as the year, that formula returns 1/1/1970 -----Original Message----- Hi try the following array formula (entered with cTRL+SHIFT+eNTER): =INDEX(A1:A100,MATCH(MIN(IF(YEAR(A1:A100) =2004,B1:B100)),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany gregl wrote: 2 columns of data: Column A has dates from 1/1/70 onward (in chronological order). Column B has a golf handicap as of the date in column A (in no order - varies up or down). I use the DMIN function to find the lowest handicap in Column B for each calendar year (based on range of dates in Column A). My question is, how do I find the date in Column A that corresponds to the lowest handicap in Column B for the given calendar year. (I want to exclude the dates from other years that had the same handicap.) . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |