+ Reply to Thread
Results 1 to 5 of 5

Calculate relative frequency?

  1. #1
    mlh0654
    Guest

    Calculate relative frequency?

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

  2. #2
    Gary''s Student
    Guest

    RE: Calculate relative frequency?

    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?


  3. #3
    Ron Coderre
    Guest

    RE: Calculate relative frequency?

    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?


  4. #4
    Gary''s Student
    Guest

    RE: Calculate relative frequency?

    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?


  5. #5
    Dana DeLouis
    Guest

    Re: Calculate relative frequency?

    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" <GarysStudent@discussions.microsoft.com> wrote in message
    news:29FF79F8-22C7-460F-BD45-8F3FC1025D73@microsoft.com...
    >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?




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1