Data search problem

[Bishee]

Hi.

I need to list, in order of frequency, the five most frequently occurring
numbers from a cell series (say A1:J10). I have been stumped for weeks on
this.

I need
Most frequently-occurring number
Next most-frequently number
etc (all the way to, say the fifth-most-frequently occurring number).

I can used =mode(a1:J10) to find the most frequently-occurring number - but
then I am lost.

Can someone help? It looks simple ... but it's beaten me.

 

[Lars-Åke Aspelin]

Hi.

I need to list, in order of frequency, the five most frequently occurring
numbers from a cell series (say A1:J10). I have been stumped for weeks on
this.

I need
Most frequently-occurring number
Next most-frequently number
etc (all the way to, say the fifth-most-frequently occurring number).

I can used =mode(a1:J10) to find the most frequently-occurring number - but
then I am lost.

Can someone help? It looks simple ... but it's beaten me.

In you want the five results in cells K1 to K5, then try this.

In cell K1: MODE(A1:J10)

In cell K2: MODE(IF(A1:J10<>K1,A1:J10))

In cell K3: MODE(IF(A1:J10<>K1,IF(A1:J10<>K2,A1:J10)))

In cell K4: MODE(IF(A1:J10<>K1,IF(A1:J10<>K2,IF(A1:J10<>K3,A1:J10))))

In cell K5:
MODE(IF(A1:J10<>K1,IF(A1:J10<>K2,IF(A1:J10<>K3,iF(A1:J10<>K4,A1:J10)))))

Note all of these, except the first one, are array formula that have
to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

 

[T. Valko]

The problem with this is the 2 dimensional range. While MODE by itself can
handle a 2 dimensional range for a single result, a 2 dimensional range
greatly complicates things for the nth mode of the range.

One way to do this is to download and install the free add-in Morefunc.xll
from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

This add-in contains many useful functions. One of which is called
ARRAY.JOIN which will evaluate a 2 or 3 dimensional range as a 1 dimensional
vertical array.

With this function we can do what you want easily.

With your data in the range A1:J10 (named Table), enter this formula in A15
for the mode:

=MODE(Table)

Enter this array formula** in A16 for the nth mode:

=MODE(IF(COUNTIF(A$15:A15,ARRAY.JOIN(Table))=0,ARRAY.JOIN(Table)+{0,0}))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down as needed.

Note that both formulas will return the *first* instance of the nth mode
from left to right, top to bottom if there are multiple instances of the nth
mode.