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

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

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

.

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

.

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.

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

.


.

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.


.

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

.


.

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

.


.


.

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

=INDEX(Dates,MATCH(...

Regards,

Daniel M.

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

.


.


.

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

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

.


.


.


.

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.


.

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

.

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

.


.


.


.