Max number of Occurrence

[Saadi]

Hi guys, I am facing a problem to find out the maximum number of Text
Occurrence in a cloumn.

Suppose I have data in State filed like this in G Column

G
State
CA
CA
FL
GA
GA
CO
CA
FL
IL
FL
WA
FL

Now I want to Show top 3 states in column G30, and H30 automatically. e.g.

FL 4
CA 3
GA 2

so the problem is this how do I check the Max number of occurence of a state
and print it and its number in respective cells.

thanks in advance,

Saddat Sarfraz
Saadi

 

[Rob van Gelder]

Chip Pearson has a good article on most common entry in list:

http://www.cpearson.com/excel/lists.htm#MostCommon

 

[Saadi]

No it is not working its for Numbers but I have to work with text. or may be
I am using incorrectly. could you plz help more.

 

[Aladin Akyurek]

The question you posed belongs to the class of questions for which a Top
N list must be built...

One option is to run a pivot table, another to apply a formula system.
In what follows, I'll take up the latter route.

Let G29:G41 house the sample you provided with the label State in G29.

In H29 enter the label: Freq

In H30 enter & copy down:

=IF(ISNUMBER(MATCH(G30,$G$29:G29,0)),"",COUNTIF($G$30:$G$41,G30))

In I29 enter the label: Rank

In I30 enter & copy down:

=IF(N(H30),RANK(H30,$H$30:$H$41)+COUNTIF($H$30:H30,H30)-1,"")

In J27 enter the N of Top N: 3

J28:

=MAX(IF(INDEX(H30:H41,MATCH(J27,I30:I41,0))=H30:H41,I30:I41))-J27

which you need to confirm with control+shift+enter instead of the usual
enter.

The latter formula determines the ties of the Nth (3rd) value.

In J29 enter the label: Top N List
and in K29 the label: Top N Freq (or some such)

In J30 enter:

=IF(ROW()-ROW(J$30)+1<=$J$27+$J$28,INDEX(G$30:G$41,MATCH(ROW()-ROW(J$30)+1,$I$30:$I$41,0)),"")

which you copy across to K30 then down:

The area in J:K from J30 on will show the Top 3 states with associated
occurrence frequencies you're after.

 

[Harlan Grove]

Saadi wrote...
....

Suppose I have data in State filed like this in G Column

G
State
CA
CA
FL
GA
GA
CO
CA
FL
IL
FL
WA
FL

I'll assume the data is in a range named StateData.

Now I want to Show top 3 states in column G30, and H30 automatically. e.g.

FL 4
CA 3
GA 2

....

If you want to do this in just 6 cells, G30:H32, with no other cells
holding intermediate calculations (using more cells actually improves
recalc speed), then enter the following formulas

G30:
=INDEX(StateData,MODE(MATCH(StateData,StateData,0)))

G31: [array formula]
=INDEX(StateData,MODE(IF(COUNTIF(G$30:G30,StateData)=0,
MATCH(StateData,StateData,0))))

and fill G31 into G32,
H30:
=COUNTIF(StateData,G30)

and fill H30 into H31:H32.

 

[Tushar Mehta]

Create a PivotTable and let XL do the work for you. Set the row field
and the data field to be the State column and by default XL will do a
count of the states for you. You can sort descending or ask it to
restrict the display to the top N items.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions