Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Frequency Formula
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 -- Thanks very much!! Barrie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Frequency Formula
On Sat, 14 Oct 2006 16:21:01 -0700, barrie
wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Frequency Formula
Barrie -
Maybe you entered FREQUENCY in cells D2:D9. Instead, if you select cells D3:D9 (or D3:D10) 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" wrote in message ... 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 -- Thanks very much!! Barrie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Frequency Formula
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. -- Thanks very much!! Barrie "Ron Rosenfeld" wrote: On Sat, 14 Oct 2006 16:21:01 -0700, barrie wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Frequency Formula
Okay, I reeducated myself on the meaning of the formulas as Ron suggested and
I selected D3:D9 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 "Mike Middleton" wrote: Barrie - Maybe you entered FREQUENCY in cells D2:D9. Instead, if you select cells D3:D9 (or D3:D10) 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" wrote in message ... 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 -- Thanks very much!! Barrie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Frequency Formula
On Sat, 14 Oct 2006 17:07:02 -0700, barrie
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Is it possible? | Excel Worksheet Functions | |||
Can I modify the YIELD formula to use a monthly frequency? | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Using Frequency formula | Excel Discussion (Misc queries) |