Excel’s =frequency function

Topic

New Reply

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

Reply | Quote

Replies

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
to get to edit mode
=FREQUENCY(A2:A33,C2:C24)
Ctrl-shift-enter will confirm the array formula down the column…

Steve

Reply | Quote

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
to get to edit mode
=FREQUENCY(A2:A33,C2:C24)
Ctrl-shift-enter will confirm the array formula down the column…

Steve

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. :^)

Reply | Quote

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

Reply | Quote

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

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.

Reply | Quote

I still don’t understand the question. Are you looking for methodology for how many and what divisions for the bin or what?

Steve

Reply | Quote

I still don’t understand the question. Are you looking for methodology for how many and what divisions for the bin or what?

Steve

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

Reply | Quote

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 +(Max-min)/x” where x would be the number of bins you want. Typically 5-10, 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 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/Histogram#Number_of_bins_and_width) with some formula using different techniques for doing bin sizes and an article at http://toyoizumilab.brain.riken.jp/hideaki/res/histogram.html which discusses optimizing bin sizes.

Steve

Reply | Quote

I will check those out. Thanks for your help! :^) gene

Reply | Quote