 Okay folks, this is done again on that homework problem 2.1.37 in which we have to take raw data and develop a frequency distribution and then also a relative frequency histogram. This time let's do it in Excel. So if we go here and look at our little icon, we're going to open the data set, click on the other icon and open in Excel. In my particular Windows configuration, it gives me a little icon down here that shows it's downloaded and I have to double click that to open up Excel. Okay, I've got Excel open, it was a little slow to open there and it comes up and it gives you the data in one column and we, as I said in the other video, we're going to have to do some things manually and Excel requires that. So let's look at the first part of this that I've already done some of the work but I will go through and show you how I did it. The problem says that the number of classes is five and that determines a lot in this problem. We need to know the minimum value in our data, the maximum value, the range. We need to calculate the class width and we can do that using Excel functions. If you get the minimum, it's just, I'll just show you, equal min, double click that to select it. We need our range, enter and that gives our minimum value. Similarly we use the max function to get the max of 73. The range is nothing more than max minus min of 21. Width is equal to the range, 21 divided by five. The number of classes gives us a width of 4.2 and of course when we're doing histograms we want whole numbers so logically we'll round up so that we'll have a class width of 5. The next thing we have to do is to come up with these lower and upper limits for the classes and the way I do that is I start the lower limit for the first class as always the minimum and I just put equal d2 and then the lower limit of the second class is equal to the lower limit of the first class plus the class width and that gives me 7 and then you can just copy that formula down to get the lower limits for each of the five classes. The next thing you need is the upper limit for each of the five classes and do that I use a little formula so I can copy it down. I'm saying that the upper limit equals d8 which is the lower limit plus the class width minus 1 and that gives me a value of 56. Using the f4 key when I key that formula in there I lock that cell reference to d6 which is the class width and I can just copy that formula down and so I've got my upper limits for each of the classes which matches that in the problem. Once we have this information we next need to come up with the frequency distribution and we can do that using the Excel data analysis tool pack. I think I've got a video there that shows you how to install the data analysis tool pack but if you click on the data tab and then the data analysis I'm going to go down and select histogram click OK and it's asking us for the data so we highlight that click the little icon and then my bin range the bin range in Excel is the upper limit so I highlight that range click the little icon I'm going to have an output to a new worksheet and I check chart output and also double check that we since we don't have labels that's unchecked but do check chart output and click OK and we come up with our frequency distribution that we need to answer the question 45951 which is over here. We had our total if we just want to equal SUM total of is up 24 and that's the data we need and we also got this roughed out histogram. It's not really a histogram technically because we've got these spaces between the bars but it gives you the shape of the histogram for the frequencies and since the relative frequencies are proportional this will give you the approximate shape of the relative frequencies as well but just to show you how we can make this into a better looking histogram click on one of the columns and the format data series opens up and down here we want to reduce the gap width to zero and then secondly we want to go here to the fill border we want a solid line let's pick make sure it's black and so now we've got our basic histogram there that is usual in some problems. The next thing we need to do though is to come up with our relative frequencies. OK the next thing we need to do is to calculate our relative frequencies and then to convert them into a histogram so I'm going to just move this chart out of the way a little bit and I'm going to add a column here that I'm going to call relative frequency and it's really pretty straightforward it's just equal to the frequency in that bin divided by the total number and then I'm going to lock that by hitting F4 I want to convert that to a percent and then I'm just going to copy that down so there are our relative frequencies 17, 21, 38, 21, and 4. Now we want to add those to this chart and the way we can do that is just click on the column right click select data and we are going to unclick the frequency and we're going to add series name click that little icon relative frequency click that to put loaded in click this icon put our values in load that and click OK and OK now we've got our basic chart there and I'm just going to format that again by clicking on it going over here to format the data series and I want to solid line black OK then I'll close that so there is our histogram it's not technically finished because we don't have the bin labels on there but we know from our part over here that our bins go from 52 to 56 and then 72 to 76 so you can keep that in mind as you've got your 5 bins and you can actually add that label if you want but this is enough let me do one more thing here I'm going to add data labels to make it a little bit easier there and this is a histogram now if we look at what we have in the my stat lab to choose from the only one that looks close is C Charlie and you can approximate there that we've got our 5 bins and 17, 21, 38, another 21 and a 4 that looks pretty good so maybe a safe bet then answer the last question which bin has the greatest relative frequency well that's the one in the middle and that would be 62 to 66 the one with the least relative frequency is bin 5 which is 72 to 76 so there's a way around that and you can take a little more time and actually label this to match that exactly but to get the answer you don't really need to do that so I hope this helps