Using MODE to determine most popular values

[pioniere]

Hello,

I have a large spreadsheet containing all numbers, and would like to determine the top 4 most frequent values on the spreadsheet. I know how to use MODE to determine the most frequent value in a range. How can I use it to determine the 2nd most frequent value, 3rd most frequent value, etc.

Thanks in advance!

 

[barry houdini]

If you have numbers in A2:A100 then try this to get a list of the most frequent numbers

In C1 put some text like "modelist" then in C2 use this formula

=MODE(IF(COUNTIF(C$1:C1,A$2:A$100)=0,A$2:A$100))

confirmed with CTRL+SHIFT+ENTER and copied down as far as required

in the adjacent column in D2 you could use a COUNTIF formula to count the occurrences too, i.e. in D2 copied down

=COUNTIF(A$2:A$100,C2)

 

[pioniere]

Hm, I tried that and it says I am creating a circular reference

 

[pioniere]

Never mind, I must have made a mistake. It worked perfectly. Thanks!!!

 

[MI_Soldier]

Mr. Houdini,

I just wanted to thank you for this post. I have spent the last two days looking around the internet trying to find the solution you provided. Thank you very much for your post!

- Brian