| 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
|
|||
|
|||
|
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.) . . |
|
#6
|
|||
|
|||
|
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.) . . |
|
#7
|
|||
|
|||
|
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-
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. . |
|
#9
|
|||
|
|||
|
That formula returns the hdcp. I wanted the date that
corresponds to that hdcp, 6/5/1972. =INDEX(Dates,MATCH(... Regards, Daniel M. |
| 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 | |||
Hybrid Mode
