Pull top 3 highest values

[Martindelica]

Hi there, I have a column of numbers that I would love it if Excel could pull out the top 3 or 4 highest values from the column. Is that possible?

Kindest regards,
Martin.

[stevekirk]

hi,
if all the numbers are in column a then put thisin column b

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

i have just gone to a20 alter to suit you range

steve

[Martindelica]

It worked like a charm, but I am sorry I forgot to mention this:
colA ColB ColC
11, 12, 5
12, 13, 8
13, 14, 6
14, 15, 2
15, 16, 9
16, 17, 11

So the formula would put into 3 different cells:
16, 17, 11
15, 16, 9
12, 13, 8

Sorry about this, I should have thought it through. Is the above possible?

Kindest regards,
Martin.

[stevekirk]

hi,

did you want the highest from the 3 columns seperatly or the hightest of the 3 columns

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

this will do the highest of the 3 columns


or just change the first formular for each column change the "a" to "b " and so on
steve

[Martindelica]

Hey thanks alot...I think it best to post a file. Thanks again.

[stevekirk]

hi,

hopefully as you need it

steve

[Martindelica]

I have uploaded hopefully a clearer explanation. Thanks for the work though I appreciate the effort.

Kindest regards,
Martin

[stevekirk]

martin,

i have had to change it a bit as this is the only way i know how to do it.

steve

[Martindelica]

Ahhhh I see what you did. That is great. Good job. This will work nicely. Thanks a million.

[Martindelica]

Actually I have just discovered a problem with it. If there are two highest values the same or three for that matter, the formula won't differentiate between them. It just repeats the same range. Can this be fixed or not? If not, no problem

Thanks anyway,
Martin.

[stevekirk]

martin,

can they be sorted in decending order if so you can just link the top e columns . if not i wil have to look at it later.

having said that other members will be able to solve it no problem



steve

[db1]

Hi, I have a similar qustion. I have a dataset as follows:

SchoolName Municipality Score
School1 Muni1 10
School2 Muni2 30
School3 Muni3 65
School4 Muni4 80
School5 Muni5 66
School6 Muni6 77
. . .
. . .
. . .
SchoolN MuniN N

I need to pull the top 3 schools, along with their municipality, based on their score. I've done it manually by ordering and filtering, and using copy+paste, but now I have 100+ datasets.

Can anybody help me out with this? Thanks.

[ConneXionLost]

Hi db1,

You would be better served if you posted your question in a separate thread, that way you won't be hijacking Martindelica's thread.