May 21 2021 04:38 AM - edited May 21 2021 04:39 AM
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?
May 21 2021 06:31 AM
SolutionLet'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.
May 21 2021 07:08 AM - edited May 21 2021 07:46 AM
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.
May 21 2021 07:39 AM
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.
May 21 2021 07:51 AM
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.
May 21 2021 07:54 AM
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)
)
May 21 2021 08:37 AM
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)
)
May 21 2021 10:30 AM
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
Aug 06 2021 02:02 AM
Aug 06 2021 03:46 AM
Change the formula to
=IFERROR(MODE(IF(ISERROR(MATCH($A$2:$A$101,$C$1:$C1,0)),$A$2:$A$101)),"")
Aug 06 2021 06:52 AM
Aug 06 2021 06:57 AM
Aug 06 2021 07:15 AM
Here it is, although it shouldn't be hard to copy/paste the formula yourself...
Aug 06 2021 09:52 AM
Aug 06 2021 10:09 AM
May 21 2021 06:31 AM
SolutionLet'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.