Constructing a relative frequency distribution from a frequency counts table in Excel

Intro

Howdy!  I'm Professor Curtis of Aspire Mountain Academy here with more statistics homework help.  Today we're going to learn how to construct a relative frequency distribution from frequency count tables in Excel.  Here's our problem statement: Construct one table that includes relative frequencies based on the frequency distribution shown below.  Then compare the amounts of tar in non-filtered and filtered cigarettes.  Do the cigarette filters appear to be effective?  (Hint: the filters reduce the amount of tar ingested by the smoker.)

If we click on this icon here, we can see the frequency counts that were given for this problem.  Notice that some of the classes appear for the filtered but not the non-filtered and vice-versa.  All that is saying is that, for the classes that don't appear, there are no counts for that category for either the non-filtered or the filtered.

Typically in solving these problems, I'm dumping data into StatCrunch and working inside StatCrunch.  StatCrunch is actually more clunky for this type of operation.  You can make it work, but, as I say, it's more clunky, so I prefer to dump my data into Excel.  If enough of you comment back and say that you want to see this worked in StatCrunch, I'll make another video working it in StatCrunch.

But as I say, the data (as is) is a little clunky to work with so — wow!  OK.  So this is funky.  Let's try this.  I’d like to just copy you.  Let's just paste you in here.  OK, that works.

Part 1

So now I have my data here in Excel.  I don't need this any more.  Before I start moving things around, I'm going to do a little formatting here so we can see a little bit more of what's actually happening and going on.  The first thing I'm going to do is format the headers.  And let's expand you out, and let's expand you out, and you out, and you out.  All right, so now our headers look a little bit better.

And the next thing I want to do is make another column here because what I'm going to do is actually calculate the percentages that we need for our table here in MyMathLab (or MyStatLab, whichever way you want to call it).  We're actually going to calculate those answers here in Excel and then just transfer them over to help us get the right numbers in the right spot.  OK, I'm gonna line up the categories here.  So see how this category here — 18 to 23 — is at the top for the non-filtered, but 18 to 23 doesn't appear until you get down here for the filtered.  So what I'm going to do is just take those cells and move them down.  So now we're all “even Steven” with that.  And then I'm gonna go and fill in the missing categories here.  I'm gonna do the same thing over here.  OK.  Got ya.  All right.

Now I'm ready to start calculating out.  If I want, I could put zeros in here, because there are no counts for these particular classes.  But that's not really needful.   First thing we need to do is relative frequency is based on the total number accounts for a particular item.  So I'm gonna sum up the frequency counts for the non-filtered cigarettes — 25.  So here I'm actually going to divide each one of these numbers in the frequency column by the total, which is 25.  Here I actually put 25, or if I wanted to I could just select the cell with the sum and then, so I keep the same cell when I copy the formula down across multiple rows or columns, I press F4, and I get those dollar signs.  And that tells Excel we want to keep the cell reference constant when we copy the cell down.  Before I copy this down, I want to make sure I have it in a percentile.  That way, I don't have to do any conversions; I can just flip the number straight from here over into my answer field.

So now this is what I need to look at.  I'm going to copy this down, and there are my frequency counts.  I can do the same thing over here.  Take the sum all of these numbers here, and I can put the same formula in that I did over here.  Percent, we want that to appear as a percent.  Now I could just take you and copy you down.  So now I got the numbers that I need.

Because there's so many numbers here, I want to actually make this a little bit more identifiable.  So I'm gonna put you in there, I'm gonna shade you a little bit something over here, I'm gonna shade you a little bit, center you, and, if I want to, I can actually put a border around it that actually helps me see it a little bit better.  So now all I need to do is just transfer the numbers over.  So 6 to 11 for the non-filtered is this number here (zero), so that's what I'm going to place here (zero).  And then I just place the numbers in one at a time.  Excellent!

Part 2

Now the next part of the problem asks, “Do cigarette filters appear to be effective?”  Well, let's look at our distribution table here.  So we have for the non-filtered cigarettes, the bulk of the frequency counts are here in the higher tar classes.  For the filtered cigarettes, the bulk of the data is in the lower tar classes.   So it appears that the filtered cigarettes have lower tar than the non-filtered cigarettes, generally speaking.  And that's what we want; that's the purpose of the filters — to reduce the tar.  So it does appear that they're effective.  So yes, because the relative frequency of the higher tar classes is greater for non-filtered cigarettes, this seems to be our answer, and it is.  Good job!

And that's how we do it at Aspire Mountain Academy.  Be sure to leave your comments below let us know how good a job we did or how we can improve.  And if your stats teacher is boring or just doesn't want to help you learn stats, go to aspiremountainacademy.com, where you can learn more about accessing our lecture videos or provide feedback on what you'd like to see.  Thanks for watching!  We'll see you in the next video.