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