Listing top 3 numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Excel Rookie - 31 May 2008 11:16 GMT

Hello,
Need some advice on this one:

I've used the rank function to assess the top 3 numbers in a range of cells.
I'd like to enter a formula into a cell that lists the top 3 numbers.
Or if this is not possible, enter a formula into 3 cells:
Cell 1: Highest result
Cell 2: Second highest
Cell 3: Third Highest.

Thanks!

Signature

Excel Rookie

Reply to this Message

Gary''s Student - 31 May 2008 11:34 GMT

How about ties?? If the values are:

6
7
8
8
10
9
7
6
10
9

do you want 10,10,9
or
10,9,8

Signature

Gary''s Student - gsnu200789

> Hello,
> Need some advice on this one:
[quoted text clipped - 7 lines]
>
> Thanks!

Reply to this Message

Excel Rookie - 31 May 2008 14:03 GMT

Hey Gary,
From your example, I'd want 10,10, 9.

Would also like to see how 10,9,8 would work.

Thanks!

Signature

Excel Rookie

> How about ties?? If the values are:
>
[quoted text clipped - 24 lines]
> >
> > Thanks!

Reply to this Message

Gary''s Student - 31 May 2008 14:23 GMT

To get the 10,10,9:

In cell B1 enter:
=LARGE(A1:A10,1)
In cell B2 enter:
=LARGE(A1:A10,2)
In cell B3 enter:
=LARGE(A1:A10,3)

To get 10,9,8:

In cell C1 enter:
=MAX(A1:A10)
In cell C2 enter:
=MAX(IF($A$1:$A$10<C1,$A$1:$A$10)) as an array formula
In cell C3 enter:
=MAX(IF($A$1:$A$10<C2,$A$1:$A$10)) as an array formula

Signature

Gary''s Student - gsnu200789

> Hey Gary,
> From your example, I'd want 10,10, 9.
[quoted text clipped - 31 lines]
> > >
> > > Thanks!

Reply to this Message

Dave - 31 May 2008 19:16 GMT

Hi,
And to get 10, 10, 9 in a single cell:

=LARGE(A1:A10,1)&", "&LARGE(A1:A10,2)&", "&LARGE(A1:A10,3)

Regards - Dave.

> To get the 10,10,9:
>
[quoted text clipped - 13 lines]
> In cell C3 enter:
> =MAX(IF($A$1:$A$10<C2,$A$1:$A$10)) as an array formula

Reply to this Message