View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
excelent excelent is offline
external usenet poster
 
Posts: 695
Default How to determine the top 5 occurrences from a list of numbers?

ups: use , not ;
=FREQUENCY(a1:a20,1)

"Eric" skrev:

Does anyone have any suggestions on how to determine the top 3 occurrences
from a list of numbers? For example,

1,1,1,1,1,2,2,2,3,3,3,3,3,3,4,4,5,5,5,5,5,6,6,6,6, 7,7,7,7,7 under column A
The occurrence of 1 is 5 times
The occurrence of 2 is 3 times
The occurrence of 3 is 6 times
The occurrence of 4 is 2 times
The occurrence of 5 is 5 times
The occurrence of 6 is 4 times
The occurrence of 7 is 5 times

It should return 3 in cell B1, 1 in cell B2, 5 in cell B3,
Since there are 3 numbers having 5 occurrences - 1,5,7, I only need to
select the rest of 2 numbers, then I prefer the smaller one 1, 5, and
ignoring 7.

Does anyone have any suggestions on how to do it in excel?
Thanks ver much in advance for any suggestions
Eric