Counts and Percentages

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi Folks - I have a column of numbers that represent grades like this:

Col A

9
10
9
12
11
9
10
9
12
11

Etc.

I need to count the number of times 9 occurs, 10 occurs, etc. Then I need to
calculate the percentage of 9's to the whole, 10's to the whole, etc.

I know I can use CountIf to count each occurrence of the individual grades.
Is there a function that will allow you to select a range of numbers and
automatically calculate counts and percentages?

Thanks.

Michael
 
Hi Michael

Here's one way to do it, assuming your grades in A2:A100

1. In D2:D5 enter the numbers 9, 10, 11 and 12
2. Select E2:E6 (one more cell than above) and
enter the array formula
=FREQUENCY(A2:A100,D2:D5)
The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.
3. In F2 enter this formula: =E2/COUNT($A$2:$A$100)
4. Copy F2 to F6 with the fill handle (the little square in the
lower right corner of the cell.
5. While F2:F6 is selected click the %-button.

The "surplus" cell E6 displays how many numbers are larger
than 12.
 
Wow!!! That's very cool ......

Thanks.



Leo Heuser said:
Hi Michael

Here's one way to do it, assuming your grades in A2:A100

1. In D2:D5 enter the numbers 9, 10, 11 and 12
2. Select E2:E6 (one more cell than above) and
enter the array formula
=FREQUENCY(A2:A100,D2:D5)
The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.
3. In F2 enter this formula: =E2/COUNT($A$2:$A$100)
4. Copy F2 to F6 with the fill handle (the little square in the
lower right corner of the cell.
5. While F2:F6 is selected click the %-button.

The "surplus" cell E6 displays how many numbers are larger
than 12.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

need
Click to expand...
 
Can also do it with a Pivot Table

Data
Col A Count of Col A2 Count of Col A
9 4 40.00%
10 2 20.00%
11 2 20.00%
12 2 20.00%




Col A is your row field. Then drag Col A twice into the data field.
For the first data field under field settings select Count; then fo
the second one select Count and then under options select show data a
% of column

Ron Hekier
 
Can also do it with a Pivot Table

Data
Col A Count of Col A2 Count of Col A
9 4 40.00%
10 2 20.00%
11 2 20.00%
12 2 20.00%




Col A is your row field. Then drag Col A twice into the data field.
For the first data field under field settings select Count; then for
the second one select Count and then under options select show data as
% of column

Ron Hekier
 
Back
Top