Results 1 to 9 of 9
Thread: Excel's =frequency function

20110825, 10:11 #1
 Join Date
 Feb 2010
 Location
 Coon Rapids, Mn
 Posts
 201
 Thanks
 22
 Thanked 4 Times in 4 Posts
Excel's =frequency function
I have been trying for several days now, though the last couple have been on and off, to make =frequency work and simply cannot make it do what I need. I have MS step by step and have tried countless permutations, none of which give me what I want. And when it comes to things like this I'm like a dog with a bone, can't leave it alone until I am finished with it.
So, basic issue is I have a list of numbers in column A, some of them repeat, what I've done with them is create histograms which only gives me 5 bins none of which are useful, used rank and percentile to create a chart that is more useful, but what I want is a separate list, grouping the numbers and showing the frequency with which they appear in column A.
So if column A has, for example, 0, 0, 0, .5, 1.3, 1.7, 2.0, 2.0 I want a chart that shows
Numbers Frequency
0 3
.5 1
1.3 1
1.7 1
2.0 2
=frequency should be the function that does this but I simply cannot make it work, at most I get 0 3 in two columns. It is an array, I am using it correctly, but I am not getting the result I need. {=frequency(a2:a33,c2:c24)} doesn't produce the result shown in MS's own step by step. So, any ideas on how I get actually two columns out of that formula? Appreciate any and all thoughts. And will try them all until I get what I want, lol. :^) gene

20110825, 13:45 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I presume your column A has values from A2:A33 and your bin (0, .5, 1.3, 1.7, 2, etc) is in C2:C24.
Select d2: d24
<F2> to get to edit mode
=FREQUENCY(A2:A33,C2:C24)
Ctrlshiftenter will confirm the array formula down the column...
Steve

20110825, 14:40 #3
 Join Date
 Feb 2010
 Location
 Coon Rapids, Mn
 Posts
 201
 Thanks
 22
 Thanked 4 Times in 4 Posts
That may be what I'm doing wrong. The only way I can create a bin column is through the histogram, right? Which gives me 5 cells plus more. I've not found a way to expand that, it gives me frequencies but not the sort I'm trying to get in 20% chunks. I tried making the bin 24 cells, which is the actual number of unduplicated numbers (some appearing more than once in A2:A33) but got nowhere with that. And when I create the array down cells d2:d24, only the first cell has data, tells me the first three numbers are 0 which is correct, but the rest of the cells are blank. As is my mind wrestling with this thing. I could do what I want manually, the data set is small enough, but I want Excel to do it, or to know how to make Excel do it. Stubborn is in my dna, lol. :^)
Last edited by genej313; 20110825 at 14:42.

20110825, 18:04 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I am not sure I understand your question. You define the bin values for what values you want to display in the Histogram. The frequency function looks in the dataset and counts the values for the bins. The histogram would be created by charting the bins you create and the values from the frequency function.
Could you provide an example worksheet with a dataset and the bins setup as desired?
Steve

20110826, 07:13 #5
 Join Date
 Feb 2010
 Location
 Coon Rapids, Mn
 Posts
 201
 Thanks
 22
 Thanked 4 Times in 4 Posts
Sure: This example has bins in column B that I created manually. And the frequency function works on it. What Excel does when I try to create the bins through Histogram and Rank and Percentile is not what I'm looking for. Maybe the real question is how do I create the bins in column B through Excel, because in the histogram next to it, I did define the output range the same size as column b, but what I got in return was the standard bins, which aren't what I'm looking for.

20110826, 08:55 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I still don't understand the question. Are you looking for methodology for how many and what divisions for the bin or what?
Steve

20110826, 09:46 #7
 Join Date
 Feb 2010
 Location
 Coon Rapids, Mn
 Posts
 201
 Thanks
 22
 Thanked 4 Times in 4 Posts
Sorry. Yes. In the example, I manually typed in the values in column b, eliminating duplicates, then used the frequency function to calculate column c. My reading of the Help pages and MS step by step example leads me to believe that Excel can create column b for me as a bin column that Excel will recognize. But I've found no way to do that but manually. Which is fine for small data sets, but as you may surmise, I intend to use this function on much larger data sets. The only way I've found to have Excel create bins is through the histogram which gives me 5 and is useful for charting and in the rank and percentile which is also useful. But what else I am looking for is a way to have Excel create what I manually did in column b and have yet to figure out how to do that. When I created the histogram, I defined, highlighted the output range to be the same size as column b, but got what you see instead. So, yes, I guess it is methodology that will have Excel create column b for me, so I can then use the frequency function accurately. The bin column has to be there or you get an error message saying the bin column can't be empty, and then I just loop back. Manually creating column b was easy for this size data set, but is impractical in data sets with several hundred rows, you know? So that is what I'm looking for. Thought about countif, but I don't think that will work either to create the bin column as in column b that I want Excel to create. I've tried a LOT of permutations but the only one that has worked is me creating column b myself. And that can't be right, or the only answer, Excel must know how to do this else the frequency function itself would be useless. I just haven't tipped on how to create that column b data using Excel. :^) gene

20110826, 11:17 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
It depends somewhat on the number of data points and the range in values. The smaller the number of data points the fewer bins I would use.
If your distribution is not skewed too much and is relatively normal, you can set the first bin to the min and make each successive bin the "previous bin +(Maxmin)/x" where x would be the number of bins you want. Typically 510, or you can look at some nominal increase in bin size (2, 5, 10, 25, 100, etc) depending on what the range in the numbers is.
If your distribution is skewed, you may want to divide up the central pieces and have a <y and/or >z as the first and final bins to collect the small tails in the distribution instead of having a larger number of bins.
There is an article on Wikipedia (http://en.wikipedia.org/wiki/Histogr...bins_and_width) with some formula using different techniques for doing bin sizes and an article at http://toyoizumilab.brain.riken.jp/h...histogram.html which discusses optimizing bin sizes.
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
genej313 (20110826)

20110826, 11:37 #9
 Join Date
 Feb 2010
 Location
 Coon Rapids, Mn
 Posts
 201
 Thanks
 22
 Thanked 4 Times in 4 Posts
I will check those out. Thanks for your help! :^) gene