Calculate relative frequency?

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

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


"mlh0654" wrote:

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

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" wrote:

> 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
>
>
> "mlh0654" wrote:
>
> > I am trying to calculate relative frequency using excel 2003, does anyone
> > know how?

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" wrote:

> 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" wrote:
>
> > 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
> >
> >
> > "mlh0654" wrote:
> >
> > > I am trying to calculate relative frequency using excel 2003, does anyone
> > > know how?

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" <[email protected]> wrote in message
news:[email protected]...
>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" wrote:
>
>> 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" wrote:
>>
>> > 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
>> >
>> >
>> > "mlh0654" wrote:
>> >
>> > > I am trying to calculate relative frequency using excel 2003, does
>> > > anyone
>> > > know how?