When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Relative and cumulative frequency distributions. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "In this video, we will build a relative frequency distribution and a cumulative frequency distribution using a frequency table. This data set represents 51 customers of a grocery store who have responded to the question, 'How many times per month do you visit our store?' You can see the raw data in column E under the Times header showing that, for example, we have six customers who reported visiting the store once per month. The information from column E has been transformed into a frequency table. The column Bins represents the answers that survey respondents gave—the number of times they visited. The Frequency column is how often each of those answers was given by the 51 respondents. Additional helpful information can be drawn from the frequency table. For example, if we wanted to know what percent of the total is represented by four visits per month or we might like to know did any responses occur less that 5% of the time. Relative frequency will answer these questions. It turns out to be easy to add this type of information to our table. For relative frequency, let me go ahead and add the column header."
Relative frequency is entered into cell J1.
Narrator: "We're going to look at what percentage of the total responses is represented by this frequency of six or this frequency of three. To do that, we need to sum this column of frequency. Excel provides us a way to do that automatically with this sigma."
The sigma button is found in the Editing section of the Home tab.
Narrator: "If you click it, it will insert the sum of the data that's above it. I'll hit Enter, and it puts in the answer. Relative Frequency would then be six divided by 51. I'll enter that as a formula. Equals the cell of I2 divided by this total."
The formula in cell J2: equals I2 divided by I16.
Narrator: "Now, I'd like to drag this down the column rather than having to enter the formula each time, but if I drag it down the column, my denominator and the numerator cells will iterate. I want that to happen in the numerator. I want it to point at, first, the frequency result for Bin 1 and then for Bin 2 and 3, but I don't want it to move away from this total in the denominator. So, I want to lock this cell. If I click back in my formula, put the cursor right after the I16 cell reference and then, on a Windows machine, it's the F4 key. When you press F4, you note that it puts dollar signs around the I and the 16. If that doesn't work, you may need to push the FN or Function key and they press F4. On a Mac, it is the CMD, or Command key, and the letter T. Once I've done that and I hit enter, the next thing is that I'd like to make it a percent rather than having it in decimal format."
Cell J2 is selected. The Percentage button found in the Number section of the Home tab is clicked to change the number from a decimal format to a percentage.
Narrator: "Now I can grab the square in the bottom-right and drag it down my column."
The bottom right corner of cell J2 is dragged to row fifteen to automatically apply the formula to each cell in the range.
Narrator: "And if I've down this correctly, the frequency column will add up to 100%. And it does."
The bottom right corner of cell I16 is dragged to J16 to copy the formula over. One appears in cell J16.
Cumulative frequency is entered to cell K1.
Narrator: "The cumulative frequency column will tell me, for any Bin value, the total amount of data up to that point is X percent. We'll start with the first cell."
Equals J2 is entered in cell K2.
Narrator: "And then, I can't yet drag it down the column, because now I need to create my formula. I'm going to look at what is the value of Relative Frequency for this Bin value. And then I'm going to add to that the total of the Cumulative Frequency column above it."
Equals J3 plus K2 is entered into cell K3.
Narrator: "Now I've created a cumulative frequency for 1 and 2, which is 18%. And now that I have a formula in the cell, I can drag it down the column."
The bottom right corner of cell K3 is dragged down to cell K15.
Narrator: "To check that you've done this correctly, the final value should be 100%. Let's answer some questions using the relative and cumulative frequency distributions. What, if any, responses occurred less than 5% of the time? We can see that 8, 9, 10, 11, 12, 14, and 15 are all less than 5% of customers reported that many visits per month. How many customers visit less than 4 times per month? Or what cumulative percent is represented by visits less than 4 times per month? In that case, we're looking at those who visit once, twice, or three times per month, and the cumulative frequency for that is 27%. In this video, we have demonstrated how to create relative and cumulative frequency distributions in Excel."
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.