Top 5 numbers in a range

[stavroseco]

i have being trying for 2 days with this problem, i have a table with numbers and i want to find the top 5 numbers that have appeared in range A4:G91
alla array formulas that i have used didnt work
pls help my mind is burned!!!!

[Pete_UK]

Put this in I6:

=LARGE($A$4:$E$91,H6)

then copy down to I10. It will give you all 45s, as your top-5 numbers are 45.

Hope this helps.

Pete

[stavroseco]

no i want the top 5 number that have appeared in the range .

[henco8]

largest number =LARGE(A4:E91,1)
second largest number =LARGE(A4:E91,2)
third largets largest number =LARGE(A4:E91,3)
etc


hope this helps

[bmouse]

There you go

[Olly]

In I6:

Please Login or Register  to view this content.

In I7, enter as array formula (ctrl-shift-enter):

Please Login or Register  to view this content.

and copy down through I10.

[bmouse]

largest number =LARGE(A4:E91,1)
second largest number =LARGE(A4:E91,2)
third largets largest number =LARGE(A4:E91,3)
etc


hope this helps

Doesn't work with multiple identical values! The largerst number is 45, and there are several 45's in the array, so you will get all 5 results as 45, checked it

[Olly]

There you go

Whilst that gives the answer, it doesn't help anyone learn HOW to get the answer.

[boopathiraja]

Hi, ref the attachment, Multiple cons ranges used, made values as row lables and thats provide the answer

[bmouse]

Whilst that gives the answer, it doesn't help anyone learn HOW to get the answer.

I used the MAX formula to determine the maximal value in each row.
I used Sort and sorted the numbers vertically from largest to smallest.
I used Remove Duplicates to remove duplicate values of the same number.

Hope that helps

[stavroseco]

i dont think you understand me .
in the range A4:E91 i want the top 5 numbers that appear the most.
for example the most common number is 13 with 25 apearances, the second most common number is 33 etc.

[Olly]

I used the MAX formula to determine the maximal value in each row.
I used Sort and sorted the numbers vertically from largest to smallest.
I used Remove Duplicates to remove duplicate values of the same number.

Hope that helps

Your method is flawed.

Imagine you had a row containing 60, 59, 58, 57 and 56.

They would be the top 5 numbers of the overall array. Your method would include 60, but would exclude 59, 58, 57 and 56.

[bmouse]

Your method is flawed.

Imagine you had a row containing 60, 59, 58, 57 and 56.

They would be the top 5 numbers of the overall array. Your method would include 60, but would exclude 59, 58, 57 and 56.

Touche! Oh, well, back to the drawing board and thanks for pointing that out

[Olly]

i dont think you understand me .
in the range A4:E91 i want the top 5 numbers that appear the most.
for example the most common number is 13 with 25 apearances, the second most common number is 33 etc.

So you want the MODE (most frequent) numbers, not the top (maximum) values?

In I6:

Please Login or Register  to view this content.

In I7, enter as array formula (ctrl-shift-enter):

Please Login or Register  to view this content.

and copy down through I10.


Edit: Although your suggestion that 13 is the most common number with 25 appearances doesn't match your dataset...

[bmouse]

I think I got it right this time, not an advanced solution though

[stavroseco]

So you want the MODE (most frequent) numbers, not the top (maximum) values?

In I6:

Please Login or Register  to view this content.

In I7, enter as array formula (ctrl-shift-enter):

Please Login or Register  to view this content.

and copy down through I10.


Edit: Although your suggestion that 13 is the most common number with 25 appearances doesn't match your dataset...

my suggestion of 13 was just an example not the real deal.
Now I6 worked!
I7 the array formula didnt work , excel said that "the formula contains an error"
maybe i am doing something wrong????

[Olly]

my suggestion of 13 was just an example not the real deal.

Bad idea. If you're going to quote numbers as an example of what you want, make them accurate...

Now I6 worked!
I7 the array formula didnt work , excel said that "the formula contains an error"
maybe i am doing something wrong????

Do you use , or ; as your formula argument separator, in Greece?

[boopathiraja]

Hi, Ref the attachment

[stavroseco]

i think is the same. i use English version of excel

[stavroseco]

**** it worked with the ; separator !!!

[Olly]

**** it worked with the ; separator !!!

Great. So that does what you want now?

[stavroseco]

two days i strangled and the error was the ; instead of a , !!!

[Olly]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also Add Reputation to helpful posts. Thanks.

[stavroseco]

thank you very much all!!!!!!!