Frequency Formula

[barrie]

Using frequency, I am trying to calculate how many students received grades
within a particular grade range. I've entered the spefici grades for 15
students in column B (B3:B17) and the grade ranges in column C (C3:C9). When
I use frequency in Column D, it does not calculate it correctly - for
example, it finds 6 students who got a grade between 51 and 60 even though
there are only 4. I used the formula {=FREQUENCY(B3:B17,C3:C9)}. Here is a
copy of my columns and the results. Any suggestions would be greatly
appreciated.

0
79 40 2
80 50 2
56 60 6
72 70 5
70 80 0
47 90 0
57 100 0
63
44
67
76
70
66
74
61

 

[Ron Rosenfeld]

Using frequency, I am trying to calculate how many students received grades
within a particular grade range. I've entered the spefici grades for 15
students in column B (B3:B17) and the grade ranges in column C (C3:C9). When
I use frequency in Column D, it does not calculate it correctly - for
example, it finds 6 students who got a grade between 51 and 60 even though
there are only 4. I used the formula {=FREQUENCY(B3:B17,C3:C9)}. Here is a
copy of my columns and the results. Any suggestions would be greatly
appreciated.

0
79 40 2
80 50 2
56 60 6
72 70 5
70 80 0
47 90 0
57 100 0
63
44
67
76
70
66
74
61

I think you are misunderstanding the logic, and also may have your frequency
formula offset by 1 row.

79 40 0 Less than or Equal to 40
80 50 2 Less than or Equal to 50 and greater than 40
56 60 2 Less than or Equal to 60 and greater than 50
72 70 6 Less than or Equal to 70 and greater than 60
70 80 5 Less than or Equal to 80 and greater than 70
47 90 0 Less than or Equal to 90 and greater than 80
57 100 0 Less than or Equal to 100 and greater than 90
63 0 Greater than 100
44
67
76
70
66
74
61


41-50 44, 47
51-60 56, 57
61-70 61, 63, 66, 67, 70, 70
71-80 72, 74, 76, 79, 80

--ron

 

[Mike Middleton]

Barrie -

Maybe you entered FREQUENCY in cells D29.

Instead, if you select cells D39 (or D310) before array-entering the
FREQUENCY function, the counts will be aligned with the upper bound of each
interval shown in column C.

- Mike
http://www.mikemiddleton.com

 

[barrie]

I am definately confused! I used the same type of formula in my worksheets
last year and had no problem. My understanding is that column c (40, 50, 60
etc) is providing the grade range to count the number of grades in the range
(40 - 49, 50-50 etc.) If I want to count the number of grades in the range,
can you help me set up the data/formula correctly? I REALLY appreciate the
help.

 

[barrie]

Okay, I reeducated myself on the meaning of the formulas as Ron suggested and
I selected D39 as Mike suggested. It's all good - I think. I'll study the
results to see if I can make it make sense (to me) but I think you both
solved it. Thanks very much. If I still don't get it, I'll be back. Thanks
again.
--
Thanks very much!!
Barrie

Barrie -

Maybe you entered FREQUENCY in cells D29.

Instead, if you select cells D39 (or D310) before array-entering the
FREQUENCY function, the counts will be aligned with the upper bound of each
interval shown in column C.

- Mike
http://www.mikemiddleton.com

 

[Ron Rosenfeld]

I am definately confused! I used the same type of formula in my worksheets
last year and had no problem. My understanding is that column c (40, 50, 60
etc) is providing the grade range to count the number of grades in the range
(40 - 49, 50-50 etc.) If I want to count the number of grades in the range,
can you help me set up the data/formula correctly? I REALLY appreciate the
help.

You just need to understand that the Bin EXcludes the bottom of the bin and
INcludes the top of the bin. (It has to exclude one or the other, else there
would be an overlap).

So:

39
49
59
....

should do what you want, I think.

79 39 0 Less than or Equal to 39
80 49 2 Greater than 39 and less than or Equal to 49
56 59 2 Greater than 49 and less than or Equal to 59
72 69 4 Greater than 59 and less than or Equal to 69
70 79 6 Greater than 69 and less than or Equal to 79
47 89 1 Greater than 79 and less than or Equal to 89
57 99 0 Greater than 89 and less than or Equal to 99
63 0 Greater than 99
44
67
76
70
66
74
61


By the way, if there might be decimal grade numbers, you might want to change
the bins to something like:

39.9
49.9
59.9
....
--ron