Help on Large Function
 

Hi!

I'm currently using the LARGE function to return the top 5 numbers in a set
of cells. Now the problem is that the formula sometimes returns the same
value. Example

A
20
55
85
75
15
65
75
75
35
95

On the sample above, below formula returns the top value as:
=LARGE(a1:a10,{1}) ... {2}, {3} and so on..
top 1 = 95
top 2 = 85
top 3 = 75
top 4 =75
top 5 =75

What i want to happen is for the formula not to return the same value if
it's already on the other top numbers.

Is there any way to do that?

Pls help, thanks so much!

Here's one method. In C1 use your existing formula for
the largest number:

=LARGE(A1:A10,1)

In C2 use this formula (array-entered, meaning press ctrl
+ shift + enter), then fill down:

=LARGE($A$1:$A$10,SUM(COUNTIF($A$1:$A$10,$C$1:C1)) +1)

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi!

I'm currently using the LARGE function to return the top
5 numbers in a set
of cells. Now the problem is that the formula sometimes
returns the same
value. Example

A
20
55
85
75
15
65
75
75
35
95

On the sample above, below formula returns the top value
as:
=LARGE(a1:a10,{1}) ... {2}, {3} and so on..
top 1 = 95
top 2 = 85
top 3 = 75
top 4 =75
top 5 =75

What i want to happen is for the formula not to return
the same value if
it's already on the other top numbers.

Is there any way to do that?

Pls help, thanks so much!
.

One way...

Let A2:A11 house the sample you provided.

B1: I-Rank

B2, copied down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",RANK(A2,$A$2: $A$11))

C1: F-Rank

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$11,1),"")

D1: List

D2, copied fown:

=IF(ROW()-ROW(D$2)+1<=5,INDEX($A$2:$A$11,MATCH(ROW()-ROW(D$2)+1,$C$2:$C$11,0)),"")

Master wrote:
Hi!

I'm currently using the LARGE function to return the top 5 numbers in a set
of cells. Now the problem is that the formula sometimes returns the same
value. Example

A
20
55
85
75
15
65
75
75
35
95

On the sample above, below formula returns the top value as:
=LARGE(a1:a10,{1}) ... {2}, {3} and so on..
top 1 = 95
top 2 = 85
top 3 = 75
top 4 =75
top 5 =75

What i want to happen is for the formula not to return the same value if
it's already on the other top numbers.

Is there any way to do that?

Pls help, thanks so much!

Jason Morin wrote...
Here's one method. In C1 use your existing formula for
the largest number:

=LARGE(A1:A10,1)

Why not just

=MAX(A1:A10)

?

In C2 use this formula (array-entered, meaning press ctrl
+ shift + enter), then fill down:

=LARGE($A$1:$A$10,SUM(COUNTIF($A$1:$A$10,$C$1:C1) )+1)

Array formula unnecessary. Consider

=LARGE($A$1:$A$10,COUNTIF($A$1:$A$10,"="&C1)+1)

Superfluous columns... See Harlan's reply.

Aladin Akyurek wrote:
One way...

Let A2:A11 house the sample you provided.

B1: I-Rank

B2, copied down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",RANK(A2,$A$2: $A$11))

C1: F-Rank

C2, copied down:

=IF(N(B2),RANK(B2,$B$2:$B$11,1),"")

D1: List

D2, copied fown:

=IF(ROW()-ROW(D$2)+1<=5,INDEX($A$2:$A$11,MATCH(ROW()-ROW(D$2)+1,$C$2:$C$11,0)),"")


Master wrote:

Hi!

I'm currently using the LARGE function to return the top 5 numbers in
a set of cells. Now the problem is that the formula sometimes returns
the same value. Example

A
20
55
85
75
15
65
75
75
35
95

On the sample above, below formula returns the top value as:
=LARGE(a1:a10,{1}) ... {2}, {3} and so on..
top 1 = 95
top 2 = 85
top 3 = 75
top 4 =75
top 5 =75

What i want to happen is for the formula not to return the same value
if it's already on the other top numbers.

Is there any way to do that?

Pls help, thanks so much!

Using MAX vs. LARGE(---,1)? You're splitting hairs,
Harlan.

But I'll take your 2nd formula.

Jason

-----Original Message-----
Jason Morin wrote...
Here's one method. In C1 use your existing formula for
the largest number:

=LARGE(A1:A10,1)

Why not just

=MAX(A1:A10)

?

In C2 use this formula (array-entered, meaning press
ctrl
+ shift + enter), then fill down:

=LARGE($A$1:$A$10,SUM(COUNTIF($A$1:$A$10,$C$1:C1 ))+1)

Array formula unnecessary. Consider

=LARGE($A$1:$A$10,COUNTIF($A$1:$A$10,"="&C1)+1 )

.

Jason Morin wrote...
Using MAX vs. LARGE(---,1)? You're splitting hairs,
....

From split hairs great toupees are made.

MAX requires less typing, puts one fewer argument on the call stack,
and is stored in less memory in RAM and on disk. It's probably slightly
faster too. What's not to love?

He

B1: =MAX($A$1:$A$20)
B2: =MAX(IF($A$1:$A$20<B1,$A$1:$A$20,""))
copy down

Ola Sandstrom

95
85
75
65
55
35
20
15
0
0

....And to Show Top5 Only:
B1: =MAX($A$1:$A$20)
B2: =IF(COUNT($B$1:B1)<5,MAX(IF($A$1:$A$20<B1,$A$1:$A$ 20)),"")

Ola