Microsoft Excel - finding number - Asked By Dan on 14-Sep-11 08:19 PM

I figured out how to find the number that occured the most times in a column, but how can I find the second, third, fourth and so on?  Iam using XL2007

Dan
smr replied to Dan on 14-Sep-11 10:44 PM
hi

use this

var most = list.GroupBy(i=>i).OrderByDescending(grp=>grp.Count())
    .Select(grp=>grp.Key).First();
 
or in query syntax:
 
var most = (from i in list
      group i by i into grp
      orderby grp.Count() descending
      select grp.Key).First();
 
Of course, if you will use this repeatedly, you could add an extension method:
 
public static T MostCommon<T>(this IEnumerable<T> list)
{
  return ... // previous code
}
 
Then you can use:
 
var most = list.MostCommon();

refer
http://stackoverflow.com/questions/355945/find-the-most-occuring-number-in-a-listint
http://www.excelforum.com/excel-new-users/372335-formula-to-count-number-of-times-the-letter-x-appears-in-a-column.html
smr replied to Dan on 14-Sep-11 10:46 PM
hi

here is an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5


A B
Salesperson Invoice
Buchanan 15,000
Buchanan 9,000
Suyama 8,000
Suyama 20,000
Buchanan 5,000
Dodsworth 22,500
Formula Description (Result)
=COUNTIF(A2:A7,"Buchanan") Number of entries for Buchanan (3)
=COUNTIF(A2:A7,A4) Number of entries for Suyama (2)
=COUNTIF(B2:B7,"< 20000") Number of invoice values less than 20,000 (4)
=COUNTIF(B2:B7,">="&B5) Number of invoice values greater than or equal to 20,000 (2)

refer
http://office.microsoft.com/en-us/excel-help/count-how-often-a-value-occurs-HP001127779.aspx
TSN ... replied to Dan on 14-Sep-11 11:13 PM
hi try like this..


I think I got it

=MAX(FREQUENCY(IF(C14:C30=0,ROW(C14:C30)),IF(C14:C30<>0,ROW(C14:C30))))

Crlt + SHIFT + ENTER

For Example:
0
1
0
0
0
1
1
0
0
1
0
0
1
0
0
0
0

E.g. 3

hope this helps you.....
Jitendra Faye replied to Dan on 14-Sep-11 11:17 PM

Counting the number of occurrences of a text string in a range

    Type the following on sheet1:

      A1: Fruit
      A2: apple,apple
      A3: orange
      A4: apple,orange
      A5: grape
      A6: orange,grape
      A7: grape, apple
      A8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple")
        

    NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula, press COMMAND+RETURN.

The value of cell A8 is 4 because the text "apple" appears four times in the range.

Radhika roy replied to Dan on 15-Sep-11 11:33 AM

Solution-

Method 1

 

Use this formula

=SUM(IF(range="text",1,0))

 

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

 

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

 

Method 2

 

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

 

 

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

 

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

 

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

 

=COUNTIF(a1:a10,"?x*")

 

 

Hope this will help you.

Pichart Y. replied to Dan on 15-Sep-11 01:07 PM
Hi Dan,

To get what you expect, it is quite complicate...
I design as following...
  1. if the value is the most frequency then identify it's status as 1stMostFreq in the right cell or if it is the 2ndmostFreq and if it is 3rdMostFreq....else "Not in Rank"
  2. Conditional Format them with color, red for the 1stMost Freq, yellow for 2ndMostFreq and green for 3rdMostFreq.
  3. also conditional format the value it is the 1stMostFreq / 2ndMostFreq or 3rdMostFreq
Here is how to...
Assume that your data is in B4:B26
  1. Find frequency of each value
    • in H4 input this formula =COUNTIF($B$4:$B$26,B4) and copy the formula down
  2. Find first time count of each value
    • in I4 input this formula =COUNTIF($B$4:B4,B4) and copy the formula down
  3. Find the value status, we must use the Array formula(after input the formula, instead of enter, press Ctrl+Shift+Enter)... 
    • in C4 input this formula =IF(H4=LARGE(($I$4:$I$26=1)*($H$4:$H$26),1),"1stMostFreq",IF(H4=LARGE(($I$4:$I$26=1)*($H$4:$H$26),2),"2ndMostFreq",IF(H4=LARGE(($I$4:$I$26=1)*($H$4:$H$26),3),"3rdMostFreq","Not In Rank"))) 
    • then you will get the { } round your formula like this {=IF(H4=LARGE(($I$4:$I$26=1)*($H$4:$H$26),1),"1stMostFreq",IF(H4=LARGE(($I$4:$I$26=1)*($H$4:$H$26),2),"2ndMostFreq",IF(H4=LARGE(($I$4:$I$26=1)*($H$4:$H$26),3),"3rdMostFreq","Not In Rank")))}
  4. Format color for easily identify the value status
    1. click at C4 
      •  click menu > format > conditional format> select the value is> equal to > input text="1stMostFreq" then set fill color "red"
      • Add more condition.....and so on...3 conditions
    2. Click at B4 
      • click menu > format > conditional format> formula is > input formula =$C4="1stMostFreq" then set fill color "red"
      • Add more condition...and so on...3 condittions
That's it ... here is the sample file for more easy to understand ---->Fml_ArrayRankFrequencyComplicate.zip

Hope this help.

Pichart Y.