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.