ExcelBanter - View Single Post - Why does a simple frequency distribution count out of range data?

Harlan Grove

wrote...
I have a frequency distribution that counts the occurance of 1, 2, 3 or 4 in
column C but for some reason if there is a 0 in a cell it gets counted as a
1. Here is an excerpt from the spreadsheet.
....

Read online help for the FREQUENCY function. It doesn't count EQUAL
matches, it counts in bins. If it's second argument were {1;2;3;4}, the
2nd bin would be 1 and <= 2, the 3rd bin 2 and <= 3, the 4th bin
3 and <= 4. Those are clear. There's also a 5th bin, 4. The 1st bin
is <= 1. That is, the bin boundary points map into intervals as
follows.

points _______ 1__ 2__ 3__ 4________
intervals (-Infinity,1](1,2](2,3](3,4](4,+Infinity)

Since 0 falls between -Infinity and 1, it's included in the first bin.
If you only want to count the positive values, use the array formula

=FREQUENCY(IF(C10:C20000,C10:C2000),$P$3:$P$6)