Most Frequent Letter in a Column of Single Letters?

[skinnea]

I have a spreadsheet that uses C, Q and B to denote three different states for a number of locations (Closed, Quiet, Busy).

Looking down each column there will be multiple occurrences of each letter, but I want to find the most frequently occurring one.

I've been able to use the following formula to decide on the most frequent between two of the letters, but am unsure how to factor in a comparison against the third one?

=IF(COUNTIF(G3:G14,"c")>(COUNTIF(G3:G14,"b")),"c","b")

I want the calculating cell (where the formula above sits) to show the actual letter that is most frequent, not the number of times it appears.

MODE would give me the detail I wanted if I was using 1, 2 and 3 - but I'm not!!

Any ideas...?

 

[al_b_cnu]

Hi,

This works:

=IF(COUNTIF(G3:G14,"b")>MAX(COUNTIF(G3:G14,"c"),COUNTIF(G3:G14,"q")),"b",IF(COUNTIF(G3:G14,"c")>COUNTIF(G3:G14,"q"),"c","q"))

HTH

Alan

 

[fairwinds]

=CHAR(MODE(CODE(G1:G14)))

 

[skinnea]

So it does - hurrah!

Many thanks...

 

[Aladin Akyurek]

A few weeks back, I'd have suggested one of the following which must be confirmed with constrol+shift+enter instead of just enter:

=INDEX(G3:G14,MATCH(MAX(COUNTIF(G3:G14,G3:G14)),COUNTIF(G3:G14,G3:G14),0))

or using the morefunc add-in:

=INDEX(G3:G14,MATCH(MAX(SETV(COUNTIF(G3:G14,G3:G14))),GETV(),0))

However, this is not quite right:

aaModeOfOrMostFrequentLetterVal skinnea.xls
	G	H	I	J	K
1				1
2	Name	Count	Rank	1	Mode
3	C	5	1		C
4	C				B
5	C
6	B	5	2
7	Q	2	3
8	B
9	B
10	B
11	C
12	Q
13	C
14	B
Sheet1

Formulas...

H3:

=IF(G3<>"",IF(ISNUMBER(MATCH(G3,$G$2:G2,0)),"",COUNTIF($G$3:$G$14,G3)),"")

I3:

=IF(N(H3),RANK(H3,$H$3:$H$14)+COUNTIF($H$3:H3,H3)-1,"")

J1 houses 1 (meaning Top Frequent, set to 1)

J2, which computes the number of ties of the most frequent...

=MAX(IF(INDEX(H3:H14,MATCH(J1,I3:I14,0))=H3:H14,I3:I14))-J1

which must be confirmed with control+shift+enter instead of just enter.

K3:

=IF(ROW()-ROW($K$3)+1<=$J$1+$J$2,INDEX($G$3:$G$9,MATCH(ROW()-ROW($K$3)+1,$I$3:$I$9,0)),"")

As you see, C and B are the most frequent, not just C or B alone.