Forum Discussion
Solved
Excel Formula
Hi, i want to list the 5 most frequented numbers in a column of my excel sheet top down. could anyone give me an idea about the right 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 already
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 already
Change 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?
ThanksHere it is, although it shouldn't be hard to copy/paste the formula yourself...