 statistics and excel bell curve people wait example part number two 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 this CPA thinking cap for example CPA thinking CAP you see what we did with like with the letters and this CPA thinking cap is not just for CPAs either anyone can and should have at least one possibly multiple CPA thinking caps why because based on our scientific survey of five people all of whom directly profit from the sale of these CPA thinking caps wearing this CPA thinking cap without a doubt according to the survey increases accounting productivity tenfold yeah at least yeah apparently the hat actually channels like accounting energy from the quantum field ether directly into your head allowing you to navigate spreadsheets faster it's kind of like how in like the matrix when neo learns kung fu or at least that's what the scientific survey saying so get one because the scientific survey participants could really use some extra cash 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 basically built this from a blank worksheet but we started in a prior presentation so if you want to start from a blank worksheet you may want to begin back there if you do have access to this workbook three tabs down below example practice a blank example in essence answer key practice have having pre-formatted cells so you can get to the heart of the practice problem the blank tab is where we started with a blank worksheet that just had our beginning data on it so we can practice formatting the cells within excel as we work through the practice problem we're continuing on it at this time quick recap of what we have done thus far our data is on the left related to weights of human beings we're imagining that these are a bunch of data points we have a lot more data points in this example problem than we have seen in prior example problems if i hit control shift down for example goes down to 25 thousand uh data points now we would imagine that something that's from nature such as weights of human beings would conform somewhat to a bell curve and the more data points that we have about something that you would think would conform to a bell curve the more likely that if we graph just those data points we get something that looks like a bell curve and we see that over here so it already looks like the shape is pretty close to a curve when we looked at that data and then we calculated our mean our standard deviation our median we saw that the mean is pretty close to the median and the mode which is another indication that it may conform to a bell curve we then plotted the actual bell curve using x's lower x and upper x determined by four standard deviations away used our norm dot dist function and that helped us to graph this one which gives us the area under the curve uh and instead of a bar graph we compared that to the actual data as a percentage of the total and saw that the difference is pretty small so we're pretty confident now that the bell curve can give us some predictive power so now we want to ask some other questions such as well what if the area of the curve was lower or below some certain weight or below above a certain weight and we want to get to our area graph over here to make it a little bit more fancy to ask different kinds of questions and we'll ask a few more a little bit different questions than we've seen in prior presentations and we'll format this graph a little bit differently than we've seen in prior presentations as well all right so i'm going to get rid of the title from it notice that to build this graph all we did was select this data and then we added we went to the insert and charts and we added an area graph and then on the area graph we changed the x axes to pick up our x axes here by selecting the graph charts and we went to data and we changed these axes to pick up our x's so now let's add a little bit more information we might want the z score so we can add the z to it and that would be an horizontal or the z which is how close we are to that middle point the mean or how far away we are however you want to look at it so i'm going to say this is going to be z and let's make that black white alignment we'll center it or wrap it and center it we don't really need to wrap it because it's only one letter not much to wrap right there it's like a present that of a grain of sand or something that you're trying to wrap for christmas and there's not much to wrap you know what i mean because it's really small so then we're going to say all right the z score is going to be equal to brackets the x right here minus and we're going to go on over to the mean so our individual x minus the mean close up the brackets divided by the standard deviation that's our z scores now i need to pick up these two numbers make them absolute because they're outside of the data that we are working on and we don't want them to move down as we go down putting my cursor in f2 selecting f4 on the keyboard dollar sign before the f and the two putting my cursor between f3 f4 and the keyboard dollar sign before the f and the three and enter there's our z score i'll open it up a bit put my cursor on the fill handle and double click it to drag to to drag it down not really dragging it's just like a button we're just button down button it down and then of course down here at zero that is where we would be equivalent to the mean right so so right here it gets to zero between between 127 and 128 which 127.08 is the mean so now we're measuring that that way let's add the z score to our graph now we can't really add another like it's what i want to do first is add another basically data point so that i can in essence assign the z score to that second data so let's ask another question before we add the z so i'm going to go back on over here and say all right let's say that we ask a question like this we're going to say p of x is greater than or equal to 133 let's say greater than or equal to 133 and i can also do that in terms of a z score as well so so let's first do this i'm going to put my cursor on column h and i'm going to add another column i want another column here so i'm going to put my cursor on column h right click this select area and insert and it takes a little time because we have a lot of data it made a skinny h i'm going to make g a little bit wider here now what i'd like to do is make this dynamic so that 133 will change automatically to do that i'll put the 133 out here and then i'm going to make this fancy i'm going to get rid of the decimals i don't really need i'll keep the decimals then i'll go into here double click and i'm going to make a fancy label so i'm going to put an equals at the beginning this is all text so i'm going to put quotes around all the way past the equal sign that's just text so in the quotes there i want to tie it to so tying it together is the end you can see it looks like a knot right it's like how you tie your shoe it's like all messed up that laces are all messed up there it's a knot and then so we're tying it together and then i'm going to say that this needs to be not 133 but i'm going to select the 133 put my cursor down and pick up this cell you can't see 133 in it but it has 133 in it and then finally i also want to tie it together with the shoelace and and and then quotes around this last bit quotes around it because it's another text field and now we have a dynamic formula there cool all right and then we can also say that if that if i'm looking at that x what would be the related z so i could say the related z if i want to look at it terms of z's would be equal to brackets this x minus and then i'm going to pick up the mean brackets divided by the standard deviation and then enter so that's the mean so i can also then say i want to pick up p of z which is greater than or equal to point five one close it up and this time again i want to make it dynamic so this changes with this so i'm going to double click in here and do the same thing equals i'll do it a little bit faster quotes everything text up to here quotes tie it together with an and not and then we want that point five one i'm going to select that and pick it up instead with a formula right there after that i want another not another and not and then quotes around that last bit because it is text and enter and now notice it comes out with something funny i don't want all those decimals i need to round it so i'm going to double click on it and do another fancy formula in front of this one say round round it and then and then i'm going to round it and then comma to how many decimals to close it up so now that whole bit is rounding it and there we go the fanciness is like unending it's unending fanciness okay so if i was to look at that if i was to go over here and say one thirty three uh over here i already did like what's what's the likelihood of just one thirty three three point oh one greater than would be everything from here down down to here you know it adds up to thirty two but that's not exactly going to be what's over here because we're using uh area under the curve so let's do the formula now this is going to be equal to norm dot dist and i'm going to say the x is now going to be this number comma the mean is going to be one twenty seven oh eight comma standard deviation eleven sixty six and this time i want it to be uh cumulative so it needs to be cumulative now if i was to say cumulative it's going to take everything up to that point so you could see what if i put a one there it takes everything up to there and what i really want is to take one minus that amount this is where it's right i want to i want to say so i'm going to click on this and say i want everything above that so i need to go before the formula one minus one being a hundred percent minus that amount gives us the the thirty let's go to the home tab number group percentify it adding some decimals and you can kind of approximate it right you can say okay i wanted everything above so if i was looking at like one thirty three and i added these and i'd say everything above there is like thirty two it's not going to be exact but it's it's if i came up with sixty you know you could say okay maybe i went the wrong way and i need to take one hundred percent minus that amount because i'm looking at the top bit not everything from here down to the one thirty three right is the is the idea so i can also do that in z scores so i can do that in z's by saying norm dot dot s dot dist and then we're going to say that the z is going to be that that's all you need because it already has the mean and the and the standard deviation in it and then comma and we want to pick up cumulative one or true and once again this is going to be the inverse so i'm going to say close it up and there's the 69 that means i want to do one minus it should be the same so i'm going to say it's going to be one minus that amount and then percentify at home tab number percentify and you get you know the same thing now looking at the z score so maybe i want to then graph that over here so let's make a column for it i'm going to make a column for that and i'm going to say let's see if i can make it dynamic and i maybe i can pick up these two like at the same time if i want to kind of group those together so this is going to be equal to uh this and then i'm going to put an and tying it together the knot and that and then boom so now i've got that and that i'm going to wrap it home tab alignment let's wrap it make it a little bit larger and now you got a fancy header we can center it font group black white all right so then what i want to do is then then i can say how am i going to get this number i want to pick up everything from 133 on down so what i'm going to do is a logical test function it's going to be equal to if brackets this 80 if that 80 is greater than or equal to the 133 then and i want to make that absolute f4 then comma that's what the comma means then what do you want to do i want you to pick up the p of x if not comma leave it blank meaning double quotes with nothing in the middle zero in the text field nothing's in the text field enter zero is something i want nothing in the text field get it right get your language right you're confusing people so now you have that i didn't mean to confuse home tab number percentify we'll add some decimals so now we have everything down there so so then i could add this of course to the graph however i'm going to do it a little bit differently i'm not going to add it to the graph yet because i'm going to do the between to add it to the graphs and this is another attempt to try to kind of use one graph to do multiple things so let's let's say we added another question which would be p of x is less than or equal to let's say 109 brackets now i want to make this dynamic again so i'm going to say 109 double click over here i'm going to say this equals quotes this is all text end quotes tying it together with an and not and then selecting this information putting my cursor down picking up this cell and then i'm going to tie that together with an and not and then quotes around that last bit of text and enter once again it's dynamic dynamic that's how i like to be and then i can say what if i want also p of z let's do the z score this is going to be equal to the z score which is brackets 109 minus the mean 127 divided by the standard deviation there's our z score so now i might want p of z is less than or equal to negative 1.55 close it up i want to make that dynamic double click in equals quotes all the way out to the equal signed ending the quote tying it together with an and not and then replacing this bit with the actual number putting my cursor down finding the the correct cell that has the number in it even though you can't see the number because it's hidden and then tying it together with an and again and then putting the quotes around that last bit of text and boom and now it's needs to be rounded so i'm going to double click on it right before that one add a round in front of it round it down so this second argument comma needs to be two decimals rounded to two decimals close up the brackets boom dynamic dynamic just like just like me when i'm performing excel exercises in a very dynamic i don't know what i'm talking about so this is going to be equal to the norm dot dist let's do the calculation of x is now that 109 comma the mean is up top 127 comma standard deviation 1166 comma cumulative yes or one and enter so let's percentify it home tab number percentify adding some decimals and it looks uh looks good may we be to the end let's do it with a z score equals the norm dot dis norm dot s dot dist brackets z negative 155 comma cumulative yes indeed one or true and close it and there it is let's go home tab number group percentify add some decimals and there that one is so again if i wanted to make a column up there to represent it i can make a dynamic column header and i can say i want it to be this and tie it together with an and not an and not looks like a shoelace not that hasn't yet been yanked down to not format but it's getting there it's getting to a knot if you just pull on the rabbit ears it will uh it will it will turn into a not the and does okay so then i can say how is this going to work well i'm going to say then i'll use an if thing again and i'll say if uh if it's less than that amount so we're going to say okay equals if brackets logical test if this number that 80 is less than or equal to the this number down here the 109 then with a comma what do you want to do i want you to take the p of x number what if it's not then leave it blank double quotes not a zero double quotes i don't want anything there just leave it alone just leave it alone okay it's what i how many times so i have to tell you to just leave it alone man 109 now something went wrong because this one over here is f16 i need to make that absolute f16 f4 and the keyboard dollar sign before the f and the 16 enter and then double click it down all right and then it stops down here so i'm going to select the whole thing and percentify it home tab number percentify to recognize and then so now we see that it populates automatically so now that i have those two parameters i might then ask the question of the between question right so now i can say well what if i have p of x is less than or equal to i write it this way this might not be the best the fastest way to write it but 133 and because i like to have the x up front greater than 109 so now we've got the between right it's got to be between the 133 i've got the upper ends now and now i want the stuff in between the upper ends so that would be kind of like uh if i was to imagine this would be kind of like summing up from from uh the 109 here down to 133 again this isn't exact but that's kind of what you would be thinking right so so you're saying okay so i want that now in the middle bit but i want to make this dynamic based on the ends that i can put up top so i'm going to say let's make this dynamic this is going to be equal to quotes all the way over to here this one gets a little trick it's a little longer but we've gotten our practice end quotes i'm going to tie it together with an and and then i'm going to replace the 133 with that 133 and then this and isn't a not and that's a text and so i don't i don't want to use that as a tying not and so what i'm going to do is put another and so i can put that text and end with a quote around the and all the way up above the greater than and i think it should be or i should have an equal to there to close it up and then tie in another and replace in this 109 with that and then tie it together with another and and quotes around that last bit all right that is a mess of stuff to do but you can see that now it's dynamic man it's dynamic so now i can say i can calculate this uh if if i so choose and if i was to calculate it i've got to take the upper end uh the upper end minus the lower end right so i got to say okay i'm going to sum it all the way up to the 133 and then subtract out the 109 so it would be like this equals the norm dot dist and we're going to be picking up the x i'm going to pick it up up here now because i'm going to have all this kind of tied together uh uh that's the upper end comma the mean is going to be this and then i'm going to say comma the standard deviation is this and then comma cumulative yes or one close up the brackets minus another norm dot dist and then this x is going to be this comma the mean is going to be this and then comma the standard deviation is going to be this and then comma cumulative or one yeah and then enter let's percentify it home tab uh number group percentify and boom there we have it i can also see it this way which would simply be that if these are the two ends then 100 percent minus the two ends is the stuff in the middle in other words this is the stuff up top and then the stuff down below and the whole thing adds up to 100 so if i subtract 100 minus those two ends i get the amount in the middle which is going to be equal to one minus brackets this plus this close it up and enter home tab number group percentify add some brackets and there we have it we can also represent this and z scores right i can say this is going to be p of z which is greater than which is less than or equal to the point five one and i'm going to say and greater than or equal to the negative one point five five closing up the brackets boom let's make it dynamic so i'll double click on it equal sign to start it out quotes from the p past the equal signed end quote and to tie it together with a knot selecting this number replacing it with this point one five i'll probably have to round it but i'll do that next or after i'm going to put another and this time so i'm going to say and this and is a text and not a code or not and so i'm going to put a quote around here over to the equal signed end quote replacing the negative 50 155 with this 155 hold on a second i need another and before i do that i've got to say and and then replace the 155 negative with this 155 and then another and quotes around that last bit of text end quotes and enter i need to round both of those numbers i'm going to go into it this f4 i need a round in front of it and then the second argument is well i forgot the d the second argument is going to be two decimals so we've embedded around i'm going to do that here round and then brackets comma two decimals so i know that was a long formula but it's dynamic now which is super super neat so now we're going to say okay so now we can say that this is going to be the in between with the z score so i'm going to say this equals norm dot s dot dist we're going to pick up the z score the larger z score which is going to be the 51 and then comma cumulative or one close up not yeah close up the brackets and then minus norm dot s dot dist and then we want the z score of the 155 comma cumulative one or true close up the brackets enter let's make it percent home tab number group percentify add some decimals all right and so then we can we can also do that this way i can say this equals this and i can do the same thing we did on the second bit here these are the two ends so if i say this equals one or one hundred percent minus brackets this plus this then i should get to the same uh result i forgot the minus sign though one minus those home tab number group percentify adding uh some decimals so there we have it so if i go up top then i can do my betweens adding a column for the betweens so i'm going to say this equals then it could be this uh this one and then i'm going to put an and and tie it to this one so we get that dynamic formula again and i can then go to the home tab alignment center it wrap it and make it black and white and so there we have it i'll make this a little larger so we could see the whole thing the whole huge formula probably too large i don't know i'll keep it i don't want to make it huge but that's the idea okay so then on on here instead of me doing a logic test that has an and or or embedded in it i'm just going to say hey look this is where it gets different than what we did before i'm just going to say i just want the bit in the middle this stuff in here so so so what i'm going to do then is is i can use the two that i already have to take to pick the one in the middle right so i'm going to do it this way i'm going to use an if test i'm going to say this equals if logical test i'm going to say brackets and i'm going to take the sum so now i'm going to embed the sum function if the sum of these two cells uh now if they have anything in them i don't want to include them if they if they uh don't have anything there if it's blank then what i want to do is pick up the related p of x so i'm going to say the sum of those two cells if those closing up the brackets are greater than zero and hopefully this will still be greater than zero even though it's a quite small number if it's greater than zero then what do we want you to do i'm going to put double quotes i want you to leave it blank over here comma if it's not greater than zero what do you want us to do i want you to pick up the p of x so i'm going to say then uh enter they found an error with it i think they just closed it up let's copy it down fill handle double clicking and then if i scroll down now you can see it's populating just in that middle area so now if i select all of my data i'm going to go to the home tab number percentify add some decimals so now this is taking that in between area so in my graph over here i'm just going to graph i'm just going to graph this last bit in my in my graph so i'm going to say and that'll give us kind of some references that might give us some indication about all of these three things without having my graph have multiple layers on it other than two layers so now i'm going to go to my graph and i'm going to say that we want to go to the chart designs i'm going to insert another data and this time i want to go to add another data the name of the data is going to be this dynamic name we created i'm going to remove this series and select the little button and go from top control shift down make sure i pick up everything and we're going to say okay boom and okay so now we've got our second data here which is picking up that middle point right so now the orange represents the middle i'm going to bring this down a bit so we might want to add a legend plus button legend okay it's quite a long legend because of the because of the long title so let's go into that legend and see if i can put it at the top maybe since it's so long that might fit a little bit larger and this will say okay and then i could add another the z score at the bottom so i could double click on this one we have our chart information primary axis we want to add a secondary x axis for the z score so then i'm going to close this up and then we'll add the axis by going to the select data now i have these two data so i have the second one i can use instead of the x's the z's so i'm going to select my data this item and i want to pick up my z's from z down to here and this is the one gets a little finicky so i'm going to say okay okay it looks like they did fill them in so i'm going to say okay and so now we've got our z's i'm going to delete this thing on the right i don't think i need that and then delete that and then plus button axes i'm going to add the secondary axis there's my z scores up top grabbing that double clicking on it bringing it to the labels down to the bottom i'm doing this fast because we've seen it before we're running long on time i'm going to bring it to the bottom now notice it froze right there i have a lot of data sometimes it freezes if you close excel and open it back up sometimes it'll it'll unfreeze if it doesn't move the data you can see it's a little tweaked right here so you can refresh the screen so there we have it now we've got this kind of dynamic chart which can kind of help to see to visualize multiple things you know at one time right we might be able to go over here and say okay well if if we have uh p of x is greater than and we can populate uh the one the this is where the 133 is and i could say okay 133 uh is is here greater than so i'm looking at kind of like the blue area on the right hand side and then if i was to ask this question uh p of x is less than 109 well that i can put that on the lower end right the one the uh 109 and say okay that would be the blue on the lower end and then the amount between when we ask a between question i could enter the two outer parts of it and the between then would be the amount in the middle right so if if this question was asked i could say okay this is going to be the upper the lower that will give me the dynamic between which of course in here will be the orange so this is you know an attempt to get a chart that you can kind of adjust fairly easily no matter which kind of question that you might that you might be uh dealing with now i know if there is another another question that we want to just point out here uh that we didn't look at last time so we might ask a question like what's the probability of lower end so let's say we know the probability of the lower end is 0.45 45 percent home tab number we're going to say okay we know the lower end is 45 percent let's say that was the blue here you know if we know we know what that end is so then the question is well what's going to be the x value related to that or what's the what's the z value related to that so now we're we can use the norm dot inverse to find that so let me show you that one what would be the x value it's going to be equal to let's put it in the outside it would be equal to the norm dot inverse tab so now we have the probability not the x see we have it reversed now we don't we don't have the x we've got the probability and we want to get the x i'm going to take the probability comma and then the mean is still the same the rest of the bits basically the same the one's 27 comma the standard deviation is 11.66 and then that's it we don't have a cumulative argument because we already know that this 45 is the cumulative up to a certain point we're trying to find the x the x value there so we're going to say enter and we get to 125 we can do a similar thing with the z so I can say the z value would be equal to norm dot norm dot s dot inverse and then we're just all we need is the probability 45 and that's it right because we already have everything else is kind of in the z so I'm going to say all right close it up enter and we get the point one three now we can also see what's the probability upper end so if we know the upper end is like 55 percent for example home tab uh number percentify we know the upper end so if I look at my chart and I'm saying okay the upper end the blue is is 55 well I have to then do like the inverse because my cumulative references go up to the lower end so I have to do something like a one minus thing right so it'll look like this it'll be equal to the norm dot in so let's well let's find the x value equals norm dot inverse tab and this is where the one minus comes in so that 55 is the upper bit so I'm going to say this is going to be one minus that right because we have to calculate it from the low end on up right so we have one minus that and then that's going to be comma the mean and then comma the standard deviation and then close it up and boom and then I can do the z value in a similar fashion equals norm dot s dot inverse and then the probability is just the we need to one minus the 55 closing it up and uh enter and so you get the point one three notice when we looked at these two examples this 45 and the 55 add up you know to 100 so you can see why we're getting the results are the same here so you can kind of mold mold that over for now I'm gonna uh make this let's just do some formatting I'll make this blue as we normally do and bordered home tab font group I'm gonna make it uh border and blue if you don't have that blue right here it's in the standard there it is I'll select all of this control shift down I'm gonna make that border blue and then over here my charts look nice and snazzy so that's good we have a lot of snaz not sneeze we don't like sneezes but snazzy's snazzy's are good all right if I review it uh editing okay so that I think that's good