 Statistics and Excel. Normal distribution calories example part number two. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth, soothing Excel. Here we are in it. 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 that's okay, whatever, because our merchandise is better than their stupid stuff anyways. Like our Accounting Rocks product line. If you're not crunching cords using Excel, you're doing it wrong. A must-have product, because the fact as everyone knows of accounting being one of the highest forms of artistic expression means accountants have a requirement, the obligation, a duty to share the tools necessary to properly channel the creative muse. And the muse, she rarely speaks more clearly than through the beautiful symmetry of spreadsheets. So get the shirt, because the creative muse, she could use a new pair of shoes. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. 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. However, if you do have access to this workbook, there's three tabs down below. Example, practice blank. Example, in essence, answer key, practice tab, having pre-formatted cells, so you can get to the heart of the practice problem. Blank tab is where we started with a blank worksheet, having just our beginning data on it. So we can practice formatting the cells within Excel as we work through the practice problem. And we're going to continue on with that now. Quick recap of what we have done thus far. We had our data on the left. We had our calorie counts, which were counted by day. We made a table out of it so we can sort by date or by calories if we so choose. We calculated the mean or average standard deviation, median mode, the mean being close to the standard deviation, as well as graphing the actual data and having it look somewhat like a bell curve, as well as just basically having an assumption about the types of data being calorie counts, which you would assume would probably hover around a center point. Otherwise, you know, you would think someone would get heavier or lighter over a timeframe would give us an indication that a bell curve might be something that would be useful. We then wanted to plot the bell curve. Now, to do so, we took four standard deviations below and above. And then we ran into the issue of, do I really want to plot one calorie at a time? If we do that, it would be great because then our percentages over here would basically add up to 100% about because we have four standard deviations and that's nice. But if we go for standard deviations below, we end up with these negative numbers, which is something that is impossible in actual practice because you can have the negative calories. And also, we end up with very small units of data. So when I plot this out, we get to these small percentages. And if we compare that then to our actual count, then we can compare the actual count in terms of, we can take these percentages times the count, meaning the number of calorie counts in days that we did. And it's going to be difficult to make a comparison between that and the actual data because, again, the actual data is going to have to be, when each of these calorie counts will have to be a whole number instead of this fraction of a number. So whether we do it by whole numbers or by, or trying to do the count or by a percent, we end up with this issue. So we have to basically, the next thing we want to do is say, okay, let's add some buckets and see if we can do this with our buckets. And also just note that if we were to plot our actual data, like if I tried to plot my actual data, as we have seen in the past, on top of this graph, it's not going to work quite well because I have too much, you know, there's going to be a lot of blank space in the actual data. So if I tried, so this is in terms of percentages, you can see if I tried to say, I'm just going to add my actual data in terms of percentages on top of it as we've seen in prior presentations to try to see the comparison between the two, we're going to run into an issue. Let's just show that charts. We're going to go and say select data. And I'm going to say, okay, let's add the percent of total this column. I'm going to delete what's in here thus far, hit this little button and then select from here, control shift down and enter and then okay. So there we have it. I'm going to say okay. And so now if I scroll on back up, we're going to see. So now it gets all messed up, right? And that's because again, we have this this issue with the with the too many units of data. So it's not going to line up. Let's undo that. And before I continue, I should have changed the X's down here last time. So let's do that now. I'm going to go to the chart design data and let's go to the edit and select this item. And I want to pick up my calorie counts here on the X's shift up so I don't pick up the total and okay. So there it looks like it's picking it up. I'm going to say okay. So now we have it goes on down to the zeros. Obviously in practice, it's going to stop, you know, at zero because we're not going to have those negative numbers. But if you want to see the full bell curve, it's going to give you that information. Now the other thing to just keep in mind is remember that because we had so many counts, we have pretty slim, slim slices of data. So when you think about like integral calculus, you know, usually you think of it as, you know, an infinite area of these of these lines, right? Well, we're getting pretty close if we were to sum up all of these lines because we have such small amounts, right? So we'll take a look that'll come into play possibly shortly here. So now let's say that we're going to say, let's make a skinny L and say, all right, how can we kind of compare this data? I'm going to pull this to the side, get it out of the way, get out of my way, man. I'm doing stuff here. I'm doing stuff here. Let's make an X and a Y or let's make an X and then let's say that the X is going to be from zero to let's say like 400, 400. And these will be kind of our buckets and then to 800 and so on. And I'm going to go, I'm going to select those three, control or the fill handle and bring it down to 5600, our upper limit. So it brings it down to 5600 because that's our, our upper X or it's greater than our upper X. So that should work. So I'm going to go back on over and then this, I don't want why I want the actual frequency and then I'm going to say home tab alignment. Let's center it black, white, wrapping the text and let's make this the same black, white and center it. And so now we're going to do our frequency. So I can do this with the actual data now. So we did the frequency over here, you'll recall. And it wasn't very helpful because when we go up calorie by calorie, then it's not going to give us, we're going to get sparse ones every once in a while on all of these calorie by calorie data. But now I'm going to do a frequency with the actual data. So for the second one, we're going to be picking up everything that is going from zero up to the 400. So let's try that. We're going to say this is going to be equal the frequency tab and we'll pick up the data array, which is our actual data way over here. And we'll pick up the whole thing and then enter a hold on a second. That's not the whole formula. And then I have to say comma and we want our bends. Now let's pull this to the side so you can see it. Then the bends are going to be from this zero on down control shift down. I'm going to scroll back up. So there it is. And then closing this up and that's not a close close it up and enter. So there we have it. It goes a little far. So I'm going to double click this, go to that last bit and bring it back to 15. And so there we have it. So then our total count, if I sum this up, alt equals should be that 457, which matches our 457 when we just counted the data. So that's our double check that it looks like it's picking everything up. So we had four hundreds, anything from zero to 400. We had five of those anything from above 400 to and including 814 and then six from 800 to 1200. And so on and so forth. Now we might want to do a percent of the total in our buckets. So we can also do that this way percent of total. And I'm going to say this is going to be font group black, white. Let's wrap it. Let's center it. This is going to be equal to this number divided by the total now, which is that 457 f4 on the keyboard making an absolute dollar sign before the N and the 17 enter. Let's percentify it. Home tab number group percentify adding some decimals and then double click the fill handle to take it on down. I don't want the 100 to be just doing this divided by this. So I'm going to say delete that and instead sum up to get that 100 alt equals for our check number. There's our 100 that checks out. So we can do that now. I also want to group my P of X's. I could do this a couple different ways. I could say, well, look, I want to get all of my P of X's that go from negative to zero. That's going to be my first bucket and then all of my all of my P of X's that go from that go from zero to 400 and so on. Now, this isn't the perfect way to do it because you might say, well, then it's you're like summing up. You're summing things up. But again, this is where it comes into play that because these are such we're using such small units that we're going to get pretty close to the to the actual answer. Even though we're kind of summing this up and then we'll show another way to do it as well. So so if I was to so I could sum up the percentages, which I can compare to these percentages, or I could sum up this count number. That's why we did the count number two. And, you know, we can do either either of those methods. Let's do the percentage, though. We're going to say this is going to be the P of X frequency. I'll just say freak when C it's the freaky P of S X and then alignment wrap center. Okay, so to do this, we could use like a sum if formula. So I could say something like this equals sum if S because there's going to be some ifs and then brackets. That's the one we want the sum range, the range that we want it to be summing is going to be this one. So I'm going to put my cursor in the P of X on the percentages control shift down. I don't want to pick up the total. So shift up and then I'm holding down control backspace to get back up to the top. And then the criteria range is going to be the criteria of the criteria is going to be these X's. I'm going to select this whole thing. I'm going to put my cursor G to control shift down and then shift backspace not to pick up the total control backspace to get back up to the top. And then comma and the criteria is that we want it to be. I'm going to say we have to put brackets around our less than we want it to be less than or equal to I've got to put not brackets, but quotes around that and then tie it together with a not and that's our not and we're going to be picking it less than or equal to the zero. So this should be picking up everything that's in the negatives up to the zero and then I'm going to close it up and enter. And so we have let's make a percent home tab number group percent to find it at a couple of decimals. We're at the point three six. Okay, so this next one's going to be a little different because I'm going to have two criteria is now because I want it to be less than the four hundred, but greater than zero and then we can copy it down. So we're going to do it again equals so we'll say some if and have the S brackets. We've got the sum range. The sum range is going to be the P of X control shift down shift up so we don't pick up the total control backspace back to the top. And then I'm going to say comma we want the criteria range which are the X's. So we're going to select the X at the top control shift down and then shift back to go back up to the top or shift up and then control backspace to go back to the top. And then comma we want the criteria the first criteria it needs to be greater than zero. So I'm going to put some quotes so I can put a greater than sign and quote and tie it together with an and you need the and before you select that cell which is the zero. And then comma criteria to starts with the criteria range we don't need to put in the sub range again just the criteria range again which are at the X's. So I'm going to put my curse at the top there control shift down shift up control backspace to get back to the top and then comma and we want the second criteria less than or equal to the four hundred. So I'm going to put quotes and then so I can add the less than and an equal for less than or equal to end quote tie it together with an and and then you can select the four hundred and close this up. Let's see if it works enter. I'm going to go to the home tab numbers percentify add some decimals looks great. I'd like to copy it down but I need to make sure I absolute reference anything that needs absolute referencing. So I'm going to double click on it. This these items over here I need them to stay the same. So I'm going to select F4 here dollar signs F4 here dollar signs here to F4 and then this one I do want to move down the M anything in column M. I want to move down relative. So I'll leave those as they are this one. However is over in G. I want it to be absolute therefore F4 dollar sign before the G and the two and enter. Now I should be able to click on it and double click the fill handle bringing it on down. So so there you have it and you can see that we we can compare these two things now right. So now we have something that's actually comparable whereas we didn't have it when we did it calorie by calorie and you can see there's somewhat comparable. So now we're saying OK these line up and it looks like the bell curve might be a good thing to use. I'm going to say alt enter to to sum this up home tab number percentify and there's our 100 percent about now. I could also take these numbers and multiply it times my actual count to get an estimate of what Y count would be based on the actual bell curve numbers. Or I can do the same thing and then add up all of the all of the frequencies that we did over here. So for example I could do this. This is going to be the P of X times times say the count. Times times the count. And so I'm going to say now home tab alignment center wrapping the text black and white. OK. And so actually we did this over here. This one this is the one I was looking at right. So we did the P of X times the count over here. But when we did that over here we got counts that are going to be very small due to the fact that we're doing one calorie at a time. So we could do a formula similar to what we did over here on this column. Right. Or I can basically repeat what we did here with these percentages. And that's what we'll do on this one. So I'm going to say OK. This is going to be the count which equals the count which was 457. That's our sample. If that's our sample count times these are the percentages that we think are going to be below zero up to zero based on our norm dot disk calculation. So I'll pick that up and enter. So we'd have two there double clicking on that. I'm going to make the total absolute which is down at the bottom there. So I'm going to say F4 dollar sign before the end and the 17 enter and then double click to pick it up on down. So now we can compare our counts as well as you know the percentages. So this would be based on the bell curve. And so now we have this comparative data and you can see it's somewhat comparative in nature now. So if I sum this up alt equals we're going to get to that 457 of course again. And so now we can we can do something like compare our actual data to to the bell curve data that we had that we couldn't do. Before you'll recall that this was our bell curve data. But because it was so fine and detail we couldn't really plot our actual data on top of it. But now we've got these two things that line up pretty well. So if we wanted to plot them together we could do so. So I could say something like either the percents doesn't I could do either the percents or the actual numbers. Let's do the percents. So I'm going to say let's take the percents of the total and then I'm going to say insert and let's do a bar graph. So we'll say boom bar graph. And there it is. Pull this to the side. I'll get rid of the total or the title and then I have to add our own X's which should be these numbers. So I'm going to click on it chart design data and let's go to the edit and then add our X's as is our general rule. So there we have it. And so that looks good. And now let's see if we can plot the P of X's on top of it to see how close they kind of line up now that we put our buckets in place. So I can say all right let's go to the chart design data and then let's add another one for the P of X now and we're going to hold on a second wait a second here before I do that. Let's add not edit add another one which is going to be the P of X and then selecting this deleting that and selecting our data right there. Okay and boom. So now we've got some comparative data. So now we were able to put those into buckets and do something somewhat comparative which we again we can say okay it looks kind of like the bell curve would be something that would be giving us some useful information. Now I just want to show also that we could do this do this frequency distribution over here where we did which one did I did I do this on. So see how we summed these up this way. So again that kind of works pretty well given the fact that we have these very fine lines as you can see when we grafted. But we could but we could do it another way which which would be to basically use a formula so we can define our lower and upper area and then basically use the norm dot dist formulas. So to see that let's go down here and say this is going to be my lower and upper and then this is going to be the P of X. And then let's make this our headers home tab font group black white wrap that we don't really need to wrap it I'll just center it. And then I'm going to say let's put the starting lower at negative one and the upper is or I can put the lower at negative. Well I could leave it blank but I'll put the upper at then zero that's our first one and then it's going to start from zero and it's going to go up by four hundred. So this is going to be the prior one plus four hundred and then I can copy these down until we get to that five six zero zero. So I'm just going to copy that down and so that looks right and then I'm just going to copy that pattern down until we get to the five six zero zero five general need this last one. Okay so now we can we can say our P of X using our norm dot dist the first one I'm just going to say this is going to be norm dot dist now and this is going to be our X. I'm just going to use the zero as my upper threshold right and then I'm going to say comma the mean I'm going to pick up the mean up top which is over here. There's our mean and then comma I'm looking up here norm dot dist and the next one is going to be the standard deviation standard deviation comma. And then let's go back on over now that we've entered that and then comma cumulative I wanted to be cumulative so I'm going to pick everything up up to that zero point. So I'm going to say one for cumulative and enter now on the second one what I wanted to do is take between zero and four hundred. So what I'm going to do is pick the upper threshold of four hundred minus norm dot dist up to zero because now we want that in between calculation. So I'm going to say alright this is going to be equal to norm dot dist the X is going to be the higher one the four hundred comma the mean and the standard deviation I'm going to go over here to pick those up. Are going to be mean comma I'm looking up here now to see what I'm doing and then I'm going to say the standard deviation is that one and then comma and then I want this let's bring it back down to where we're at. I want this to be cumulative so I'm going to say this is going to be one close it up minus the lower bucket minus another norm dot dist. And then X is going to be the lower bucket now comma the mean and standard again mean comma I'm looking up here now standard boom comma we want this to be cumulative. So I'm going to say one and close it up so that's going to take everything up to the four hundred minus the stuff up to the one to the zero which will be the stuff in the middle. So then we're going to say enter and so that's going to be let's make these percentages home tab number group percentify add some decimals there we have it now to copy this down the second one. I can copy it down but anything that's not in my area here I need to make absolute. So if I look at my formulas the X is in here but the mean is not that's over an E. So I need to say F4 in the keyboard dollar sign before the E and the one same with this one that's over there and E F4 dollar sign before the E and the two over here the M is in here so that's good but this one is outside. So F4 dollar sign before the E and the one F4 dollar sign before the E and the two enter then I can click on it and double click and bring it on down. So our totals are total total total I'm going to say alt equals adds up to approximately 100% home tab number percentify and there we have it. So you can see that this comes out pretty close like if I look at that twenty two thousand four hundreds to two thousand eight seventeen twelve two thousand four hundred two two thousand eight seventeen fourteen right or seventeen thirteen. So if you compare these two it comes out pretty close given the nature of the data that we're looking at because up here we did a pretty fine. Because normally you can't just sum up right because it's the area under the curve but because we had such fine detail on each of these lines down here we actually get pretty close pretty pretty close to if we used the the function here on these. So you've got point three six one point oh four point one point three six point eight two six point eight two and so on and so forth. OK let's just do some formatting here and and stop this foolishness I'm going to say a home tab font group I'm going to make this blue if you don't have that blue I like to make it standard blue OK put some borders around it if we could. I like to have some separation between my stuff I don't like the mashed potatoes all mixed in with the peas on my food plate. Could you put some borders around them if you would this is not this is not anarchy that is happening here we need some.