find date in Col A corresponding to min value in Col B

[Guest]

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.)

 

[Frank Kabel]

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))

 

[Golfer]

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

 

[Guest]

Hi
and what would be your expected result in these cases?

 

[Daniel.M]

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.

 

[Golfer]

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

 

[Golfer]

Hi-

That formula returns the hdcp. I wanted the date that
corresponds to that hdcp, 6/5/1972.

Thanks

 

[Frank Kabel]

Hi
so you want the minimum value for a specific year???

 

[Golfer]

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

 

[Daniel.M]

That formula returns the hdcp. I wanted the date that

corresponds to that hdcp, 6/5/1972.

=INDEX(Dates,MATCH(...

Regards,

Daniel M.

 

[Frank Kabel]

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))

 

[Aladin Akyurek]

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 i
capable of returning a Top N list (N = 1, 2, ...) when no additiona
conditions involved (like year in your case). The system delivers th
same results as an appropriately built pivot table. Approaching th
problem of the dates list corresponding to the minimum handicap in
given year or every year in the data using pivot tables is a route yo
could take. The formula system I mentioned can be adapted in order t
create a conditional list of dates (all dates in a year associated wit
the minimum handicap in that year), but it would be quite unnerving. I
interested, we can take up the formula route. Just to recap th
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

 

[Golfer]

Thanks for stickin' in there with this. Yes, that formula
works.

Thanks

 

[Golfer]

I could have sworn that I tried a version of that and it
did not work.

Yes, that does work.

Thanks

 

[Golfer]

Hi-

Yes, I am interested in seeing your formula method. I can
probably learn something from it.

Thanks

 

[Guest]

And thanks for following this all the way through. I'll bet people think
we're the same person.