find the 2nd max, 3rd and so on

[ss]

how do we find the 2nd maximum number from a list like we find the maximum with =max(a1:a12)

 

[Asala42]

Sounds like you want the LARGE and SMALL functions.

=LARGE(A2:A12,2) should kick out the 2nd largest value in that range.

 

[ss]

that is correct. Thanks

an extension of that question -
can we locate the max, 2nd max., 3rd max data from a reference datasheet for a few particular ids in a different sheet.

 

[Asala42]

That complicates the formula a little but sure. For example here's an array formula:

{=LARGE(IF($B$2:$B$10=$D$2,$C$2:$C$10),2)}

Would give you the 2nd largest value in C2:C10 for only when the values in B2:B10 contains a value in D2.

(Hit Ctrl+Shift+Enter to get the brackets)
This message was edited by Asala42 on 2002-10-09 14:21

 

[Yogi Anand]

Hi ss:

You can use the INdex and Match functions to locate the largest, the 2nd largest, and so on ... so to pick up from Asala42's work,

see the following worksheet simulation

y021009h1.xls
	A	B	C	D	E
2	5		Largest	22	inrow6
3	9		2ndLargest	17	inrow2
4	17		3rdLargest	16	inrow7
5	11
6	3
7	22
8	6
9	2
10	16
11	7
12	4
Sheet3

</SPAN>

Regards!

Yogi

Regards!
This message was edited by Yogi Anand on 2002-10-09 14:23