Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data search problem
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data search problem
On Sun, 10 Jan 2010 14:13:01 -0800, Bishee
wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data search problem
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/300...-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,ARR AY.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. -- Biff Microsoft Excel MVP "Bishee" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
having a problem with IF and SEARCH | Excel Discussion (Misc queries) | |||
Xl search problem | Excel Discussion (Misc queries) | |||
Problem with search and replace data,thanks for you help in advance. | Excel Discussion (Misc queries) | |||
Problem with search | Excel Worksheet Functions | |||
Problem with search | Excel Worksheet Functions |