Forum Discussion
Excel Formula
Let's say the numbers are in A2:A101.
In C2, enter the formula
=MODE(IF(ISERROR(MATCH($A$2:$A$101,$C$1:$C1,0)),$A$2:$A$101))
and confirm it with Ctrl+Shift+Enter to turn it into an array formula (this is essential!)
Fill down to C6.
See the attached sample workbook.
Let's say the numbers are in A2:A101.
In C2, enter the formula
=MODE(IF(ISERROR(MATCH($A$2:$A$101,$C$1:$C1,0)),$A$2:$A$101))
and confirm it with Ctrl+Shift+Enter to turn it into an array formula (this is essential!)
Fill down to C6.
See the attached sample workbook.
- Hi Hans !
you sent me already a formula about the 5 Top most repeated numbers in a column in excel. This was very useful for me. Just another question. when i fill down the column C , it shows till Row 11 and afterwards shows #N/A . how can i make it show the rest numbers down without limit ? or may be you send me a new formula without this limit?
Thanks alreadyChange the formula to
=IFERROR(MODE(IF(ISERROR(MATCH($A$2:$A$101,$C$1:$C1,0)),$A$2:$A$101)),"")
- Hi,
could you please send to me its workbook as xslx file?
Thanks
- Thanks a lot, it was useful.
Thanks,
but i do not know what is wrong with my sheet, it does not work on my sheet, instead it pops up an error. i sent the screen shot to you, could you please check it.
Column D simply uses COUNTIF to return the frequency of the number in column C.
The formula in C2 calculates the most frequently occurring number, that in C3 the second most frequently occurring number etc. These formulas only refer to column A, not to column D.
