Four co-workers play 8-ball every day at lunch. They are currently calculating win rate on a white board. Can Excel be used to track the win rate of each player? Although the person is new to Excel, this video goes through a few somewhat advanced topics - Freeze Panes, Ctrl+T tables, Fill Handle, and a pair of COUNTIF functions.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1776: Lunch Break 8-Ball Win Rates.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Long, long question actually sent in at YouTube and I don't have the first part of the question here.
It was so long. It was longer than YouTube would do, but 4 people at their lunch break are playing 8-ball and, right now, on a whiteboard, they're keeping track of who wins each game, who wins, who loses, and they're trying to calculate the win rate.
So, if, you know, you played 7 games…or you played 10 games and you won 7 of those 10, then you have a 70% win rate.
No experience with Excel.
Can this be done in Excel?
Of course this can be done in Excel.
So, here's what I am proposing.
We’ll hide that for right now.
I'm proposing setting up some headings here with date, who the winner was, and who the loser was.
So, as each game is played, you don't bother to fill anything until we know who won and who lost.
We have 4 people here -- ANDY, BETTY, CHARLIE, and DON -- and they say it's all random, you know.
They're not playing the same person time after time after time, and I would put in today's date.
Now, the fast way to do that is CONTROL+; will put in today's date.
I'm actually recording this on Friday, record the date, record who the winner was, and record who the loser was, and just keep going.
Now, here's my first Excel trick for you.
We want to make sure that we could always see these headings in the summary table at the top even as we get more data than what appears on the screen.
So, we're going to choose the very first cell that we don't want to freeze, come here to VIEW, FREEZE PANES, FREEZE PANES.
That's going to allow us to scroll down and always see rows 1 through 8 at the top, 1 through 8 at the top, and I am guessing that you're going to keep adding new data day after day after day.
Every work day, you're going to come in and play a few more games during lunch break.
So, let’s make this table something that can expand.
I'm going to choose one cell in the table, and CONTROL+T. MY TABLE HAS HEADERS.
That's right, click OK, and it does apply some nice formatting but it also makes these formulas up here formulas that will expand so that way, as you get more data -- for example, if we come along on 7/20/13 and BETTY beats ANDY -- that data will automatically become part of these formulas.
Alright.
So, the number won, the number of games that are won.
We're going to do =COUNTIF.
COUNTIF says we're going to look through a range and the range here is going to be from A9.
I'm going to hold down CONTROL+SHIFT+DOWNARROW to select that whole range, and you see, because it's a table, they use this formula nomenclature which is unusual to most people who use Excel but you're new to Excel so it's going to be natural to you.
It’s saying this is table 3, the winner column, and we're going to count how many times that winner is = to ANDY, alright, and I will press ENTER there.
[ =COUNTIF(Table2[ Winner ],A2) ] ANDY won 131 games.
This little square dot here, see, watch my mouse pointer, is a white plus + sign.
When I get to the black + sign, I'm going to click and drag down, and now, that's amazing, alright?
So, ANDY won 31, BETTY won 37, CHARLIE won 39, and DON won 38, but we want to know the total number of games.
So, here we're going to =COUNTIF.
COUNTIF.
This time, the range that we're going to look through, I'm going to start…now, let's see.
Now, I'm going to click here in B8, I'm going down one using the down arrow, CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW to select this range.
So, it's table 2 from the winner to the loser column, see how many of those are = to again ANDY, click on ANDY over there, and so ANDY played a total of 65 games.
[ =COUNTIF(Table2[ [ Winner ]:[ Loser ] ],A2) ] You see, not everyone plays the same number of games.
That's just the way it goes.
Here, it looks like CHARLIE and DON are playing more often.
Maybe they're faster players.
It's hard to say.
Alright, so, we know how many games they won and the total number of games.
The win rate then is we type an = sign, click on 1, click the /, that's the divide in Excel, and click on games, and we get 47%.
Let's format that as a %age, choose whether you want one decimal place or not, and copy it down.
[ =B2/C2 ].
Alright.
So, there you go, we have data, and what's really cool about Excel is Excel will continue to calculate.
So, right here, CHARLIE and DON, it looks like they've been playing a lot.
Let's just hypothetically say -- right now BETTY is in the lead with a 61% win rate -- let's hypothetically say that CHARLIE beats BETTY the next 10 games, alright?
So, they come in on 7/20.
I'm going to fill all those in, CONTROL+ENTER, and CHARLIE beats BETTY 10 games straight.
What are the odds of that?
Well, look.
Just as soon as you enter that data, all of these formulas here and these formulas here recalculate, and the win rate changes as well.
Excel is great, much more efficient than the whiteboard that you've been using to keep track of all these because, every time someone plays a new game, you’d have to redo all of the math, and Excel is going to do that for you.
Great question.
Hope you enjoy using this for your daily 8-ball lunch break tournament.
Well, hey.
I want to thank you for sending that question in and I want to thank everyone for stopping by.
We’ll see you next time for another netcast from MrExcel.
Well, hey.
They’re saying all the kids have left Facebook.
The only people left on Facebook are people my age.
So, if you are still on Facebook…I have 2 accounts out there.
I have Bill Jelen and I have mrexcel.com.
MrExcel’s where you'll find interesting articles.
I post a lot of things to Twitter.
I post the really interesting things to Facebook.
So, go out to Facebook, search for mrexcel.com, and become a fan of our site.
You'll, you know, see one or two things a week, interesting articles get posted out there.
Learn Excel From MrExcel, Podcast Episode 1776: Lunch Break 8-Ball Win Rates.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Long, long question actually sent in at YouTube and I don't have the first part of the question here.
It was so long. It was longer than YouTube would do, but 4 people at their lunch break are playing 8-ball and, right now, on a whiteboard, they're keeping track of who wins each game, who wins, who loses, and they're trying to calculate the win rate.
So, if, you know, you played 7 games…or you played 10 games and you won 7 of those 10, then you have a 70% win rate.
No experience with Excel.
Can this be done in Excel?
Of course this can be done in Excel.
So, here's what I am proposing.
We’ll hide that for right now.
I'm proposing setting up some headings here with date, who the winner was, and who the loser was.
So, as each game is played, you don't bother to fill anything until we know who won and who lost.
We have 4 people here -- ANDY, BETTY, CHARLIE, and DON -- and they say it's all random, you know.
They're not playing the same person time after time after time, and I would put in today's date.
Now, the fast way to do that is CONTROL+; will put in today's date.
I'm actually recording this on Friday, record the date, record who the winner was, and record who the loser was, and just keep going.
Now, here's my first Excel trick for you.
We want to make sure that we could always see these headings in the summary table at the top even as we get more data than what appears on the screen.
So, we're going to choose the very first cell that we don't want to freeze, come here to VIEW, FREEZE PANES, FREEZE PANES.
That's going to allow us to scroll down and always see rows 1 through 8 at the top, 1 through 8 at the top, and I am guessing that you're going to keep adding new data day after day after day.
Every work day, you're going to come in and play a few more games during lunch break.
So, let’s make this table something that can expand.
I'm going to choose one cell in the table, and CONTROL+T. MY TABLE HAS HEADERS.
That's right, click OK, and it does apply some nice formatting but it also makes these formulas up here formulas that will expand so that way, as you get more data -- for example, if we come along on 7/20/13 and BETTY beats ANDY -- that data will automatically become part of these formulas.
Alright.
So, the number won, the number of games that are won.
We're going to do =COUNTIF.
COUNTIF says we're going to look through a range and the range here is going to be from A9.
I'm going to hold down CONTROL+SHIFT+DOWNARROW to select that whole range, and you see, because it's a table, they use this formula nomenclature which is unusual to most people who use Excel but you're new to Excel so it's going to be natural to you.
It’s saying this is table 3, the winner column, and we're going to count how many times that winner is = to ANDY, alright, and I will press ENTER there.
[ =COUNTIF(Table2[ Winner ],A2) ] ANDY won 131 games.
This little square dot here, see, watch my mouse pointer, is a white plus + sign.
When I get to the black + sign, I'm going to click and drag down, and now, that's amazing, alright?
So, ANDY won 31, BETTY won 37, CHARLIE won 39, and DON won 38, but we want to know the total number of games.
So, here we're going to =COUNTIF.
COUNTIF.
This time, the range that we're going to look through, I'm going to start…now, let's see.
Now, I'm going to click here in B8, I'm going down one using the down arrow, CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW to select this range.
So, it's table 2 from the winner to the loser column, see how many of those are = to again ANDY, click on ANDY over there, and so ANDY played a total of 65 games.
[ =COUNTIF(Table2[ [ Winner ]:[ Loser ] ],A2) ] You see, not everyone plays the same number of games.
That's just the way it goes.
Here, it looks like CHARLIE and DON are playing more often.
Maybe they're faster players.
It's hard to say.
Alright, so, we know how many games they won and the total number of games.
The win rate then is we type an = sign, click on 1, click the /, that's the divide in Excel, and click on games, and we get 47%.
Let's format that as a %age, choose whether you want one decimal place or not, and copy it down.
[ =B2/C2 ].
Alright.
So, there you go, we have data, and what's really cool about Excel is Excel will continue to calculate.
So, right here, CHARLIE and DON, it looks like they've been playing a lot.
Let's just hypothetically say -- right now BETTY is in the lead with a 61% win rate -- let's hypothetically say that CHARLIE beats BETTY the next 10 games, alright?
So, they come in on 7/20.
I'm going to fill all those in, CONTROL+ENTER, and CHARLIE beats BETTY 10 games straight.
What are the odds of that?
Well, look.
Just as soon as you enter that data, all of these formulas here and these formulas here recalculate, and the win rate changes as well.
Excel is great, much more efficient than the whiteboard that you've been using to keep track of all these because, every time someone plays a new game, you’d have to redo all of the math, and Excel is going to do that for you.
Great question.
Hope you enjoy using this for your daily 8-ball lunch break tournament.
Well, hey.
I want to thank you for sending that question in and I want to thank everyone for stopping by.
We’ll see you next time for another netcast from MrExcel.
Well, hey.
They’re saying all the kids have left Facebook.
The only people left on Facebook are people my age.
So, if you are still on Facebook…I have 2 accounts out there.
I have Bill Jelen and I have mrexcel.com.
MrExcel’s where you'll find interesting articles.
I post a lot of things to Twitter.
I post the really interesting things to Facebook.
So, go out to Facebook, search for mrexcel.com, and become a fan of our site.
You'll, you know, see one or two things a week, interesting articles get posted out there.
