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.
With a pivot table:
Numbers column in rows area and in values area. Change from Sum to Count.
Sort values column in descending order.
Set a Top10 filter for the Numbers column.