[SOUND] Here we have a data file where we have recorded customers' waiting time before they were able to talk with someone on the phone, and recorded it in seconds. In this file we have 500 observations. So if I were a manager and I wanted to know what is a typical time a person spends waiting before they are able to talk to a customer representative. By looking at the raw data, I will not get a sense of it. So I'm going to use frequency table here to summarize my data. But because I'm looking at quantitative data I cannot just have each number be represented as an individual, I am going to create ranges. So to create the ranges it's very useful for me to know what is minimum observations that I have had in this data. What was the maximum number of waiting time that the customer waited? And basically also knowing how many customers I had altogether recorded in case I didn't know that there were 500. So I'm going to show you all of these, how it's done. To find the minimum, I'm going to use a function called MIN. And MIN would return the smallest number that I give it in a range. So for me, that is going to be in the cell A2 all the way down. So I'm going to hold CTRL+SHIFT+down and pick the entire thing at once. And then press parenthesis, and return. So this gives me a minimum of 0, which means at least there is one observation where the customer waited nothing. They were able to talk to a representative immediately. So then I'm going to look at my maximum here, which works the same way except the function is MAX. I will pick the same range. So it's A2, CTRL+SHIFT+Down, close the parentheses, return. And as I return, you can see that my screen moves, I just need to scroll. So the maximum waiting time has been 300 seconds, so that's 5 minutes. Somebody has waited as much as 5 minutes, at least one customer has waited as much as 5 minutes. The count I would say is equal to count, parentheses, pick the same cells, CTRL+SHIFT+Down and 500. So this tells me that there are 500 observations at the minimum somebody waited 0 seconds and at the maximum somebody waited 300 seconds. So I'm going to create a bin that is going to organize my data in 30 second intervals. So here I am going to be writing 30, so what I am going to do is I am going to include all the customers who waited between 0 seconds all the way through 30 seconds for this observation. For this one I am going to put 60, which means anybody waiting slightly over 30 seconds, all the way to 60 seconds, is going to be included in this bin. And the next bin is going to be 90, so you can see here that I'm going in intervals of 30, and I need to go all the way to 300. So instead of doing that, I can just grab this. And then it automatically will fill it and you will see on the right side there is 120. And if I keep dragging it it's going to be going all the way to 300 and I can wait right here. In my next bin I am going to use something known as frequency. So how many times did this occur? So to find a frequency, there is actually a function which is an array function in Excel, known as frequency. When you say it's an array function, meaning that it's going to return an array to you, not a single number. And since it's going to be an array, it's going to require room to record everything. So I'm going to record all my return values right here. And it's always a good idea to select one extra row incase there is any observation that will fall there. It doesn't hurt to have it, so I'm going to have this included, as well. Now I'm ready to write my function, so I'm going to say it's equal to FREQUENCY, and you would see that the FREQUENCY will show up. So frequency, I will tab it, it needs the data array. So, data array is where your observations are and obviously that's held right here. So I'm going to again press on A2, hold down CTRL+SHIFT, go all the way down so all my 500 observations are taken. And it wants to know, where are the bins. Where have you kept those? So the bins are right here. It starts from 30, and goes all the way to 300. So this is my bin. But in order for me to receive all the values from this array, you have to make sure that you hold down CTRL+SHIFT and Enter, and all the values are shown. So now what it's telling you is the following. There were 30 customers that waited between 0 to 30 seconds, 49 customers that waited between the 30 to 60 seconds, 40 customers that waited between 60 and 90 seconds, and so on and so forth. I can also create relative frequency. Relative frequency will tell me what percentage of customers waited in each category. So for example, 30 customers waited somewhere between zero seconds to 30 seconds, and this is 30 customers out of 500, so basically I need to take that ratio. So, I'm going to say it's equal to 30 divided by. Now 500 is something that I want every entry to be divided by. So, I would like to pick that cell and lock its position. So, as I cut and paste it it's not going to be lost. So, I'm going to press F4 and then location of 500 is going to be fixed. So now if I take this value and just grab it down, you can see that in the first cell it's E11, so this value divided by 500, but in the second one it's E12. So it automatically advances my numerator but it going to keep my denominator at E4, which is locked. Okay, so E4 is locked. So now I can just double click this and I would get the values coming down. So I have now the relative frequency for customer waiting time also. And if I look at this, I can change it if I want in percentages, if that's what I want and I can see that a lot of my customers are waiting on the upper end. So as a customer service indicator, if waiting time is a negative experience this is not good news for any business manager.
