MAX formula with Associated DATE

[Patrick White]

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

 

[mangesh_yadav]

=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]

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

Mangesh

 

[Aladin Akyurek]

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

 

[Patrick White]

Mangesh

Thanks for this. Works great.


"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...

 

[Mangesh Yadav]

Thanks for the feedback.

Mangesh

Mangesh

Thanks for this. Works great.


"mangesh_yadav"

 

[Aladin Akyurek]

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

 

[mangesh_yadav]

Hi Aladin,

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

Manges

 

[Aladin Akyurek]

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.