 statistics and excel bell curve people wait example part number one get ready taking a deep breath holding it in for 10 seconds looking forward to a smooth soothing excel first a word from our sponsor yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like our crunching numbers is my cardio product line now I'm not saying that subscribing to this channel crunching numbers with us will make you thin fit and healthy or anything however it does seem like it worked for her just saying so yeah subscribe hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot think of it dot com here we are in excel if you don't have access to this workbook that's okay because we'll basically build this from a blank worksheet but if you do have access three tabs down below example practice blank example in essence answer key practice tab having pre-formatted cells so you could get to the heart of the practice problem of the blank tab blank worksheet with just our practice data so we can practice formatting the cells within excel as we work through the practice problem if you don't have access to practice data you could use resources such as Kaggle dot com you can also try to create your own data to practice with by going to the data tab up top the analysis group and the data analysis tool that we saw in a prior presentation if you don't have this analysis group then you can go into the options and possibly add it as we saw in a prior presentation as well back to the home tab down to the example tab to get an idea of what we will be doing our data is going to be related to weights of people we also have heights if you want to practice with that as well but we're focused on the weights this time measured in pounds now this will be similar to what we've seen in the past there's two or the couple major differences however one is going to be that we have a whole lot more data of weights and weights as you would imagine you would think that because it's something that's related to nature people's weights that it would follow a bell shape type of distribution the more data that we have the more likely that the actual example if it conforms to a bell shape distribution will simulate a bell shape distribution so that's one thing we have a lot more data in this example than prior examples we're also going to be building our graph over here and we're going to use a similar technique but slightly different in the technique to get this between column that we will then use to build our graph trying to come up with some strategies that you might be able to build your graphs fairly quickly so that you can use them when you're working these practice problems and we'll do a little bit more with the Z score calculations and formulas as well alright let's go to the blank tab and get started I'm going to remove the Kaggle thing I'm going to select the entire worksheet this time and format the data before we get started selecting the triangle right clicking on the worksheet formatting the cells I'm going to go to currency because I'm an accountant that's where I like to go I'm going to say negative numbers are bracketed and red and I'll keep the decimals for now let's go two decimals but no dollar signs these are not dollars we're talking about now if I say okay obviously it trimmed up our data over here to just show the two decimals even though it had more than two decimals right now what I'm going to do is we could just hide the heights I want to keep the heights there just because you might want to use those in practice in a similar method you can do basically the same sample problem we're doing here but with the height stated if you want but I don't need it right now so I'm going to go ahead and select columns well let's first put a table around it and then I'll hide them possibly so I'm going to let's go up top make this bold home tab font group bold it and then I'll put my cursor somewhere in this group of numbers and go to the insert tab tables group I'm going to add a table to it the dancing ants are doing their table creation dance looks kind of like a rain dance it's hard to tell but you know it's different because obviously how would how would excel know what to do if they did the same dance for everything you know spending his home tab alignment wrapping the text and I'm going to double click in here and just put some space so I can wrap the text where the space is all right and then I could just hide columns a and b because I'm not using those I'm going to put my cursor on a go over to be right click and hide them just hide it now note that if I was I was going to sort this data somehow possibly I had some blank cells in here where we didn't get the weights or we have some very high or low weights that don't look correct or something like that then I might want to filter out those outliers that might not be correct depending on what I'm doing if I was to do that I would want my data on a separate tab because once I start filtering things it'll collapse the rows so but here I'm not going to do that so I'm just going to keep my data on the same tab here and we'll do our normal calculation so I'm going to make a skinny D skinny D and then we're going to say sounds like a rapper's name or something skinny D skinny D's up throwing down the base beat and doing some licks over it or something anyway here's the mean equals the average tab we're going to select our data now there's a lot of data I'm going to select the data boom and it's going to just pick up that entire table if I go control shift down on this data we're at 25,000 data points so we have a lot more data control backspace and then I'm going to say alright let's do the standard D I'm just going to call it SD for the standard D I'm going to say this equals ST this is the population so I'm going to pick up the population STD EV for the pop and the population not your dad the population the pop that's what I call it the pop and then we're going to say okay and then the median the median the one in the middle hit the one in the middle we're going to say the median tab picking that up boom and then the mode might not always be as applicable if you didn't have a whole lot of data because that shows multiple if multiple items are being hit and because we're using fractions of pounds then it would be less likely but since we have a whole lot of data the mode will probably be applicable here to you let's say the mode single mode just one please I don't need like a whole bunch of modes boom and okay so now I can see that the mean is pretty close to the median which is an indication that this group of data follows somewhat closely to the bell curve and the mode is also there remember the modes kind of kind of tricky because if you didn't have a whole lot of data the mode that might not even have a mode because you have percent because you have these fractions of the measuring in this case of pounds which means that you might not have a lot of duplicates whereas if you rounded them to a pound it's more likely that the mode is going to be more applicable but again we have so much data right now that the mode will work so now I'm going to say all right I'm going to graph this thing out and so I'm going to make a bell curve because it looks like a bell curve would work before I do that let's actually graph let's look at this data in terms of a histogram if I select the entire thing I'll just select the data I just select the data then I'm going to go into the insert and then charts and just make a histogram say what does this thing look like from a histogram standpoint where we have the groups on the bottom and then and then how many and this is the buckets the buckets on the bottom and how many of these numbers which there's a whole lot of them fall into each bucket now the point here is that this time we came up with something that looks a lot more like a bell curve than some of our prior examples due to the fact that we have a whole lot of data here we're using a whole lot of information so if you're looking at something that would conform to a bell curve you would think that if you had the more data that you had the more smooth looking the shape would look in them and the more you can be kind of certain that it looks like it follows a bell curve type of distribution so we're going to go okay it looks like a bell curve kind of does the thing I'm going to move this off to the side and let's start to build an actual bell curve so I'm going to say let's say that this is going to be x this is our x this is going to be p of x let's say I'm going to make this black and white so we're going to go hometown font group making this black and this white and then I'll center it and then let's make a skinny x skin out skinny up the x it's on the lighter side of our bell curve it's it weighs less because it's skinny it's a skinny x but it could be skinny with big bones you know it just has but any case so now we're going to say okay well what am I going to start because I'm measuring in pounds I could start from like one pound and go up to like I don't know 500 pounds or something but it's I don't probably need to go that far because it's not likely that someone weighs one pound so what's the range that I need on my exes that are going to be on the X parameter so I could say well everything let's do that for standard deviations again that should pick up almost everything and for standard deviation so let's go that we're going to say the numbers of of standard deviations which I'm just going to say SDs in our chart the number of exes that we want are going to be equal to well let's four let's just say four of them and so that means that the lower x and then the upper x we can calculate so the lower x is going to be the mean the middle point minus the standard deviation 1166 times four of them to goes to go for standard deviations and we're going to say okay boom so we've got 80 so I so people don't weigh less than 80 oftentimes right and then we're going to say pounds we're talking pounds I know if you're not in the United States you're like what are you talking about or what a pound what in the world but whatever that's how we do it man so now we're going to say the upper one is going to be 127 plus a standard deviation times four and enter so now 173 pounds is going to be our upper end okay and so now let's let's just build this I'm going to say this is going to be an X of let's go from starting point is just going to be one less than this one or 80 you know at the lower end I'm going to get rid of the decimals because I'm not going to do the decimals here I'm just going to take it to the whole pounds and I'm going to drag it let's do 81 so we can see the pattern get rid of the decimals select those two I'm going to drag it down to 174 174 and that should be good so I'm going to drag it down till I get the pattern down to 174 that doesn't even fit me man I'm because I'm yoked up what about my someone like me that's like huge like Arnold Schwarzenegger but not now because he's old but like when he was I'm not you know when he was not old no anyways so now we're going to say P of X we're going to say this equals then norm dot dis so we've seen this in prior presentation so we're just going to take the norm dot dist I won't do the spill function this time I'll just do the normal process I'm going to take the X comma the mean the mean is outside the data we're working in so I'm going to F for it making it absolute so it will copy down and not move that cell down comma the standard deviation is that one again it's outside the data I'm working with I don't want it to move down so I'm going to select F for dollar sign before the F and the three making an absolute comma cumulative not cumulative false or zero and then enter I'm going to percentify it home Dan home tab number group percentify you better percentify if you want to recognize I still don't recognize it still zero but if I double click on the fill handle copies it down and there we have it now if I if I was to total this up total and say alt equals to some trustee some formula we get one or 100 because basically all the data is in there all right so that looks good and so so now I'm going to say okay so so now let's ask well let's see how closely that fits to our actual data now now remember our actual data is actual samples not given to us in percents so I want to either adjust my actual data to percents or adjust this curve to actual data right so first I've got to take all my information here and group it together and that's going to be our frequency so this is going to be actual frequency actual frequency and then I'll wrap that home tab alignment wrap it center it black white so so this is going to be the one where we're going to say the frequency is going to tell us everything for example this one that is above 80 up to and including 81 is going to go into that bucket when we count our actual data all right so we're going to use our frequency which is a spill array function frequency tab I'm just going to select all of our data with the little arrow selecting all of the data comma and then I'm going to select all of my x's by putting my cursor on the top x control shift and down arrow don't want to pick the total so I'm just going to say shift up and then control back space to get up to the top without the dancing frequency ants stopping their frequency dance and then I'm going to say enter and boom spills it on down so these are the number of of occurrences of these datas that are are in between 100 in this case or above 100 up to and including 101 I don't want it to it spilled down a little one more than I wanted it to go so I'm going to double-click here I'm going to get rid of that last one so it spills out right to there now if I sum this up how do I know it picked up all my numbers well if I say all equals down here summing it up comes out to 25,000 and I could say does that equal my count of data right I can check my actual count say how many data points do I have over here imagining these are our bunch of data that we actually counted right so we're gonna say this is going to be count I'm just gonna say count all of my data how many are there there's 25,000 we picked up all 25,000 are represented in one of these buckets so that looks good check confirmed so now I can either convert my percentages to counts by multiplying all the percentages by 25,000 then I can then I can compare my actual to my to my the smooth curve or I can convert which is probably the better thing to do the actual frequency to the percent by taking the percent of the total and I'm gonna say let's go home tab numbers black white rapid center it and I'm gonna do this by saying this equals this number two divided by control shift down I'm just want to pick up that 25,000 so it's just that number two divided by the 25,000 I want it to be absolute so f4 and the keyboard dollar side before the J and the 9 enter I'm gonna percentify this whole column or I could just do this one cell and then copy it down maybe home tab number percent of five so we can recognize and then add some decimals and then double click on the fill handle and boom it copies it on down so now we're comparing these percentages and I could say okay what's the difference between our percentages difference between what we got to what we would have predicted if we used these numbers and multiplied times our 25,000 count or something right or got our percentages so let's go to the home tab font black white alignment center wrap and then difference equals the P of X minus the percent of total I'm going to percentify at home tab number percentify add some decimals double click it down on the fill handle and so you can see the differences are pretty low for the most part so once again indication that indication that this could conform to a bell curve and a bell curve could give us good information about this data okay so now we want to say okay let me graph this thing this actual bell curve I could graph it first to just see what it looks like in terms of our percentages I'm gonna put my cursor here control shift down shift up so I don't pick up the total scroll back up to the top insert I'm gonna go to the charts first I'm gonna just hit the bar chart and let's just check take a look at a standard bar chart there it is there's our bar chart and so now I can say okay maybe I can look at this bar chart and compare it to my actual percent data so remember we did this one over here with with the numbers but now I'm gonna do it comparing it with our percents of the actual data and put them on top of each other so before I do though I'm getting ahead of myself hold on hold your horse you hold your horse your horse is tired it needs a hug so I'm gonna change this bottom bit because it shouldn't start at one it should start at 80 selecting the data chart design data and I'm gonna say selecting the data edit group on the right and then we'll select this item I'm gonna put my cursor on the 80 shift down control or just shift up so that I don't pick that up and then I'm gonna say okay and okay so make sure to check it on this side cuz sometimes it gets a little picky over here and it doesn't pick it up but it looks like it did it so there it goes so there's our smooth bell curve now let's add this on top of it to see our actual data in percent form on top of the bell curve stuff so I could then go to the data and I could say let's add another it's gonna be the percent of the total as the name delete this stuff make sure you it's a little tricky here it gets a little sketchy it's not hard but it's but sometimes excels doesn't do what you tell it to do and you got to be like okay Excel I'm not mad I'm not mad or anything but you're not doing it you're not doing what I said to do and it's just frustrating I'm just disappointed just disappoints me when you do that Excel and then if you talk to it nicely like that then it usually starts to work so that you can see that this lines up pretty close our actual data and that you can also do that with a line graph I probably should show a line graph just to show the difference but that's the idea so now I can say alright now I can make an area chart of it if I wanted to because we're usually thinking about terms of areas not the bar chart so I can take my actual bell curve and say my bell curve is good to go now and let's make an area chart with it so I'm gonna go up top and say I want to insert this time an area thing an area chart not just like a thing and then we'll say okay and so now we have the area and I once again need to change the bottom bit down here so I'm gonna go to my data and then this this one on the right I'm going to then say I want you to be the x stuff here and then the dancing ants do their x axis dance see how it didn't do it see that Excel why I'm not mad I'm just saying like I picked up the numbers correctly and you didn't you didn't bring the x's in there I see what you did pick those you see the you see the dancing ants doing their x axis dance get it right see then it picked it up so you just have to just have to get a little you know you got it you got to watch him you got to watch Excel they mess with you sometimes so there's that so so now now what we would like to do and we'll do this next time we'll continue on to adjust this graph so that we can then ask questions like what if x is you know greater than so much and we'll do the between thing looking a little bit differently and we'll also add the the z scores as well and add those to our graph in the following presentation