 Statistics and Excel. Deck of cards. Statistics and Excel part number two. Get ready, taking a deep breath, holding it in for ten seconds and 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 that's okay, whatever. Because our merchandise is better than their stupid stuff anyways. Like our, trust me, I'm an accountant product line. Yeah, it's paramount that you let people know that you're an accountant. Because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.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. However, we did so in a prior presentation, so you can go back to the prior presentation, work it from a blank worksheet. But if you do have access to this workbook, three tabs down below. Example, practice blank. Example, in essence, answer key. Practice tab, having pre-formatted sales so you can get right to the heart of the practice problem. A blank tab is where we work the practice problem from a blank sheet where we will continue working the practice problem here, allowing us to practice formatting sales within Excel as we work through the practice problem. So in a prior practice problem, we talked about a coin flip and now we're moving on to a little bit more complex situation where we don't just have the heads or tails, but rather we have the deck of cards. And we're practicing some statistical tools as well as how we can use Excel to help model our practice problems. So we first needed to lay out the cards in the deck. So we note that we have the cards in the deck. We have the suits of the cards and we have the numbers of the cards. And then we labeled, you know, how many cards are aces versus twos, threes, fours and so on. And we also labeled the Jack, Queen and King as 11, 12 and 13 to give them a numerical value, breaking out the total value of the cards, our number of cards to be 52. Now then we can do some kind of general assumptions with a 52 card deck. If we were to draw one card out of 52, one divided by 52, you've got a 1.92% chance, 13 out of 52 to get a suit, spades, heart, diamonds, clubs, and four out of 52 to be able to pull one card of an ace through a king of any suit. So then we wanted to mirror a random sampling selection. To do that, we needed to give a unique value to each card instead of having 1 through 13 and then it starts over again with different suits. We then just listed all of the cards and their suits and gave them an individual number so that now we have each card numbered 1 to 52, allowing us to use our random sampling tool over here to then draw a random sample between 1 and 52. We then made a static example of that random draw and we did it 5,000 times in Excel, which that's part of the beauty of Excel, allowing you to do a long large sample size or fairly large sample size. And then we broke out our results. So these are the assigned numbers, these are the card numbers, this is the suit, and the results meaning that the ace of spades that we drew 79 times. Down here, we had the 10 of hearts, which we labeled as card number 23, we drew 113 times. We then took the percent of the total, 79 over the total, which was 5,000, and that gave us our percent, which we expected to be around 1.92. 1 over 52, this is the difference. And then we made a histogram of our data and we also formatted it in just a bar chart, a couple examples of some different charts that we can format. So we're just going to continue on this time and we're also going to then see how can I mirror a situation where the deck is not exactly fair in Excel. So we can kind of do some comparisons using our tools and seeing what the results would be from a statistical standpoint. All right, just one thing I want to note here is that we also can make a histogram instead of the actual results of the percent, because each of these are 1 out of 52, so you would think that all of these numbers should be hovered around this 1.92. So let's do that real quick. I'm going to select this column and simply insert charts, histogram, and boom. So now we've got this histogram. I'll pull this down here and I'll remove the title. And you can see I won't mess with the boxes right now, but just by default, you can see the center point is somewhere close to what we would expect, which is the 1.92. It's the endpoint at 1.9. So it's a little tilted, a little off of the center, but it's somewhere around there, right? So notice that I summed these up to get 100%. If I take the average of all these numbers, of the percents, I get 2 adding a couple of decimals, home tab number, couple of decimals. I get pretty close, right? It's not exact because there's more decimals, but the average of all the results is somewhere pretty close to that 1.92. Let's just take a couple other stats now that we're down here and we could say how likely or how many clubs did we pull out, right? We know that the number of clubs we would expect to be like 25%, right? So we can count the number of clubs versus hearts versus spades and so on. So let's try that. We're going to say, let's say, I'm going to copy so I don't mess up the names. I'm going to copy the spades. I'm going to paste 123 and then I'll copy the heart, a heart number and I'll hard code it down here just to make sure I have the exact spelling because I'm going to use these in our formulas and then I'll copy the diamonds. And I'm going to put that down here and right click and paste it 123 and copy the clubs and paste it 123. So now I would like to get the count. Let's pull these down when I'm going to take these, grab them and pull them down one and I'd like to count the results of how many times we've we've got a spade. So to do that, what I'd like to do is look at this column and say, Excel, look at that column and every time you see a spades, I want you to count not just count some. It's different. I want you to sum up this column. So we can use our sum function but a little alternative on the sum function. It's going to be some ifs. Now I'm going to use the one with an S ifs with an S and then brackets. And here's our argument. It says what's the sum range? What's the range you want to sum going to scroll up? I'm going to say I want you to sum up these results, just the numbers and then comma and then the criteria. What's the criteria? I want you to, this is the criteria range. I want you to sum up this column if you find in this column, selecting this column, that's the criteria range, comma. Next argument, what is the criteria? The spades. So once again, we've got the sum range here, the criteria range here. If within that criteria range, you see that spades and enter and it comes out with 100, 1233. I'm going to copy that down, copying it down here and it should take the relative cells. So there we have it. Let's take the total now. Total equals the sum of those items. We come up to 5000, that looks right. And then we're going to say this equals this number divided by the total. And that's going to give us, let's make a percent of it, home tab, number group and percentify it, add a couple decimals. So it's not exactly 24%, but it's, you know, 25%, but it's pretty close. And then let's double click on that. I want to make this 5000 static. It's not in a table, therefore I have to make it an absolute reference to make it static so I can copy it down. So in this U61, I'm going to put my cursor in there, select F4 on the keyboard, dollar sign before the U and the 61, enter. Now I can put my cursor back on it, fill handle, drag it down. If I sum this up then equals the sum, I should get 100%. One or 100% home tab, number group, percentify it and add a couple decimals should be exact. And let's underline these two. Fonts group underline. So this is the count. This is the, let's just call it the percent percent. And I'll make these headers, home tab, font group, bucket, black, white, centered. So there we have it. Now the expected value should be equal to one over four because there's only four suits and they're equally represented. If I go up top, home tab, number, percentify, add a couple decimals. You can also see that as equals, there's 13 of each suit divided by 52. And if I make that a percent, add a couple decimals, right, one fourth. So I can then say, let's copy that down. This equals the one above it. I'm going to copy that down. That's what we would expect to happen. And home tab, number, and we'll sum that up equals the sum, which will of course come out to 100 again. Let's go to the numbers group, make that a hundred percent. And then the difference, which I'll just say diff is going to be equal to this minus this. And we can subtract that out and just do our little analysis table on this. And I'll make this a percent, a home tab, number, percent, adding a couple decimals. It's interesting. And then let's go ahead and then header, format, home tab, font group, black, white, and center. And then I'll make this blue and bordered, as is our general custom, home tab, font group, borders, drop down on the bucket. If you don't have that blue, it's in the more colors standard blue. Okay, now we could also run some numbers and think about how many times did each individual number come up, like the ace of spades and the ace of hearts and the ace of diamonds. There's four cards for each for each one. So I could I could list something like that out. I could say, okay, let's say these are my cards. And I just want to say an ace to three and so on of any suit of any of the four suits. So if I copy that down, I'm going down to 13 ace through King. And I could say, all right, then I'm going to say I want to do my count of each of those. So if I want to count those, if I go up to my system, notice, I don't really want to use the assigned numbers here. It's not as easy to do at least because then I've got, I've got the number one is the ace, but the next ace is being labeled number 14. So I could do that. But I have this other column, which is nicely just showing me the card number, no matter the suit. So I want to, I want to basically tell Excel everything in this column. If it has a one in it, I want you to some this column. So we could use a similar kind of technique here. I could say, all right, this is going to be equals to the sum. If I want the one with the S, because I think that's an easier one to use. That's the newest one, I believe the sum range. So I'm going to go up and say, I want you to some this range again. And then I'm scrolling back down comma, and then the criteria range, I want you to look through this column for the criteria. And then comma, what's the criteria? The criteria is that if you see a one, then you're going to sum up the range, the sum range. So there we, it comes up with 363. If I fill handle that and copy it down, there we have our results. And this is going to be the total equals the sum brackets. Summing it up, I should come up to the 5000. If that's my check number, because that's that's our totals. How many times we did this. And then we're going to say then the percent of the total percent. Of total is going to be equal to the 363 divided by the 5000. That second number, I want it to stay the same when I copy it down. Therefore, I'm selecting F4 and the keyboard dollar sign before the AA and the 70 enter. And then I'm going to put my cursor on it. Home tab number percentify, adding a couple of decimals. Then fill handle it and drag it down. So then we'll sum it up. Should come up to 100%, right? Because we're just summing up the total. Sum it, sum it up. And then we're going to go to the home tab, numbers, percent, couple decimals. There we have it. What was the expected, expected, the expected value from on a, on a percent. Bases would be equal to there's four cards of each number for aces. Divided by 52 in the deck. So you would expect a percent of home tab number percent adding a couple decimals 769. If I copy that down, there's our expected value. And then the difference between what we drew and the expected percent. We can also do like, I mean, if that's the percent, what would the actual count be? You would expect then out of 5000 that you would, that you would get 385, right? Would be the expected number of count that you. So in any case, we're going to say the difference is going to be equal to this minus this. And let's add a couple decimals to that. And then let's make it a percent and then add decimals. All right. And then we'll copy the difference down. We'll copy the totals across. And so there we have it. Let's put an underline here and just format this stuff underline headers, home tab, font group, bucket, black, white alignment centered. And that's all we need there. And then I'll make this whole thing blue and bordered. We'll go home tab, font group, border and bucket blue. Okay. So those just some other stats, some other stats we can make from our numbers. Now let's imagine a situation where I want to create another count. But instead of how I would like to create some numbers that are that are skewed somehow. Let's say we want to say that there's a number missing. Number five is missing and possibly there's an ace that is has been overstated. So people put took fives out of the deck and they put aces in the deck, right? So they took maybe they take a three out. They took the low number out and they put the aces in. So let's to do that. I'm going to just copy another random. So this is our random selection. I can shuffle the random selection that goes one to 5000 all the way down to 5000. I'm going to copy this whole column, right click and copy and bring that over to the side here. And then I'm going to paste it 123 right click paste 123. So now we've mirrored another random sample. I'm going to insert a table, insert table. Boom. Let's wrap the text and make it a little skinnier again. We don't need it to be that wide. And we're going to go to the home tab alignment, wrap it and center it. Actually, hold on a sec. I'm in the wrong up here and then home tab and wrap it and center it. Okay. And then now I want to adjust the results so we get results that are different than what you would expect from a random sample of a 52 card deck. In other words, we're going to imagine that there's like a three of diamonds. Let's say that wasn't in the deck and that there's an over representation of the ace of spades. There's multiple ace of spades within the deck. Now there's multiple ways that you can do this. But the easiest way to do this is just to try to look for the number representing the three of diamonds. And let's replace it with the number one for the ace of spades. So if I go back on over here and I look for the number representing the three of diamonds, we can see that that is a 29. So I'm going to go through and say every time you see a 29, I would like you to place it with a one. So I'm going to select the entire data set and we're going to go in the easiest way to do this home tab, editing, find, but I'm going to go to the find and replace. And I'm going to ask Excel every time you see 29, I would like you to replace it with a one. Every time you see a 29, I want you to replace it with a one. Okay, so then I can say replace all replace each. I'm going to say replace all and all done. So now there shouldn't be any 29s. If I look to find a 29 find next, there are no 29s, right? So it replaced all of the 29s in the data. So now we have kind of a skewed data set. And let's see how that just basically maps out when we take this data and do our calculations with it. So I'm going to do the same kind of thing we did before. And it might be easier to do if we simply copy what we did last time. So last time we took our random data set, this is where we're at a mirrored situation here, except that our data set has been skewed a little bit. And then I'm just going to copy all the formulas, this entire table. And if I put it in the same relative position to my data set, then hopefully it'll give me the same formulas unless there's absolute references. And I might have to reassign the table, for example, because it's going to a table, but it might be a little faster. So I'm going to right click and copy the entire thing. And then we're going to bring that over here. And I want to put it into the same relative position, skipping a line, putting it in AP, right click. And I'm going to paste it with the formulas, because hopefully the formulas will be pointing to the right spot. They might not be because I'm using a table. So we'll have to adjust that, but there it is. Let's make this a little thinner and let's check it out. So here we just have a number. That's fine. This we just have our numbers. This is our suit. These are hard-coded, nothing to adjust here. The results, let's see what it's doing. It's saying count if that looks good, but then it's pulling up the wrong table, right? It's still pulling up the table from the prior system. So I'm going to have to replace the count if tables on all of these. So I'm going to say that right. Count if the table needs to be this one, because I'm pulling in these numbers. Let's just do it again. I'll have to redo this formula. We'll say this equals count if brackets. And we're going to say equals count if and then brackets. And then the range is going to be this range, that whole thing. And then comma, the criteria is the assigned number, not the card number, a side number. There we have it. Now it should copy that all the way down because we're in a table. If I look at the percent total, this percent divided by the total, that looks like it's doing the right thing. B to the end, the actual number is still correct, I believe. It's picking up the proper number because it's got an absolute reference. And then the difference is the difference between those two. So that looks correct. All right, so if we analyze the results here, we would expect the ace is kind of overrepresented. So I'll make that yellow. And the three of diamonds is the other one that we shorted. So the diamonds are down here. So those two look quite odd, of course. And so we can clearly see, well, it looks like the three of diamonds might not be in the deck. And it would look like the ace of spades from our draws looks to be overrepresented. A lot more draws of the ace of spades than you would normally get. That's the general idea. So then we could do some pictorial representations. We could make a histogram of the results. We could make a histogram trying to show all of the cards 1 to 52, but we could also do that with a bar chart. So let's do a couple of those just to get an idea of the differences between the tools. So if I try to go to the results and insert a histogram, charts, histogram here. So now we've got a histogram of the results for each card. Then we're coming to the default buckets that are down here. We could then look at the buckets and say maybe we want more buckets. Let's say we want like 12 buckets or something. And so now you've got and you've got these two items that are kind of laying on the outside. So let's close that one back out. Now the other way you could try to do a histogram. And because you might say I want to count all 52 numbers and I might take the entire data set over here and say what if I made a histogram of that charts histogram. So now you can see the buckets that it's putting together are 1 to 4, up to 54. Let's try to get the buckets down to just 1, 2, 3, up to 52. I could select the bucket sizes and I can try to say hey look I want the bins at just one in between them. So now you've got 1 to 2 and so on up to 52. And now of course in this one you can see this huge one in the first bucket and you can see we're missing something over here. You can kind of get an idea of these outliers for the results here in this histogram. Now you might want to mirror this chart might be better represented by just doing a bar chart because then you can just list instead of buckets 1 to 52 to do that and I'm doing these fast because we saw these last time but I'm just or some of them at least last time but now I'd like to say hey look I want you to list these numbers 1 to 52 so I'm going to select this column and I want that on the x axis not including the total holding down control and I want the results on the on the y and this time not a history and we're going to go to the insert and charts and build a bar chart and so we have our bar chart now again by default it tries to plot both of those so what I'm going to do is is that I'm going to make this really wide because I want to have it have 52 numbers fit in this thing and it did actually put the 52 numbers there so it looks good that it went up to 52 that looks right I just need to remove that first data set which isn't useful to me so I'm going to then say data I don't want series one we just want series two and so so there we have it so there we have it I'm going to get rid of the key down here and then we could put the count if we want to I'm not going to select him I'm just going to select the chart and then chart design add tools and I want the the data labels so I can put the data labels like up top here so there's our data labels and again conspicuously missing this one on 29 so that again obviously the data is telling us you know something isn't quite right here we can also make the histogram of the of the percent of the total which we would expect things to be hovering around the actual value of the 1.92 if it was a fair 52 card deck so if I select this column and go to the insert charts histogram and take a look at it you can see again we've got these kind of outliers so it looks something looks a little bit strange with that so it say okay because you would expect it to be hovering around the 1.92 and you could see that that middle point you know it's kind of it's kind of in the you've got some a lot of the data in the middle but then again it's kind of you've got these outliers that are happening here what if I took the average of all of of the results if I take the average of these results it's already been taken it's still the average is still coming up pretty close 1.923 to the average to the expected average so there it is but we could see a couple of those those outliers and again we could do some more tables to count the number the number of aces right if I did my count down here at the table we have the spades the spades versus the hearts and the diamonds count is this pulling up everything that it should from the proper table expected value and the difference here so notice the spades the number of spades is over represented and we I'm not rebuilding the table because we have this from the prior worksheet it pulled over but notice that the the spades are over represented because we we we've replaced the diamond with a spade right so that's the general idea and then we can compare and contrast the data from this data set which we're saying is representing an unfair deck it has too many aces it doesn't have one of the threes to the data that we have from a fair deck and when we're doing our statistical analysis that's often what we are doing we're trying to say what would be the results if we would except expected the no assumption that it's a fair deck versus is there a statistical difference to what the actual data is saying can we draw then conclusions from that difference