Brutish calculation of nth mode

[CaptainPi]

Hello,I'm trying to calculate the "nth mode" (i.e. the nth most frequent value) in a large list. Alternatively, I want to rank the values of the list from most frequently appearing to least frequently appearing.A brutish way that I thought of doing this would be to calculate the mode then delete all instances of the mode in the list, calculate the mode of the new list (thereby giving me the second most frequent element), and repeating this process until the list is empty. How would I do this using a macro?

 

[Fazza]

hi,

What about instead using a pivot table? Make a pivot table with the values as a row field and also a data field - the data field being a count. Then sort the row field on descending counts. now you have a list with the most frequent at the top and its count adjacent. For the nth value, use an INDEX formula.

HTH

 

[CaptainPi]

hi,

What about instead using a pivot table? Make a pivot table with the values as a row field and also a data field - the data field being a count. Then sort the row field on descending counts. now you have a list with the most frequent at the top and its count adjacent. For the nth value, use an INDEX formula.

HTH

Hi, thank you for your reply. I'm unfamiliar with pivot tables. Can you walk me through what you just said. (I'm using Excel 2010.)

 

[Fazza]

Here is a link to some well presented info DataPig Technologies

And also Contextures Excel Tips, Excel Tutorials and Excel Examples

Pivot tables have been around since the mid 1990s. Please google, or refer to Excel help, you'll find lots more info. They are wonderful tools. Row fields are on the left hand side, data fields in the middle. A numeric data field will default to SUM but for your task needs to be changed to COUNT. I work in Excel 2003 & right clicking from a pivot table item is often a means to change properties & settings - I don't know about Excel 2010 though.

regards

 

[CaptainPi]

Thank you!

Holy bejesus that was simple using pivot tables. Fantastic! I used this video to get introduced and then the above advice How to Create a Pivot Table in Excel 2007 - Video - For Dummies

 

[Fazza]

You were fast! Welcome to the world of pivot tables!

 

[T. Valko]

Hello,I'm trying to calculate the "nth mode" (i.e. the nth most frequent value) in a large list. Alternatively, I want to rank the values of the list from most frequently appearing to least frequently appearing.A brutish way that I thought of doing this would be to calculate the mode then delete all instances of the mode in the list, calculate the mode of the new list (thereby giving me the second most frequent element), and repeating this process until the list is empty. How would I do this using a macro?

Here's a formula method.

Book1
	A	B
1	Value	Header
2	1	6
3	1	10
4	2	1
5	3	3
6	3	4
7	4	_
8	4	_
9	5	_
10	6	_
11	6	_
12	6	_
13	10	_
14	10	_
15	10	_
Sheet1

I'm using Excel 2010

This array formula** entered in B2:

=IFERROR(MODE(IF(ISNA(MATCH(A$2:A$15,B$1:B1,0)),A$2:A$15)),"")

** 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 until you get blanks.

 

[CaptainPi]

Hi, unfortunately the list is very long (million rows) and that technique seems too computationally intensive (for the first 500 rows it took about 5 minutes). For some reason the pivot table works it out very quickly.

 

[T. Valko]

Hi, unfortunately the list is very long (million rows) and that technique seems too computationally intensive (for the first 500 rows it took about 5 minutes). For some reason the pivot table works it out very quickly.

Yeah, that would take a while to calculate on 1m rows of data!