Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mlh0654
 
Posts: n/a
Default Calculate relative frequency?

I am trying to calculate relative frequency using excel 2003, does anyone
know how?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dana DeLouis
 
Posts: n/a
Default 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" wrote in message
...
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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
histograms - frequency and relative frequency? confusedstudent Excel Discussion (Misc queries) 2 February 8th 06 09:20 AM
How do I create a Histogram for relative frequency distributions? pipereed Excel Discussion (Misc queries) 1 February 28th 05 04:40 AM
Frequency for Histograms in Excel Jim Charts and Charting in Excel 7 February 24th 05 08:33 PM
how can I create a histogram with relative frequency? phong Excel Discussion (Misc queries) 1 November 30th 04 09:24 AM


All times are GMT +1. The time now is 09:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"