 Statistics and Excel. Deck of cards. Statistics and Excel. 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 better than their stupid stuff anyways. Like our Accounting Rocks product line, if you're not crunching chords 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. 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 can go right to the heart of the practice problem. The blank tab being blank, so we can practice formatting the cells in Excel as we work through the practice problem. Let's take a look at the example tab to get an idea of where we will be going. So in a prior presentation, we talked about some statistics related to a coin flip, which is a great start, but oftentimes people say, hey, look, that's a little too simplified because you only have two outcomes of a head and a tail. So let's take a look at another example with a deck of cards. And we'll practice mapping out the deck of cards, seeing how many cards are in the deck and how they are labeled, and then thinking about how we can assign possibly a number to each card. So we can use our Excel tools in order to have a random sample, try to simulate a random selection of a card in a deck of cards, for example, and then analyze the outcome with some of our statistical tools, including making some charts and whatnot from the data that we get. All right, let's go on over to the blank tab and just build this from a blank tab. I'm going to hold down control, scroll up a bit. We're currently at the 280, or I am at least, I'm going to select the triangle up top, formatting the entire worksheet like we do every time, right click it on the cells to format. And then we're going to go down, let's go to the currency, negative numbers bracketed and read. And then I don't need any dollar signs for my particular problem. So I'm going to remove, well, I don't need the dollar sign, I'm going to remove the decimals, which I don't need for my particular problem. So let's remove those as well. So let's first just think about the deck of cards. Now, oftentimes, when we're trying to use our analytical skills in Excel, we have to kind of do some conversion to numbers. So we can do some numerical calculations. So let's think about the cards in a deck of cards first. So, so we'll say, okay, if there's a deck of cards, we know that there's a one through a 10, and then there's a jack, queen, and a king, and the one is an ace, an ace or a one. So I can say right if I have a deck of cards, I'm going to, I'm going to select the whole thing again, go to the home tab font group and bold the whole thing. And then I'm going to wrap the text on this cell. So I'm going to go to the home tab alignment, wrap the text and center it. So I know if I just list out my deck of cards, I've got one, two, three, four, I can select those, I can copy it down to 10, copy it down to 10. And then, and then I've got the jack, queen, and king, which I'm just going to say I'm just going to continue my numbering and copy that down and just label them as 111213. Now, again, depending on what you're doing, this is a useful tool if you're trying to kind of count the cards, right? So you might try to assign a number to each of the cards, even though three of the cards don't have an actual number to them that could be useful for calculations and assigning which card is what, especially when you're in something like Excel. But it doesn't stop there because then we have, we have the suits of the card, we've got spades, we've got hearts, we've got diamonds, we've got clubs, and then the total. So if we think about each of these cards, there's four aces or ones, there's one spade, one heart, one diamond, one club. So we have a total number of aces, some function equals the sum of these four. So summing up those four, we'll just build in a little table. And then clearly that's the same all the way down, right? There's one, one, one, one number twos of spades, hearts, diamonds, clubs, these are not in any particular order. So I'm just making up an order of the suits. So I'm going to go ahead and say, all right, we sum that up. I can copy these ones all the way down because there's going to be one of each suit of each card. So I'm just going to copy and paste that down, and then I can copy my totals down, I'm going to put my cursor on the fill handle, and just copy that down. So there we have it. So so then I can have my totals down here. The total on down below is going to be if I if I sum this up equals the sum of these, I'm holding shift and scrolling up. Let's do that again, just so you can I can do this, the sum, the trusty sum function, SUM favorite function, shift nine up arrow, I'm doing it just with the keyboard. And then I'm holding down shift with the up arrow, just one at a time, because it's it's I don't need to like jump up there because it's not that far up. Once we have that then of course we can copy it across putting my cursor on the fill handle and dragging across now I'm going to go all the way to the end. And 52 then is the total number of cards in the deck so I can sum it up this way. Or I can sum it up this way right come up to 52 either way. So we've kind of listed out our our deck of cards let's go ahead and format this thing our typical formatting for the headers is going to be black and white so I'm going to go to the home tab and I'm going to go to the fonts and make this black and then white on the text. And then I'll typically make the middle bit blue the middle bit blue. So we'll make that blue home tab font group dropping it down on the borders let's make the borders first and then I'll go to the blue bucket if you don't have that blue it's in the more colors over here and then blue and the standard blue boom. And then the bottom maybe I'll make that dark blue home tab font I'll make this dark blue and then the font white just so we can stand out down there let's put some borders around the two borders you got to separate all the cells need to be in their own place just like just like the potatoes and the peas do not mix together on the plate or they will not be eaten. All right so then we're gonna we could are gonna make a little bit thinner too. It's a little it's a little wide I'm not trying to be offensive to the cells but they're a little wider than then is healthy for them to be. Okay so all right so there's our so there's just our numbers now. Now what if we were if we were to try to look at a deck and we're trying to think about well what if I what's my odds of drawing you know any of these any one card out of the deck so so the let's make a skinny H here so the odds of any card you would think would would be put a colon let's make the I a little bit larger so we could do a calculation of it the odds of any card would just simply be the number you know one card out of how many cards there are we know that there are equal to let's say equal to 52 in the deck so the odds of any one card per draw would be equal to 1 over 52 that would be a little bit of our statistics that's what we would expect now I'm going to make that a percent home tab numbers let's percentify that let's add some decimals so there we have that so so we could say okay what are the odds of a any suit is that how you still suit for cards I think that's like a a business suit I think they're the same though whatever you know what I'm talking about so there's only four there's an even amount of each suit so there's 13 cards of each suit so then we could say so cards per suit are equal to I'll just point to this 13 down here out of total cards so let's just call this one card out of cards in deck and then I can copy that here I'll say this equals cards in deck and this equals 52 so I know I'm doing this fast but we've done this a little bit in the past so I'm going to say all right there's our calculation let's go to the home tab fonts group underline that and so this is the odds of any suit this is equal to up to 13 divided by up arrow 152 I'm then going to make this sell a percent by going to the home tab number group percentifying it that's not a real word but I like using it some people it upsets them but you get 25 percent I think it's fun I think it's I think it's a term that should be introduced to the dictionary and I should get credit for advancing humankind's understanding through language but in the case that I'm also going to indent this home tab alignment indent and then I'll select these so notice this is a nice way that we can go home tab alignment indent oftentimes when I have the subtotal you put a colon at the end of it to show the indentation and sometimes you might even I'm going to make this little wider double indent the answer right alignment indent so now you've got you know this nice little formatting I'll underline this one too that shows shows everything we need there so that's the odds of any suit and then I could take uh I could take the odds odds of uh any number any suit so any one number any one number in any suit in other words I could get an ace in either spades hearts clubs uh diamonds or clubs right so any I can get I have four out of out of 52 chances every time I draw a card to draw a ace a two a three a four and so on so we can once again say uh number of card cards of each number are equal to four right and I can say then compare compare that to the cards in the deck equals 52 let's put an underline here home tab font group underline and do our odds I'll just say odds equals four over 52 we'll make that a percent home tab numbers percent to find it add a couple decimals so about 7.69 note that excel actually even though I'm rounding it here remember that excel doesn't actually round it if I was to use this number in another calculation in other words if I said I want to take this cell times 100 it's not really multiplying it times 100 times 7.692 but rather it's multiplying by the full decimal it's only that it's only rounding to the point that you can see that's really important otherwise you'll get a little bit confused like if I just took seven times 100 uh or or now it's eight times 100 and I get eight but I add some decimals it's like wait a sec something went wrong here it should be eight and it's seven point six nine that's because it's multiplying by the whole number here even though you can only see uh see how many decimals that you are showing all right let's indent these the same way home tab uh alignment indent I'll indent this one home tab alignment indent I'll make these blue and bordered as is our tradition so I'm going to select the first cells home tab font group border it and then bucket add in the blue same here I'm going to say border blue and then this one let's make this border uh blue the blue and border okay so so now we're going to say okay well how can I simulate that and excel I'd like to run an experiment where I basically can mimic uh pulling a card from the deck but I can't really do it because all of because I don't have an individual value for each of these cards unless I call it you know one of spades or something I would like an individual numerical value for each card well okay so now let's let's try to assign each card a numerical value so I'm going to then make k small I'll make a skinny k and say all right here's our issue here's the here's the uh the assigned number I'll say assigned number and notice I'm I'm over the cells but I'm not going to worry about it because I can type over here I'm going to say this is the card number and this is the suit okay so what I'm going to do is center these and format them selecting the top three home tab font group let's actually go to the alignment first and wrap the text center them I'm going to make them a header but by going to the font group bucket black and white all right now the the card numbers that I have I've got one two and so on selecting those cards and copying down until I get to 13 so I've got 13 cards and they're of the suit let's just pick this suit I'm going to say equals because that's the easy thing to do because I'll just pull the suit from over here equals c1 so I just pulled it over equals c1 instead of typing it again and on the second one I'm going to say this equals the one above it that's a useful little formula because then I can I can select this cell and copy it down the other method you could use is to make an absolute reference but notice I can copy that down so I've got one through 13 spades these last three representing the jack queen king and then I've got starting over again one two I'm going to select those two cards copy them down to 13 and these are going to be the next suit I'm going to say equals to pull that suit over left arrow scrolling up to the top I want to pick up the hearts that's the next one I had in line or at hearts and then in the cell below it I'm going to say equals up arrow the hearts above it and then simply put my cursor on that cell fill handle drag it down dragging it down so then we have one two again to 13 selecting those two so I can do the auto fill bringing it on down to 13 and these are going to be equal to in in 25 I'm going to say equals instead of typing it in less likely to misspell it multiple times I'll just pull in the one misspelled one diamonds diamonds and then we're going to say this equals the one above it and copy that down and then we'll do this one more time of course for the last suit and we just put these suits in random order this you can put them in any order you want this but this is the order we're going to choose once they're in order then you got to stick to it so the last one is the clubs so I'm going to say equals why you got to put the clubs last huh huh huh I don't know I just there's no reason I just randomly put them in hey clubs you're going to put the clubs on the bottom is that what okay I just here we go so here and then I'm just going to assign a number so now I'm going to assign them a number from one to 52 instead of saying they're going to be from one to 13 and then assign each suit I'm just going to say the number we're going to give them is just going to be one two two grabbing those two and fill handle them down to 52 cards so there it is boom all right so then so now I've just assigned these out so you can see down here like this 11 or if I go into the diamonds this five of diamonds is being represented by a 31 in our in our numbering scheme so that I can then set up a random selection between one and 52 each number representing a separate card so let's put a table around this I'm going to just put a table around it by going to the insert tab tables and then we'll just add a table around it and say okay so then I'm going to I'm going to make these two cells skinnier again I didn't mean to widen the cells out like that with the table all right so now so to simulate a draw of a card if it was an even deck that has all 52 cards in it you don't have the the card shark with an ace up their sleeve that they snuck in to the deck then you would expect that we could we could just do a random draw between one and 52 to simulate a random draw of the card so I can then let's make a skinny zero here a skinny zero column and then I'm just going to say this is a random draw of a of a fair deck that we're trying to simulate so I'm going to go then to the home tab font group make this black white alignment group wrapping it and then alignment group centering it so I'm going to use my random number generator again I'm going to say rand between and I want to take it between 1 comma 52 so we're just going to get a random number between 1 and 52 enter there we have it now the question is well how far down do we want to go now we have it we're in excel here so we can do this for quite some time so let's let's imagine that we did this a whole lot of time so let's take it down to like thousands of of draws just for the fun of it so we're going to go into like I'm going to drag it down to let's say like 3000 I'm sorry let's say let's keep it down here I went to five 5000 that seems a little much let's just we'll we'll keep it at 5000 instead of 5000 two I'll see if I can go up to just 5000 5000 and one because I think we started on cell number two so a whole lot of random drawing happening here so I'm going to go back up and so that would obviously take a lot of time in real life but our generator can do that and so then I can go into the insert tab and let's put just a table around it and it'll grab the whole table all the way down to cell 5001 and then okay so now we've got a random generator so we've got a random generator and if I double click on it it should reshuffle or I can like resort it and it'll reshuffle of as I uh resort it so now let's just mimic one one of those random samples of you know the 5000 draws that we drew one card at a time so I'm going to copy this whole thing I'm going to right click and copy and I'm going to put it in column r but paste it just one two three so it just grabs those numbers and solidifies them to just one number let's go ahead and uh put a table around this one insert and then uh tables and insert a table so here's our result of the first random draw and I'm going to make this home tab alignment wrap center and there we've there we go so now we can sort that uh we could sort this you know a to z or z to a if we so choose and we can see all the ones all the twos and whatnot but there's a lot of draws there so we could then uh we could then try to come up with a a table showing how many times each of those cards were drawn and see if that kind of lines up to what we would expect and how far off we are and so on let's make a skinny q column to do that I'm going to make a skinny s column and so let's say that we have our table again with our assigned number let's start off by just copying the table we did over here right let's copy the table we did over here where we had these three our assigned number let's copy this stuff from l to n I'm just going to copy the whole column and we'll bring that on over here and I'll paste it in column t pasting it the suits got messed up because I used a formula to get the suits so let's just say the suits let's just type it in here this is going to be spades uh well let's do this let's hard code the suits so I'm going to go over here and I'm going to copy this whole thing and instead of pasting a formula I'm going to right click and paste it one two three so now the suits are hard coded and there's no formula in there okay so there we have that and then I'm going to say the so these are our our results results from the five thousand and one or five thousand draws that we had so let's do a count of the results so now I'd like to do a count function and I'd like to say I want you to count it if it's a number one so however many times the number one shows up here that's what I want the result to be the number one standing for the ace of spades so I'm going to use our trusty count formula count if brackets and then I'm going to say the range is the first criteria selecting the entire range so that should pick up the entire range of the table comma and then the criteria is this number one not the card number because that repeats I want the number that we assigned that has an individual a number per card so it picked up and notice it copied the reference all the way down for us because we're in a table so that's great that's what we wanted to do and so there there's our results so we've got it has 79 ones 100 of the twos of spades spades right and then the count of 28 which represents a two of diamonds 90 of those and so on so if I then I can add to the bottom of the table a total right we'd like to have a total down here so let's go up top and to do that with a table I can go to the table design I can go to the table style options and let's add a total column so there's our total and then when it counts this up it comes out to 5000 and that's correct because that's how many times that's how many how many of these we we did how many times we drew one particular card out of the deck of 52 5000 times all right so now we can get a percent a percent of total so so how often is that from a percentage basis common calculation when we're doing our statistics it's going to be equal to 79 divided by the total which is 5000 enter it copies it down I can't see anything because the cells are not in the format of a percent so I'll select the whole thing uh home tab numbers percentifying it I can add a couple decimals to it and then if I scroll down we can see that uh it should sum up I'll choose my table options to sum it it should come out to you know 100 percent number group percent 100 percent so there so there we have it so now now when we when we looked at the actual here we said the actual outcome should be around uh 1.92 per card so you could see let's let's put that here let's say actual should be equal to we'll we'll pull in our number here 1.92 or one out of 52 and so I'm going to make that absolute by selecting f4 on the keyboard and then it copies it down so it's the same cell reference all the way down because I put a dollar sign before the j and the and the four selecting the entire column home tab number group percentifying it so let's add some decimals number group couple decimals and then I can look at the difference difference and we could say okay this is going to be equal to this cell minus this cell again because it's in a table it'll copy it all the way down let's add some decimals selecting the entire thing and then home tab number group decimalizing it and adding uh or percentifying it adding a couple decimals so there we have there we have our change so so it's pretty close on on on each of the cards that were that we're drawing out right so this is each of the cards each one of them we would expect to be because they're all representing you know one out of 52 chance if it wasn't even deck so you can also think about it this way if you drew one card uh 5000 times how many times would you expect to draw any one card like an ace of spades for example it would be equal to the 5000 draws times the actual 1.29 about or in other words equals the 5000 draws uh times 1 over 52 so right you that's what the expectation would kind of be okay so then so so let's if we wait we could make a graphical representation of this i'll do this fairly quickly because we're running long on the time here so we could take our results let's just take our results over here and we're going to select the whole thing not taking the total into account and then saying insert and charts histogram making a histogram so if i select this bottom amount i could go to my my bend size and say i would just like the bend size to be like one so i try to say like 0.99 and so now you've got each number you might say okay what if i make a bar chart that will list just 1 to 52 and then give me and then give me the results in a bar chart so to do that i could select i'm going to select the two columns i want this column on the left so i want the 1 to 52 on the x axis and then scrolling back up i'm holding down control to select the result column and that's what i would like on on the y and so i'm going to scroll down to there and then i'm going to place it where i want to be before i insert at this time and then insert charts and the bar graph this time so now we have a bar graph now it's not doing exactly what i want you say what is it doing so we can then i'm going to get rid of the title and i'm going to look at the data so chart design let's look at the data so what did it do it put both of them over here on the y so what i'm going to say is i really want over here on this side i just want the second series of data the results so i think i remove the first series of data and it has just the second one and then over here i want it to go from 1 to 52 i think it's doing that so i'm going to say okay that's good so i'm going to say okay so now i've got my chart down here 1 to 51 that's interesting it didn't go quite to 52 let's take a look at this second actually let's go back up to my data and then i'm going to edit this side and say let's pick up this side should be picking up this series of numbers over here one two 52 and okay and then okay so now it doesn't show me the end of it it still stops at 50 but there we have it and it's still stopping at 51 poor k so it looks like it's actually skipping every other number one three five so let's go into here and i'm going to say in here at category numbers and then under the labels down here i want to have a specific interval unit instead of automatic of one so now it kind of crowds them in because it put them all together so this would be a really long kind of graph i can pull it to the right so i can see all the numbers spaced out well enough to appreciate the graph so i'll make this a little wider holding control scrolling down a bit so there we've got our super wide graph and i could put my data labels in here i could go into let's just click anywhere in here and go to my chart designs and we could go to the chart elements and add data labels we'll put it like there it is in the center let's put them kind of up towards the top and now i'm going to remove this series two i don't need the legend and so so there we have it i and i could kind of mirror these two uh making them somewhat the same link so you can see basically you know if if we drew these out infinite one card an infinite number of times you would expect then the value to be let's do that value again down here if i well you would expect it to have an even odds meaning it comes up uh 1.92 percent of the times which in this case if i drew it 5000 times times the 1.92 would be 96 so this would be kind of like the expected expected value or the estimated value for any one of them so i'll put some brackets around there and so you can kind of get an idea of where these are hovering around now if this wasn't an even drawn this should represent an even draw then we can we can think about a system next time we'll talk more about well what if it's not an even deck what if we're missing a card or there's multiple other cards in it and then we can kind of see what would happen if we were to map it out in this kind of fashion so we'll dive more into that in future presentations