Using MODE

[Guest]

How can the Top 10 MODEs (most frequently occurring number) of a column be
determined?

 

[Bob Phillips]

To produce a ranked list of most frequently occurring numbers

B1: = A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A
$200&""),0)))

as an array formula

Copy this as far down column B as you think you might have unique values in
column A

In C1

=IF($B$1:$B$10<>"",COUNTIF($A$1:$A$200,$B$1:$B$10))

and copy down as far as required

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Use AUTOFILTER and then in the drop down list of the column to filter on
select TOP TEN

 

[RagDyeR]

Say your column of numbers was A1 to A20.

Enter this formula anywhere you wish, and copy down 10 rows:

=INDEX($A$1:$A$20,MATCH(LARGE(FREQUENCY($A$1:$A$20,$A$1:$A$20),ROWS($1:1)),F
REQUENCY($A$1:$A$20,$A$1:$A$20),0))

You could enter the frequency placement numbers in a column, say B1 down,
and refer to *those* numbers as you copy this formula down the adjoining
column, say C1:

=INDEX($A$1:$A$20,MATCH(LARGE(FREQUENCY($A$1:$A$20,$A$1:$A$20),B1),FREQUENCY
($A$1:$A$20,$A$1:$A$20),0))

You could enter 2, 4, and 6, for instance in Column B, and retrieve that
*order* of frequency, as well as entering 1, 2, 3, ...etc.

If there are duplicate occurring values, the first in the list will be
returned as the highest frequency *and* the next highest.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


How can the Top 10 MODEs (most frequently occurring number) of a column be
determined?