Rounding up - number data and frequencies

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi-

I am trying to capture the frequencies of a data set of numbers. The numbers
were recorded with one decimal point, and I am only trying to capture whole
numbers.

I have 11,300 records, and I would like to avoid manually rounding these
numbers to the nearest whole number, but I need an accurate frequency count.

How can I round up these numbers and count them specifically? (Rather than
count the original data set that contains something similar to 13.1, 13.2,
13.2, 13.6, 13.8, etc.)

Thanks!
 
Say your data is in column A.

In B2 enter:
=ROUND(A2,0) and copy down. For example:

raw rounded
5.4 5.0
6.6 7.0
1.2 1.0
1.1 1.0
9.5 9.0
7.8 8.0
3.5 4.0
0.4 0.0
1.6 2.0
1.8 2.0


The select column B and create a Pivot Table. You should see:

Count of rounded
rounded Total
0.0 1
1.0 2
2.0 2
4.0 1
5.0 1
7.0 1
8.0 1
9.0 1
Grand Total 10

Each distinct item appears in the Pivot Table and next to it the frequncy of
occurance.

For Pivot Table info see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
Not sure it makes any difference.
In A1:A22 I have 12, 12.1, 12.2 ....13.9,14
In my bin (C1:C3) I have 12,13,and 14
The formula =FREQUENCY(A1:A21,$C$1:$C$3) gives the counts 1, 10,10

In B1 I have =ROUNDUP(A1,0) and this is copied down to B22 giving 12,
13,13,13,13,13,13,13,13,13,13,14,14,14,14,14.14.14.14.14.14
The formula =FREQUENCY(B1:B21,$C$1:$C$3) gives the same counts 1, 10,10

Or have I missed the point?
best wishes
 
Back
Top