finding most frequent number in a list

[Guest]

Anyone know a formula, macro, etc. that will take a certain list, and find the most frequent number that appears? How about the least frequent number that occurs?

I would also like to find the second most common, third most common, etc. I know I could just delete all occurences of the previously most common number, but would like to avoid this. Any advice much appreciated.

 

[Aladin Akyurek]

It keeps giving me 0 for the result. I may have entered my reference column in the wrong location. There's only 1 column, CSV!S3:S201. It also contains blank cells which would need to be ignored. I am trying to find the top 5 most occurring numbers in the list.
I used MODE to find the most common and was going to simply list the 2nd, 3rd, etc below the mode formula.

Borrowed from a post by pgc01, control+shift+enter, not just enter, and copy down:

=IFERROR(MODE(IF(ISNUMBER(CSV!$S$3:$S$201),IF(ISNA(MATCH(CSV!$S$3:$S$201+{0,0},$A$1:A1,0)),CSV!$S$3:$S$201))),"")

If you want also to know the occurrence frequencies associated with these numbers, please post back.

 

[Nanaia]

Thank you!
This gives me the number that occurs most often, the same one I got using MODE. What does the extra stuff do in it?
How can I modify it to show me the 2nd and 3rd most common numbers?
Thanks again!

 

[Aladin Akyurek]

Thank you!
This gives me the number that occurs most often, the same one I got using MODE. What does the extra stuff do in it?
How can I modify it to show me the 2nd and 3rd most common numbers?
Thanks again!

Just copy the formula down after you have confirmed it control+shift+enter.

 

[Aladin Akyurek]

Thank you!
This gives me the number that occurs most often, the same one I got using MODE. What does the extra stuff do in it?
How can I modify it to show me the 2nd and 3rd most common numbers?
Thanks again!

Here is the workbook that implements the suggested formula:
https://www.dropbox.com/s/st4l27um6ddlf7a/nanaia%20top%205%20most%20frequent%20numbers.xlsx?dl=0

 

[Nanaia]

I'm using this formula in a table in a separate tab/worksheet from the data it's using. It's a template type of file that we use for every new job we process. Basically Tab A has the data (a CSV that we copy and paste there that has the same column headings but the data in the columns changes for each job) and Tab B has the table that with the above formula from this post. The data on the worksheet that's being used (Tab A) to feed the table on Tab B changes with each job. The idea was to have a table in Tab B that tells us what the most common occurring numbers were from specific columns in Tab A as well as the largest and smallest numbers. It works great!... However, there have been instances where the table displays #VALUE in place of the number. My co-workers are uncomfortable with going into the formula in the cell to hit Ctrl+Shift+Enter to fix the error. This is all that has to be done to resolve the error. It doesn't always happen and I haven't determined (yet) if something specific happens to cause it. Is there a way to keep the error from occurring? Or would I be better off finding a different way to do this?

 

[Nanaia]

https://www.excelforum.com/excel-general/1227195-code-problem.html#post4881246