Excel Video – Frequency Distribution for Soft Drink Page 39

Excel Video – Frequency Distribution for Soft Drink Page 39


Hi everyone. This is Professor Huang. Today, I would like to use this opportunity
to demonstrate on how to use Excel to create a Frequency Distribution Table. The data file you’re going to use is essentially
based on page 39. The “Soft Drink” example. First of all, how do you plot the data? Let me turn the page back to the “Student
View.” So, what do you see is the first thing here
on the “Getting Started.” You’re going to see “Excel Data Files Associative
with Chapters.” So, click that. It says “Open with Windows Explorer.” So, I click that. Then you can see all the data files associated
with this textbook. It has been put in here. By the way, in your “Case Studies,” on all
other assignments, the data file could be stuff on why this mattered. So, click Chapter 2 and find the “Soft Drink”
data file. So, this is the data file. Let’s open this data file. So, what you’re gonna see is the, the, soft drinks. Their file brand. And, uh, we have a total of about 50 observations. So let’s create the table as if we created
the table exactly how it is on the page 39. So, first of all let’s type “Soft Drink.” And I would like to copy the soft drink brand
and paste it over here. The reason I don’t want to tap is because I worry about what if I make a mistake? By miss step one single item? The Excel won’t identify this file, this particular
brand. So once that’s done, then we have this. Okay? I want you to use an Excel function to demonstrate
how to get the frequency distribution. This is what we have…we can see…and the
function I’m using is called “COUNTIF” function. “COUNTIF” function can basically ask you to
select the data range which is A:2 – A:51. So, I got all the data range and then find
and then the correct here means that when if the Excel finds out any observation which
has similar spell, like in this cell C:2, that says Coca-Cola, it has the exact same
text, then it will count as one observation. Then Excel can automatically use this function
to identify how many similar Coca-Cola observations exists among this data set, this data file. So, that essentially gives you the answer
19. However, that’s not the end of the story. If you notice, if you read page 39, you’re
going to notice that before this column and it was before the row, it has a “$” inserted
before that. The “$” in Excel essentially is designed to
fix the range of the data. So that when you copy the formula and paste
it to the other cells, this data range won’t be changed. So let’s do that. By the way, there’s a shortcut that exists
for you to insert a “$.” If you have the F4 key on your keyboard, F4, then you can press
that automatically it should add the $ before the column and the row. So let’s click… Press enter. And now we are ready to copy the formula. Paste to the other cells. We can essentially duplicate what we observed
on page 39. The last issue is if you turn to the next
page you can also create the relative frequency as well as the percentage of frequency. Before we get to relative frequency, we have
to do a summation. So, let’s do total observations and do a summation. Alright. It tells me that we have 50 total observations. And then the relative frequency which is 19
divided by the total number of observations for the same reason the total number of observations
does not change. So actually want to fix it. And in turn, top of the formula to the end. And so we automatically generate the relative
frequency for each type of brand. The percentage frequency, relatively speaking, is simple. Using the relative frequency times 100. So….
Copy the formula. Paste it. Again, you can double check whether you get
everything right or not, by do a summation of the relative frequency, and if it shows
equal to one, then it means that’s correct. And from this we can do the same thing for
the summation of percentage frequency. And my goal is equal to 100 and so that essentially
duplicates the results on page 39. Thank you for watching. And we’re going to see you soon. Bye.

Leave a Reply

Your email address will not be published. Required fields are marked *