histogram is counting input data wrong

[Jake]

The histogram analysis is automatically grouping fish lengths into the next
larger bin-class (For example: 20 is considered part of the 20-bin; 21 and
24 are included in the 30 bin). From what I understand it is supposed to
group into the bin of equal or greater size, up to the next bin-class.

Ex. Fish Lengths (20 cm, 21, 24, 30, 32, 40)

Bin is (20, 30, 40, 50)

What it is doing: Frequencies (20 is 1; 30 is 3; 40 is 2, and 50 is 0)
What it should be doing: Freqs (20 is 3; 30 is 2; 40 is 1; and 50 is 0)

Please help!

 

[Gord Dibben]

Excel is doing its job correctly.

It counts equal to or less than each bin number.

Change your bin numbers to 19, 29, 39, 49

You will get 0, 3, 2, 1, 0

Do not forget to add one more cell to the bins array when entering the
formula.

{=FREQUENCY(A1:A6,B1:B4)} array entered in D15


Gord Dibben MS Excel MVP

 

[T. Valko]

Fish Lengths (20 cm, 21, 24, 30, 32, 40)

Bin is (20, 30, 40, 50)

Here's how FREQUENCY works...

Based on your bins:

Count if <=20
Count if >20 but <=30
Count if >30 but <=40
Count if >40 but <=50
Count if >50

So, your results are correct for what the FREQUENCY function does. Now, if
you want to count based on some other interval evaluation, that's a
different story!