4th most common text value

[sparkd]

I currently have the following formula to capture the 3rd most common text value from a range.
I cant seem to change this in order to capture the 4th most common text value


=INDEX(S19:S52,MODE(IF(((S19:S52<>"")*(S19:S52<>INDEX(S19:S52,MODE(IF(S19:S52<>"",MATCH(S19:S52,S19,S52,0)))))*(S19:S52<>INDEX(S19:S52,MODE(IF((S19:S52<>"")*(S19:S52<>INDEX(S19:S52,MODE(IF(S19:S52<>"",MATCH(S19:S52,S19:S52,0))))),MATCH(S19:S52,S19:S52,0)))))),MATCH(S19:S52,S19:S52,0))))

 

[Eric W]

First, you have a typo in your formula. In the first MATCH, you have S19,S52 when it should be S19:S52. Next, changing it to capture the 4th most common would be somewhat painful. Using the same logic, you'd have to add another set of INDEX/MODE/MATCH/MATCH functions. I'd suggest something like the formula in T19 where you just drag it down to get the most common values in order.

Book1
	Q	R	S	T
18				Most common values
19	d		a	c
20			a	a
21			a	d
22			b	e
23			b	f
24			c	b
25			c	x
26			c	z
27			c	y
28			d
29			e
30			e
31			c
32			d
33			a
34			e
35			d
36			c
37			x
38			y
39			x
40			z
41			a
42
43			f
44			f
45			f
46
47			z
48
49
50
51
52
Sheet2

Cell Formulas
Range	Formula
Q19	{=INDEX(S19:S52,MODE(IF(((S19:S52<>"")*(S19:S52<>INDEX(S19:S52,MODE(IF(S19:S52<>"",MATCH(S19:S52,S19:S52,0)))))*(S19:S52<>INDEX(S19:S52,MODE(IF((S19:S52<>"")*(S19:S52<>INDEX(S19:S52,MODE(IF(S19:S52<>"",MATCH(S19:S52,S19:S52,0))))),MATCH(S19:S52,S19:S52,0)))))),MATCH(S19:S52,S19:S52,0))))}
T19	{=IFERROR(INDEX($S$19:$S$52,MODE(IF($S$19:$S$52<>"",IF(COUNTIF($T$18:$T18,$S$19:$S$52)=0,MATCH($S$19:$S$52,$S$19:$S$52,0)*{1,1})))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Let us know if this works for you.

 

[Eric W]

After a little thought, I came up with this array formula that will return the nth most common value without needing the previous values:

=IFERROR(INDEX($S:$S,MOD(LARGE(IF($S$19:$S$52<>"",IF(MATCH($S$19:$S$52,$S$19:$S$52,0)=ROW($S$19:$S$52)-ROW($S$19)+1,COUNTIF($S$19:$S$52,$S$19:$S$52)+ROW($S$19:$S$52)/1000)),4),1)*1000),"")

Confirm with Control+Shift+Enter. Change the red 4 to the value you want. In case of ties, this lists the bottommost first, whereas my previous formula lists the topmost first.