Why does a simple frequency distribution count out of range data?

[Guest]

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.
Row Col P Col Q Formula in Q3-Q6 =FREQUENCY(C10:C2000,$P$3:$P$6)
3 1 77
4 2 113
5 3 48
6 4 0
All of the data in columns A-L is imported each month and C10:C2000 contain
either text (as part of merged cells) or 1-4 formatted as "General" or are
blank. I have tried reformatting C10:C2000 as numbers but it makes not
differance. Does anyone know what is happening and how to correct for it?

 

[Harlan Grove]

(e-mail address removed) 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:C2000>0,C10:C2000),$P$3:$P$6)

 

[Biff]

Or use Sumproduct which is easier to understand for most people.

Biff

(e-mail address removed) 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:C2000>0,C10:C2000),$P$3:$P$6)

 

[Harlan Grove]

Biff wrote...

Or use Sumproduct which is easier to understand for most people.

COUNTIF would be easier and more understandable still.

FREQUENCY was meant to deal with fractional valued data points, not a
handful of integers. For what it's meant for, its behavior is necessary.