Max / Min but Returning Adjacent Data

[glerwell]

Hi

I have some data on my sheet like so:

Excel Workbook
	S	T
12	DATE	OVERALL
13	14/04/2011	0.76
14	13/04/2011	2.28
15	12/04/2011	1.50
16	*	*
17	*	*
18	11/04/2011	0.07
19	*	*
20	10/04/2011	0.94
21	*	*
22	09/04/2011	0.76
23	*	*
24	*	*
25	07/04/2011	1.28
26	*	*
27	*	*
28	06/04/2011	0.74
29	05/04/2011	0.76
30	04/04/2011	1.00
31	*	*
32	*	*
33	01/04/2011	2.04
34	*	*
35	*	*
36	31/03/2011	0.15
37	*	*
38	30/03/2011	0.50
PROFITS

What I want is 2 formulas that will look in column T and find the max and min vlaue but return the date that is adjacent to it in column S.

So from the data above I would like "13/04/2011" returned for MAX and "112/04/2011" for MIN.

Thanks in advance

 

[gsistek]

Sorry. Please ignore.

 

[Asator]

=index(s:s,match(min(t:t),t:t,0))
=index(s:s,match(max(t:t),t:t,0))

 

[T. Valko]

Hi

I have some data on my sheet like so:

Excel Workbook
	S	T
12	DATE	OVERALL
13	14/04/2011	0.76
14	13/04/2011	2.28
15	12/04/2011	1.50
16	*	*
17	*	*
18	11/04/2011	0.07
19	*	*
20	10/04/2011	0.94
21	*	*
22	09/04/2011	0.76
23	*	*
24	*	*
25	07/04/2011	1.28
26	*	*
27	*	*
28	06/04/2011	0.74
29	05/04/2011	0.76
30	04/04/2011	1.00
31	*	*
32	*	*
33	01/04/2011	2.04
34	*	*
35	*	*
36	31/03/2011	0.15
37	*	*
38	30/03/2011	0.50
PROFITS

What I want is 2 formulas that will look in column T and find the max and min vlaue but return the date that is adjacent to it in column S.

So from the data above I would like "13/04/2011" returned for MAX and "112/04/2011" for MIN.

Thanks in advance

Try these...

For the MAX:

=INDEX(S13:S38,MATCH(MAX(T13:T38),T13:T38,0))

For the MIN:

=INDEX(S13:S38,MATCH(MIN(T13:T38),T13:T38,0))

Format as Date

 

[glerwell]

thanks guys

 

[Aladin Akyurek]

Hi

I have some data on my sheet like so:

Excel Workbook
	S	T
12	DATE	OVERALL
13	14/04/2011	0.76
14	13/04/2011	2.28
15	12/04/2011	1.50
16	*	*
17	*	*
18	11/04/2011	0.07
19	*	*
20	10/04/2011	0.94
21	*	*
22	09/04/2011	0.76
23	*	*
24	*	*
25	07/04/2011	1.28
26	*	*
27	*	*
28	06/04/2011	0.74
29	05/04/2011	0.76
30	04/04/2011	1.00
31	*	*
32	*	*
33	01/04/2011	2.04
34	*	*
35	*	*
36	31/03/2011	0.15
37	*	*
38	30/03/2011	0.50
PROFITS

What I want is 2 formulas that will look in column T and find the max and min vlaue but return the date that is adjacent to it in column S.

So from the data above I would like "13/04/2011" returned for MAX and "112/04/2011" for MIN.

Thanks in advance

U12, just enter:

=MAX(T13:T38)

U13, just enter:

=COUNTIF($T$13:$T$38,U12)

U14, control+shift+enter, not just enter, and copy down:

=IF(ROWS(U$14:U14)<=U$13,INDEX($S$13:$S$38,SMALL(IF($T$13:$T$38=U$12,
     ROW($S$13:$S$38)-ROW($S$13)+1),ROWS(U$14:U14))),"")

V12, just enter:

=MIN(T13:T38)

Copy across U13 to V13.

Copy across U14 to V14 and then down.

 

[T. Valko]

thanks guys

You're welcome!