Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Max number of Occurrence
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 |
#2
|
|||
|
|||
Chip Pearson has a good article on most common entry in list:
http://www.cpearson.com/excel/lists.htm#MostCommon -- Rob van Gelder - http://www.vangelder.co.nz/excel "Saadi" wrote in message ... 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 |
#3
|
|||
|
|||
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. "Rob van Gelder" wrote: Chip Pearson has a good article on most common entry in list: http://www.cpearson.com/excel/lists.htm#MostCommon -- Rob van Gelder - http://www.vangelder.co.nz/excel "Saadi" wrote in message ... 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 |
#4
|
|||
|
|||
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:H4 1,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. Saadi wrote: 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 |
#5
|
|||
|
|||
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,StateDat a)=0, MATCH(StateData,StateData,0)))) and fill G31 into G32, H30: =COUNTIF(StateData,G30) and fill H30 into H31:H32. |
#6
|
|||
|
|||
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 In article , says... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Random Number Questions | Excel Worksheet Functions | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
multiply by actual number in cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |