Calculate relative frequency?

M

mlh0654

I am trying to calculate relative frequency using excel 2003, does anyone
know how?
 
G

Gary''s Student

By relative frequency, I am assuming that you mean the relative frequency of
the occurance of something in a population. If this is not true, stop
reading.

Let's say you have items in cells A1 thru A100:
dog
cat
bird
dog
cat
bird
dog
dog
dog
cat
cat
cat
dog

We wish to know the relative frequency of dog, cat, bird in our sample
In B1 thru B3 enter:
=COUNTIF(A$1:A$100,"cat")
=COUNTIF(A$1:A$100,"dog")
=COUNTIF(A$1:A$100,"bird")

In C1 thru C3 enter:
=COUNTA(A$1:A$100)
=COUNTA(A$1:A$100)
=COUNTA(A$1:A$100)


In D1 thru D3 enter:
=B1/C1
=B2/C2
=B3/C3
to see:
0.384615385
0.461538462
0.153846154

so cats make up about 38% of the population. The relative frequency of dogs
to cats is:

..4615/.3816 or about 1.2 to 1
 
R

Ron Coderre

Expanding on your excellent example, GS....we could let Excel do the heavy
lifting automatically:

Using the same animal data, but with a column heading (Animal) in cell A1

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select the data
Click the [Layout] button

ROW: Drag the Animal field here
DATA: Drag the Animal field here (it will list as Count of Animal)
dbl-click that field
Click the [options] button
Show data as: % of collumn

Click [OK] twice
Select where you want the Pivot Table and click the [Finish] button

That will list each name and the percent ranks as in the below table.

Count of Animal
Animal Total
bird 15.38%
cat 38.46%
dog 46.15%
Grand Total 100.00%

***********
Regards,
Ron

XL2002, WinXP
 
G

Gary''s Student

A good suggestion Ron...

Using a pivot table means you don't have to know exactly what's in the
column. Could be dogs, cats, birds, fish, flowers, etc. The COUNTIF method
compells the user to create a formula for each case, pivot tables don't.
--
Gary's Student


Ron Coderre said:
Expanding on your excellent example, GS....we could let Excel do the heavy
lifting automatically:

Using the same animal data, but with a column heading (Animal) in cell A1

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select the data
Click the [Layout] button

ROW: Drag the Animal field here
DATA: Drag the Animal field here (it will list as Count of Animal)
dbl-click that field
Click the [options] button
Show data as: % of collumn

Click [OK] twice
Select where you want the Pivot Table and click the [Finish] button

That will list each name and the percent ranks as in the below table.

Count of Animal
Animal Total
bird 15.38%
cat 38.46%
dog 46.15%
Grand Total 100.00%

***********
Regards,
Ron

XL2002, WinXP


Gary''s Student said:
By relative frequency, I am assuming that you mean the relative frequency of
the occurance of something in a population. If this is not true, stop
reading.

Let's say you have items in cells A1 thru A100:
dog
cat
bird
dog
cat
bird
dog
dog
dog
cat
cat
cat
dog

We wish to know the relative frequency of dog, cat, bird in our sample
In B1 thru B3 enter:
=COUNTIF(A$1:A$100,"cat")
=COUNTIF(A$1:A$100,"dog")
=COUNTIF(A$1:A$100,"bird")

In C1 thru C3 enter:
=COUNTA(A$1:A$100)
=COUNTA(A$1:A$100)
=COUNTA(A$1:A$100)


In D1 thru D3 enter:
=B1/C1
=B2/C2
=B3/C3
to see:
0.384615385
0.461538462
0.153846154

so cats make up about 38% of the population. The relative frequency of dogs
to cats is:

.4615/.3816 or about 1.2 to 1
Click to expand...
Click to expand...
 
D

Dana DeLouis

Another reason I like Pivot tables is that it will highlight any stray
typing errors.
For example, the table may have lots of "dogs" & "cats."
The Pivot table may show a count of "dog", "doggs", "cat", or "cattts".
I find it easier to spot these errors in a pivot table. You can then go
back to the original data and make the corrections.

--
Dana DeLouis
Windows XP, Office 2003

Gary''s Student said:
A good suggestion Ron...

Using a pivot table means you don't have to know exactly what's in the
column. Could be dogs, cats, birds, fish, flowers, etc. The COUNTIF
method
compells the user to create a formula for each case, pivot tables don't.
--
Gary's Student


Ron Coderre said:
Expanding on your excellent example, GS....we could let Excel do the
heavy
lifting automatically:

Using the same animal data, but with a column heading (Animal) in cell A1

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select the data
Click the [Layout] button

ROW: Drag the Animal field here
DATA: Drag the Animal field here (it will list as Count of Animal)
dbl-click that field
Click the [options] button
Show data as: % of collumn

Click [OK] twice
Select where you want the Pivot Table and click the [Finish] button

That will list each name and the percent ranks as in the below table.

Count of Animal
Animal Total
bird 15.38%
cat 38.46%
dog 46.15%
Grand Total 100.00%

***********
Regards,
Ron

XL2002, WinXP


Gary''s Student said:
By relative frequency, I am assuming that you mean the relative
frequency of
the occurance of something in a population. If this is not true, stop
reading.

Let's say you have items in cells A1 thru A100:
dog
cat
bird
dog
cat
bird
dog
dog
dog
cat
cat
cat
dog

We wish to know the relative frequency of dog, cat, bird in our sample
In B1 thru B3 enter:
=COUNTIF(A$1:A$100,"cat")
=COUNTIF(A$1:A$100,"dog")
=COUNTIF(A$1:A$100,"bird")

In C1 thru C3 enter:
=COUNTA(A$1:A$100)
=COUNTA(A$1:A$100)
=COUNTA(A$1:A$100)


In D1 thru D3 enter:
=B1/C1
=B2/C2
=B3/C3
to see:
0.384615385
0.461538462
0.153846154

so cats make up about 38% of the population. The relative frequency of
dogs
to cats is:

.4615/.3816 or about 1.2 to 1

--
Gary's Student


:

I am trying to calculate relative frequency using excel 2003, does
anyone
know how?
Click to expand...
Click to expand...
Click to expand...
 
Top