Ordering array data

**bishee** · 01-10-2010, 06:42 PM

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.

**DonkeyOte** · 01-10-2010, 06:54 PM

If A1:J10 contain your values and say A12:A16 to hold 1 to 5 in order of frequency (desc)

Applying same formula to all for sake of consistency - ensure A11 holds say a non-numeric header value eg "Most Frequent:"

Please Login or Register  to view this content.

(obviously A12 need not be an Array but above is such that you can use the same formulae for all 5 cells for sake of consistency)

**bishee** · 01-11-2010, 04:23 AM

Thanks.

WHen I copy the formula into b12, it gives me the correct most frequently-occurring number of the series. However, when I copy b12 to the other cells below, b13:b16 I get the same value as in b12.

Thanks

**DonkeyOte** · 01-11-2010, 04:47 AM

On which basis I suspect it has not been entered correctly.

EDIT:
I just realised I populated A1:J10 with RANDBETWEEN in the sample (to dummy values) - note pre XL2007 this would require activation of the Analysis ToolPak Add-In (Tools -> Addins)
This function was used purely for demo. to populate A1:J10 and change output (A12:A16) with each calculation - important to note that this does not affect the formulae of interest in any way (A12:A16)

**bishee** · 01-11-2010, 05:00 AM

Thanks I have attached my sheet. I am a bit confused about the ctrl-shift-enter process. I applied it to inputting the formula into cell B12 and then dragged the corner of that cell to B16 where it was replicated. Do I have to do something different?

Thanks

**bishee** · 01-11-2010, 05:01 AM

I am not sure if the attachment was successful. I was adbised that It uploaded ok - but I canot see it on the posting

**DonkeyOte** · 01-11-2010, 05:02 AM

Originally Posted by bishee

...applied it to inputting the formula into cell B12 and then dragged the corner of that cell to B16 where it was replicated

If you're entering the formulae in B12:B16 rather than A12:A16 then you must adjust the MATCH range accordingly:

Please Login or Register  to view this content.

The function essentially takes the MODE of all the numbers that have not appeared in the most frequent list thus far... ie for B12 it looks at number in B11 (not a number), in B13 it discounts the number in B12 before calculating the MODE (hence 2nd most frequent) and so on and so forth... thus if your function references Column A (ie NOT the list) it will simply return the first MODE over and over.