Transcript:
Hi, everybody, this is. Eugene, Oregon lecturer in computing at the National College of Ireland and welcome to my series of short How-to videos in this video, we’re going to learn how to plot a frequency distribution histogram using Excel 2010 So let’s say I have a series of data like I have here in the first two columns a and B on the left hand side of my spreadsheet economy. I’ve got students in this case represented by letters of the alphabet and the grades that they’ve scored in tests and what I’d like to be able to do is display as I’ve shown here a frequency distribution histogram to show me and how many grades are falling within certain bands. And I’d also like to see if my chart represents a normal distribution like what you see here, It’s almost normal for my frequency distribution histogram. So how do we draw this? This histogram? Let’s delete this one here and build one up from scratch. The first thing I need to do is to sign two intervals that I’m going to use and in his family showed you My interview intervals where of 5% we call these intervals and in frequency distribution bin limits. So I’m going to type that in here as a heading. Make it bold as you can see, it’s a little bit easier. I want to start out with zero and I want to do increments of 5% so I’m just going to, as easy format in here equals and the cell with zero in it plus five, and I want to copy that value down until we reach a value of 100 so these are my and limits for the intervals that I’m going to use on my Instagram, the next thing, the next column what I’d like to do is add in the frequency, so just going to type in the heading frequency here, and I want to opposite each of the values each of my bin limits and plant the number of grades that we see over here within each limit are within each live at interval bound. I’m going to use a frequency formula to do that. I could, of course, add them up myself and put them in manually, But I want to use a formula to do this because this is a short piece of data. Imagine if you had. Hundreds of grades listed here. So what I want to do Is in this first cell here. I want to put in the frequency. How many that are grades a car in the range zero up to five and to do this? I’m going to choose the formulas ribbon across the top of my screen here and the function I need is not listed, so I need to go to the more functions option here towards the center select that there are five options here and the one that I need is the statistical option. I’m going to scroll down then until get to the letters F and select the option for frequency, so I can put that in here and this immediately displays the function arguments window. So what I need to be able to tell? My formula is where the data are and want the intervals that I want to use our in other words, the bin limits, so let’s select the data array first of all that’s simply these values over here, so I want to use the Select tool. Click on the first cell, use the shift key and click on the last cell to select all data within my range and in the function arguments window. I got b2 to be 27 listed here in the bin’s array. Well, that’s these values listed over here, so I can just go and select those again, select the first value and shift-click to select an ask value. And I’m now gosh, in my frequency function, arguments window and both arrays that I need the data representing the grades and the bin’s array representing my interval. Bittan limits over here. All right, go ahead and just click, okay. You can see that the frequency. The number of grades occurring in the range 0 to 5 is zero. Thank goodness for us and nobody has scored that low in my test, so I can’t now just copy this formula down as it would normally do copying formulas in Excel. I need to do something a little bit different, and you need to watch carefully at what I do here. The first thing I’m going to do is I’m going to select all the empty cells here. All the values that I want my frequencies to be displayed in so highlights. Are the entire column here next? I’m going to click on the frequency formula in the formula bar up here at the top, and you can see. Excel highlights the grade values and the bin limits for my formula. I’m going to very carefully then and listen to this. I’m going to enter the ctrl shift and enter keys all together and what that does is. It fills out my values here for each of my bin limits If you’re using a Mac by the way, it’s the command and return key combination that you need to do so all. My values have been filled in here now. I’m in a position to plot my graph. So what I’m going to do Then is select the heading and all the values here. I’m just doing normal, creating a column select 2d column here, and I have now created my histogram and this is close to what I had in the early part of the video now. Only one or two more things to do first of all. I’m going to just delete the label over here to make my dog. I’m a little bit bigger, but you can see, and I’ve got my frequencies on the left-hand side, but you can see across the bottom here. I don’t have 0 to 100 to represent presents that I was hoping to have. It’s going from 1 to 21 in this instance here, so I want to select a X X axis with my left mouse button, and then I’m going to choose the right mouse button and select the select data option here and what this does is. It allows me to change the values in on the X Axis in my chart and over here on the right hand side, there is a horizontal axis label and you can see this starts out with labels 1 2 3 4 5 and it goes up to 21 representing the values that I’ve had on my chart and I need to change this. So the edit button here allows me to do that, So click on the edit button. I need the access labor range, so I’ve got the Select tool here in the right-hand side. So what I want to do is select that and put in my bin limits for the X-axi’s, So I just select the first to the end there continue that and click OK to finalize my frequency distribution histogram. You can see across the bottom. I quote my range is from zero to a hundred on the wax on the left hand side. I’ve got the frequencies for each one. I’ve got an almost normal distribution with my charts here with the center high point around about the 55 to 60% eyelet marking great. So that’s how you create a frequency distribution histogram in Excel 2010 I hope you found this video useful. Thank you for your attention.