The first entries in the NCAA office pool are starting to come in and the commissioner of the NCAA pool will have to key your entries from the faxed USA Today bracket into the spreadsheet. In the MrExcel spreadsheet, concatenation formulas help to make sure that the teams keyed for each player are correct. Episode 463 shows you how to use FIND, ISERR, and IF functions to check to make sure your data entries are in a list of valid values.
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:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This week it's the beginning of March Madness.
The NCAA men's college basketball tournament in the United States.
And I want to encourage you to enter our competition go to "MrExcel.comNCAA.html" to download your bracket, fill it out. Send it in you can win some great Excel books.
It's completely free of course, no gambling here.
We're talking about the commissioner worksheet.
This is the worksheet, where the commissioner keeps track of all the entries and basically it's a big pain because going up to Thursday at noon, will be sending brackets in and you have to enter you know 63 teams for everyone and you want to make sure that you didn't enter anyone's teams, incorrectly.
One thing that we have interesting in our commissioner spreadsheet is that we showed the top seed and the bottom seed for each game.
So, here let's say, West Virginia was the top seed and Southern Illinois was the bottom seed.
Again, this is last year's bracket and what we do then in the next row, in row 7 is show a list of all the possible teams.
So, right here in row 7.
Let me expand this in round-1, It shows that the two possible teams that could win that game are West Virginia, or Southern Illinois.
Now, if we go out to round-2 to look at that equivalent game, the formula continues to build.
So, if I make this wider, you'll see that the possible teams here are West Virginia, Southern Illinois Iowa or Northwestern.
Those are the four possible teams that could have won that game in round-2 and as we go out even further.
Let's go out to round-3 and make it a little bit wider.
You'll see that there are 8 possible teams that could win that game.
Now, as I fill in winners earlier in the bracket.
This field that shows the possible will continue to narrow down.
It's basically a formula says, we're in take two possible teams from round-2.
Concatenate it, using the ampersand (&) with a comma and the possible teams from the other game in round-2.
Now, by the time we get down to the very last formula, of course all 63 teams are here, initially.
Well, that creates a real interesting opportunity for us to make sure that the codes that we've entered for each individual entry actually show up in that formula.
Let me scroll all the way down to a work area that we have in the worksheet and we're using a couple of interesting formulas.
It's called the 'fine' formula.
Basically, we're saying find this player's selection for game-1, in the list of possible selections for game-1.
The fine function is an interesting function.
If it finds Duke in the combination of Duke in Southern, then it returns a number for us.
If it doesn't, then it returns an error.
So, I have to use the is error function is ERR will tell us whether or not, we have an error or not.
And basically what happens...
Let me go up and enter the wrong value for this player.
I put in, an X Y and then we scroll down that causes this formula at the bottom to show up as a 1.
Basically, after I've entered all the players pics.
I need to just come through here and make sure that the total of all of these values is 0.
Any 1s that I have, I know that I made an error in entering the information.
Now again, 1 invite you to download our bracket, MrExcel.com/NCCA.html completely free to play even if you know nothing about basketball.
There are years, or you have the best chance of winning.
Thanks for stopping by, we'll see you tomorrow for another netcast from MrExcel.
I'm Bill Jelen.
This week it's the beginning of March Madness.
The NCAA men's college basketball tournament in the United States.
And I want to encourage you to enter our competition go to "MrExcel.comNCAA.html" to download your bracket, fill it out. Send it in you can win some great Excel books.
It's completely free of course, no gambling here.
We're talking about the commissioner worksheet.
This is the worksheet, where the commissioner keeps track of all the entries and basically it's a big pain because going up to Thursday at noon, will be sending brackets in and you have to enter you know 63 teams for everyone and you want to make sure that you didn't enter anyone's teams, incorrectly.
One thing that we have interesting in our commissioner spreadsheet is that we showed the top seed and the bottom seed for each game.
So, here let's say, West Virginia was the top seed and Southern Illinois was the bottom seed.
Again, this is last year's bracket and what we do then in the next row, in row 7 is show a list of all the possible teams.
So, right here in row 7.
Let me expand this in round-1, It shows that the two possible teams that could win that game are West Virginia, or Southern Illinois.
Now, if we go out to round-2 to look at that equivalent game, the formula continues to build.
So, if I make this wider, you'll see that the possible teams here are West Virginia, Southern Illinois Iowa or Northwestern.
Those are the four possible teams that could have won that game in round-2 and as we go out even further.
Let's go out to round-3 and make it a little bit wider.
You'll see that there are 8 possible teams that could win that game.
Now, as I fill in winners earlier in the bracket.
This field that shows the possible will continue to narrow down.
It's basically a formula says, we're in take two possible teams from round-2.
Concatenate it, using the ampersand (&) with a comma and the possible teams from the other game in round-2.
Now, by the time we get down to the very last formula, of course all 63 teams are here, initially.
Well, that creates a real interesting opportunity for us to make sure that the codes that we've entered for each individual entry actually show up in that formula.
Let me scroll all the way down to a work area that we have in the worksheet and we're using a couple of interesting formulas.
It's called the 'fine' formula.
Basically, we're saying find this player's selection for game-1, in the list of possible selections for game-1.
The fine function is an interesting function.
If it finds Duke in the combination of Duke in Southern, then it returns a number for us.
If it doesn't, then it returns an error.
So, I have to use the is error function is ERR will tell us whether or not, we have an error or not.
And basically what happens...
Let me go up and enter the wrong value for this player.
I put in, an X Y and then we scroll down that causes this formula at the bottom to show up as a 1.
Basically, after I've entered all the players pics.
I need to just come through here and make sure that the total of all of these values is 0.
Any 1s that I have, I know that I made an error in entering the information.
Now again, 1 invite you to download our bracket, MrExcel.com/NCCA.html completely free to play even if you know nothing about basketball.
There are years, or you have the best chance of winning.
Thanks for stopping by, we'll see you tomorrow for another netcast from MrExcel.
