March Madness has begun and commissioners in offices throughout the US are using the office copier this morning to distribute copies of the NCAA bracket found in the USA Today. While you are invited to join the free MrExcel.com/ncaa.html contest, todays Episode 462 takes a look at the life of the NCAA commissioner using formulas to pre-enter your bracket selections in Excel.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm bill Jelen.
On Friday invited you to download my NCAA bracket. It's it MrExcel.comNCAA.html.
In case you're not from the United States the next three weeks are kind of crazy. It's called March Madness.
It's the men's college National championship.
And there are 64 teams that enter the bracket starting actually that the first game is Thursday this week.
Offices throughout the country have generally an NCAA pool.
Where people choose their winners all the way through you get one point for every correct team in round one, 2 points in round two, 3 points in round three and so on there's a total of sixty three games.
And it really kind of keeps everyone interested in the bracket even if the local team is knocked out, and so once you fill out that bracket and send it in then we go through the tedious process on the back end of the commissioner has to go through and enter all of your picks.
One thing that we learned early on is that most people choose the higher seeded team most of the way through.
For example, in round one with 32 games most people will choose the higher seed maybe, 24 of those games so the process of entering your selections really comes down to just finding the places where you chose an upset and entering the other code.
Now, this large spreadsheet here is a very wide spread sheet that has all 63 games scheduled across the spreadsheet and we enter your names going down the left hand side here.
One thing that I added in order to make this very easy.
Basically, we just have to find the games where you chose someone other than the higher seeded team and then choose the other team.
So, now this is a 2006 spreadsheet. e had West Virginia playing Southern Illinois.
If you wanted to choose Southern Illinois or West Virginia, I could enter the two digit code but I wrote a quick MACRO.
I just hit a keystroke and it basically, toggles to the other team.
The first round teams are basically pointing up here to the top of the spreadsheet where I have a list of the teams.
But as we get over to round two, so let me choose that let me choose Southern Illinois over West Virginia and now, we'll go over to round 2, game 3 around 2, where we have to know that it is Southern Illinois playing.
And now if, I hit control+A, it will bring in the other team that you chose before.
So, the first set of formulas are basically pointing up to row 4 but, then after that we have a set of formulas that are basically pointing back to whoever you chose in the earlier route.
Now, tomorrow we'll take a look at one of the fail safe formulas, I have out on the right hand side to make sure that we didn't enter any teams completely wrong.
Thanks for stopping by and remember.
If we're your basketball fan or not please stop to MrExcel.comNCAA.html.
Download the bracket and enter it's free you have a chance to win some great books and you'll definitely have some fun over the next few weeks
I'm bill Jelen.
On Friday invited you to download my NCAA bracket. It's it MrExcel.comNCAA.html.
In case you're not from the United States the next three weeks are kind of crazy. It's called March Madness.
It's the men's college National championship.
And there are 64 teams that enter the bracket starting actually that the first game is Thursday this week.
Offices throughout the country have generally an NCAA pool.
Where people choose their winners all the way through you get one point for every correct team in round one, 2 points in round two, 3 points in round three and so on there's a total of sixty three games.
And it really kind of keeps everyone interested in the bracket even if the local team is knocked out, and so once you fill out that bracket and send it in then we go through the tedious process on the back end of the commissioner has to go through and enter all of your picks.
One thing that we learned early on is that most people choose the higher seeded team most of the way through.
For example, in round one with 32 games most people will choose the higher seed maybe, 24 of those games so the process of entering your selections really comes down to just finding the places where you chose an upset and entering the other code.
Now, this large spreadsheet here is a very wide spread sheet that has all 63 games scheduled across the spreadsheet and we enter your names going down the left hand side here.
One thing that I added in order to make this very easy.
Basically, we just have to find the games where you chose someone other than the higher seeded team and then choose the other team.
So, now this is a 2006 spreadsheet. e had West Virginia playing Southern Illinois.
If you wanted to choose Southern Illinois or West Virginia, I could enter the two digit code but I wrote a quick MACRO.
I just hit a keystroke and it basically, toggles to the other team.
The first round teams are basically pointing up here to the top of the spreadsheet where I have a list of the teams.
But as we get over to round two, so let me choose that let me choose Southern Illinois over West Virginia and now, we'll go over to round 2, game 3 around 2, where we have to know that it is Southern Illinois playing.
And now if, I hit control+A, it will bring in the other team that you chose before.
So, the first set of formulas are basically pointing up to row 4 but, then after that we have a set of formulas that are basically pointing back to whoever you chose in the earlier route.
Now, tomorrow we'll take a look at one of the fail safe formulas, I have out on the right hand side to make sure that we didn't enter any teams completely wrong.
Thanks for stopping by and remember.
If we're your basketball fan or not please stop to MrExcel.comNCAA.html.
Download the bracket and enter it's free you have a chance to win some great books and you'll definitely have some fun over the next few weeks
