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.
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)
)
With ties:
=LET(
unique, UNIQUE(numbers),
count, COUNTIFS(numbers,unique),
sort, SORTBY(IF({1.0},unique,count),count,-1),
sorted_count, INDEX(sort,0,2),
include, sorted_count>=LARGE(sorted_count;5),
FILTER(sort,include)
)Exactly, if only correct misprints for English version
=LET( unique, UNIQUE(numbers), count, COUNTIFS(numbers,uniq), sort, SORTBY(IF({1},unique,count),count,-1), sorted_count, INDEX(sort,0,1), include, sorted_count>=LARGE(sorted_count,5), FILTER(sort,include) )and add frequency to the result
- Thanks
