Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gregl
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Daniel.M
 
Posts: n/a
Default

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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
Daniel.M
 
Posts: n/a
Default

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


=INDEX(Dates,MATCH(...

Regards,

Daniel M.




  #11   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
Golfer
 
Posts: n/a
Default

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   Report Post  
gregl
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date issue between Windows and Macintosh version dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM
Excel has a "Find Next" command but no "Find Previous" command. Michael Fitzpatrick Excel Discussion (Misc queries) 2 January 11th 05 12:45 AM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM
Need to find Min value based on date range entered Chad S Excel Worksheet Functions 0 October 28th 04 08:03 PM
Need to find Min value based on date range entered Chad S Excel Worksheet Functions 1 October 28th 04 09:52 AM


All times are GMT +1. The time now is 07:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"