Forum Discussion

BabakGhadiri's avatar
BabakGhadiri
Copper Contributor
May 21, 2021
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?
excel
  • HansVogelaar's avatar
    HansVogelaar
    May 21, 2021

    BabakGhadiri 

    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.

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 21, 2021

BabakGhadiri 

And on 365

=LET(
  freq,     FREQUENCY(numbers,numbers),
  skipLast, INDEX(freq, SEQUENCE(ROWS(freq)-1)),
  topN,     SEQUENCE(5),
  topNumbers, INDEX(SORTBY(numbers,skipLast,-1),topN),
  topFreq,  LARGE(SORT(skipLast,,-1),topN),
 IF({1,0}, topNumbers, topFreq)
)
BabakGhadiri's avatar
BabakGhadiri
Copper Contributor
May 21, 2021
Thank you a lot, this was useful.

Resources