MAX formula with Associated DATE

P

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
 
M

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

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
 
A

Aladin Akyurek

Patrick said:
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
Click to expand...

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
 
A

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
 
M

mangesh_yadav

Hi Aladin,

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

Manges
 
A

Aladin Akyurek

mangesh_yadav said:
Hi Aladin,

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

Mangesh
Click to expand...

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