MAX formula with Associated DATE
 

Hi there

I have organised data into 2 Columns named DATE (A1:A10) and DATA (B1:B10)

What I am looking at doing is creating a formula where it sources the
highest and Lowest number in the DATA col. with the corresponding DATE as
well.

The first part I have worked out = MAX(B1:B10) and LOW(B1:B10) however I am
unsure of how to get it to lookup the corresponding date.

Thanks for any help.

Patrick

=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1)
and
=OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1)

Mangesh


PS: if I double-posted, sorry.


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376706

=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1)
and
=OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1)

Mangesh



"Patrick White" wrote in message
...
Hi there

I have organised data into 2 Columns named DATE (A1:A10) and DATA (B1:B10)

What I am looking at doing is creating a formula where it sources the
highest and Lowest number in the DATA col. with the corresponding DATE as
well.

The first part I have worked out = MAX(B1:B10) and LOW(B1:B10) however I
am
unsure of how to get it to lookup the corresponding date.

Thanks for any help.

Patrick

Patrick White wrote:
Hi there

I have organised data into 2 Columns named DATE (A1:A10) and DATA (B1:B10)

What I am looking at doing is creating a formula where it sources the
highest and Lowest number in the DATA col. with the corresponding DATE as
well.

The first part I have worked out = MAX(B1:B10) and LOW(B1:B10) however I am
unsure of how to get it to lookup the corresponding date.

Thanks for any help.

Patrick




Try either building a pivot table where you can make it show Top N (N=1)
or see:

http://www.excelforum.com/showthread.php?t=333697

for a formula system where you need to set the Top 3 to Top 1 in order
to create a list of dates that are associated with the Max value
instances. For a list of date instances corresponding to the Min value
instances, the same setup with the rank formula modified to:

=RANK(B3,$B$3:$B$9,1)+COUNTIF($B$3:B3,B3)-1

Mangesh

Thanks for this. Works great.


"mangesh_yadav"
wrote in message
news:mangesh_yadav.1q76am_1118048786.9146@excelfor um-nospam.com...

=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1)
and
=OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1)

Mangesh


PS: if I double-posted, sorry.


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376706

Thanks for the feedback.

Mangesh



"Patrick White" wrote in message
...
Mangesh

Thanks for this. Works great.


"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q76am_1118048786.9146@excelfor um-nospam.com...

=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1)
and
=OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1)

Mangesh


PS: if I double-posted, sorry.


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread:
http://www.excelforum.com/showthread...hreadid=376706

Are you sure? What happens if...

A1: 1-Jan-05
A2: 3-Jan-05
A3: 7-Feb-05
A4: 3-Mar-05
A5: 5-May-05

B1: 7
B2: 3
B3: 7
B4: 4
B5: 3

Patrick White wrote:
Mangesh

Thanks for this. Works great.


"mangesh_yadav"
wrote in message
news:mangesh_yadav.1q76am_1118048786.9146@excelfor um-nospam.com...

=OFFSET(B1,MATCH(MAX(B1:B10),B1:B10,0)-1,-1)
and
=OFFSET(B1,MATCH(MIN(B1:B10),B1:B10,0)-1,-1)

Mangesh


PS: if I double-posted, sorry.


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376706

Hi Aladin,

The functions will obviously choose the first occurence of the max and
min. One can't help with it.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376706

mangesh_yadav wrote:
Hi Aladin,

The functions will obviously choose the first occurence of the max and
min. One can't help with it.

Mangesh



At least two approaches, as I proposed, can: Pivot Tables and the
formula system I quoted.