Candace is using a third-party Bingo app to generate numbers for a bingo game. Some people are complaining that the app is not fair and that it is calling some numbers too frequently. Candace manually keyed the numbers into Excel and wants to analyze to see if the numbers are uniformly distributed.
This episode will show you how to test to see the distribution between the five columns and between the 75 numbers. Note that while this could have been done with the FREQUENCY function, I chose a pivot table instead.
This episode will show you how to test to see the distribution between the five columns and between the 75 numbers. Note that while this could have been done with the FREQUENCY function, I chose a pivot table instead.
Transcript of the video:
Alright, so hey, Candace has a third party Bingo app that is calling bingo numbers.
And she suspects that it's not being fair.
She wants to test if the numbers that are being drawn are truly random or if there's a pattern.
Candace says that she is keying the numbers from the bingo game into Excel right? And I don't know exactly what that looks like.
I can kind of picture what it might look like here. I have 750 numbers from A2 down to A751.
And what I'd like to do is be able to separate that into the column (that's the letter) and the number, right? So I'm going to be very careful here.
Control shift down arrow to select all the data. And then on the Data tab. Text to columns.
In the first step, step one, choose Fixed Width.
In the second step, they're not going to figure out what you're trying to do.
So draw a line just by clicking between the letter and the number. But don't click finish.
It's really important here.
I don't want to destroy the original data in step three.
The destination: I want to change that destination to be the column next to the original column.
And what that's going to do is it's going to just be a fast way to break out the letter from the number.
Yes, we could have done that with =LEFT and =MID, but there's sometimes just an easier way to go.
And then over here in column D add the number 1 to do a count.
We could do the pivot table without this.
It's going to make the pivot table a little bit easier, right? So now we have these four columns.
The original number drawn.
Breaking out into the letter, the number, and then just a column of ones. Pivot tables are super easy to analyze data.
Choose one cell in your data. Insert pivot table.
Let's put it on an existing worksheet and just click out here to the right somewhere.
Click OK.
Alright and then look over here on the right hand side at the PivotTable Fields.
Your pivot table can is probably starts like this - Field section and Area stacked.
I always change mine to be side by side, but the basic idea is the same.
These four drop zones here and I'm going to checkmark letter and it's going to go to Rows.
And then I drag the count field down to values.
Right, and what we get here is we get the letters B-I-N-G-O. Although they're in alphabetical order.
And the number of times they were drawn. So in this particular case, it looks pretty good.
I mean, it's an equal distribution, If you want to rearrange these letters, it's pretty wild in a pivot table. You can do this.
I'm going to type and I here and the 147 changes to 150 and then an N.
And then it actually that she was already there.
OK, so you could rearrange those just so it it kind of looks better. Right now that's the columns.
The columns seem to be pretty equally distributed from these 750 data points.
I'm going to make a copy of this PivotTable with Ctrl+C.
Just come out here and paste and from the second pivot table I'm going to change the shape of this.
I'm going to get rid of the letter.
I can't just click number because it would go to the values areas. I take the number to Rows like that.
And now these are the numbers like your G 48 that's going to appear in 48.
Ideally with 750.
You would expect about 10 of each, But when I select all these numbers, not including the grand total and come down here and look in the status bar.
The min of 3, there's some numbers were picked as few as three times. And then Max of 19.
So I don't know is that is that suspicious that some numbers were chosen three times and some numbers were chosen as many as 19 times.
That might be OK, and that might just be a problem that I have 750 instead of. 7500 or 75,000 data points.
I did run this again with 75,000 data points and in that case the Min and the Max get closer 941 to 1067 when you would expect 1000 of each right.
So for this to me it looks fairly random.
What I would be concerned about is if you got something like this where there was a high number of selections in the N column.
Not as many in I and G and very few in B and O.
Then they're using a Normal distribution instead of just random numbers between one and 75.
So if you get something like this, yeah, I'd be concerned that it doesn't seem to be working correctly.
Right, so that's using a pivot table to create a frequency distribution.
You could certainly do that with the =FREQUENCY function.
Frankly, it's a lot harder to do than a pivot table.
And she suspects that it's not being fair.
She wants to test if the numbers that are being drawn are truly random or if there's a pattern.
Candace says that she is keying the numbers from the bingo game into Excel right? And I don't know exactly what that looks like.
I can kind of picture what it might look like here. I have 750 numbers from A2 down to A751.
And what I'd like to do is be able to separate that into the column (that's the letter) and the number, right? So I'm going to be very careful here.
Control shift down arrow to select all the data. And then on the Data tab. Text to columns.
In the first step, step one, choose Fixed Width.
In the second step, they're not going to figure out what you're trying to do.
So draw a line just by clicking between the letter and the number. But don't click finish.
It's really important here.
I don't want to destroy the original data in step three.
The destination: I want to change that destination to be the column next to the original column.
And what that's going to do is it's going to just be a fast way to break out the letter from the number.
Yes, we could have done that with =LEFT and =MID, but there's sometimes just an easier way to go.
And then over here in column D add the number 1 to do a count.
We could do the pivot table without this.
It's going to make the pivot table a little bit easier, right? So now we have these four columns.
The original number drawn.
Breaking out into the letter, the number, and then just a column of ones. Pivot tables are super easy to analyze data.
Choose one cell in your data. Insert pivot table.
Let's put it on an existing worksheet and just click out here to the right somewhere.
Click OK.
Alright and then look over here on the right hand side at the PivotTable Fields.
Your pivot table can is probably starts like this - Field section and Area stacked.
I always change mine to be side by side, but the basic idea is the same.
These four drop zones here and I'm going to checkmark letter and it's going to go to Rows.
And then I drag the count field down to values.
Right, and what we get here is we get the letters B-I-N-G-O. Although they're in alphabetical order.
And the number of times they were drawn. So in this particular case, it looks pretty good.
I mean, it's an equal distribution, If you want to rearrange these letters, it's pretty wild in a pivot table. You can do this.
I'm going to type and I here and the 147 changes to 150 and then an N.
And then it actually that she was already there.
OK, so you could rearrange those just so it it kind of looks better. Right now that's the columns.
The columns seem to be pretty equally distributed from these 750 data points.
I'm going to make a copy of this PivotTable with Ctrl+C.
Just come out here and paste and from the second pivot table I'm going to change the shape of this.
I'm going to get rid of the letter.
I can't just click number because it would go to the values areas. I take the number to Rows like that.
And now these are the numbers like your G 48 that's going to appear in 48.
Ideally with 750.
You would expect about 10 of each, But when I select all these numbers, not including the grand total and come down here and look in the status bar.
The min of 3, there's some numbers were picked as few as three times. And then Max of 19.
So I don't know is that is that suspicious that some numbers were chosen three times and some numbers were chosen as many as 19 times.
That might be OK, and that might just be a problem that I have 750 instead of. 7500 or 75,000 data points.
I did run this again with 75,000 data points and in that case the Min and the Max get closer 941 to 1067 when you would expect 1000 of each right.
So for this to me it looks fairly random.
What I would be concerned about is if you got something like this where there was a high number of selections in the N column.
Not as many in I and G and very few in B and O.
Then they're using a Normal distribution instead of just random numbers between one and 75.
So if you get something like this, yeah, I'd be concerned that it doesn't seem to be working correctly.
Right, so that's using a pivot table to create a frequency distribution.
You could certainly do that with the =FREQUENCY function.
Frankly, it's a lot harder to do than a pivot table.
