How do a find a second Max?

K

kwiklearner

Hi, I'm trying to find the maximum and second maximum number out of a
range? I have tried: MAX(IF($A:$A<B1,$A:$A,0)). Any help would be
great.
 
R

Ron Coderre

Try the LARGE function:

=LARGE(A:A,2)

The second arugment of the function is where you set which large value
you want. The example above returns the 2nd largest value in Col_A.

Does that help?

Regards,
Ron
 
B

Biff

Hi!

If your data looks like this:

10
10
10
8
9

=MAX(A1:A5) = 10

Technically, the second max value is also 10:

=LARGE(A1:A5,2) = 10

But if you want the literal second max value:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A5<MAX(A1:A5),A1:A5)) = 9

Biff

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

kwiklearner

Thanks, this is great...

Ron said:
Try the LARGE function:

=LARGE(A:A,2)

The second argument of the function is where you set which large value
you want. The example above returns the 2nd largest value in Col_A.

Does that help?

Regards,
Ron
Click to expand...
 
B

Bob Phillips

Your formula would have worked as

=MAX(IF($A:$A<B1,$A:$A)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

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

Harlan Grove

Bob Phillips wrote...
Your formula would have worked as

=MAX(IF($A:$A<B1,$A:$A)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Click to expand...

Not so. Excel can't handle the $A:$A<B1 term.
 
R

Ron Coderre

How about this?

For a list of numbers in A1:A10
Example:
A1: 1
A2: 2
A3: 3
A4: 4
A5: 5
A6: 9
A7: 9
A8: 9
A9: 9
A10: 9

Cell B1 holds the rank to find, ignoring duplicates.

Example:
B1: 3 (indicating that you want the 3rd largest number)

C1:
=IF(B1=1,MAX($A$1:$A$10),LARGE(IF($A$1:$A$10<LARGE($A$1:$A$10,B1-1),$A$1:$A$10,0),B1-1))

Note 1: Commit that array formula by holding down the [Ctrl][Shift]
keys and press [Enter].

Note 2: In case window wrapping impacts this post, there are no spaces
in that formula.

Sample Values:
For B1: 1........C1: Returns 9
For B1: 2........C1: Returns 5
For B1: 3........C1: Returns 4
etc

Does that help?

Regards,
Ron
 
R

Ron Coderre

RE: Array formla:
C1: =IF(B1=1,MAX($A$1:$A$10),LARGE(IF($A$1:$A$10<LARGE
($A$1:$A$10,B1-1),$A$1:$A$10,0),B1-1))


Nah...doesn't work for mid-range dupes.

Ron
 
Top