March is here and that means March Madness will be starting soon. For those of you that don’t know about this, it is a 64 team College basketball tournament where you keep playing until you lose and there is one team left. Along with the millions of people who watch these games comes the millions of office workers that fill our brackets in their office pool, hoping to win!

If you are like me, **I don’t know much about college basketball, yet every year I fill out a pool **or two in hopes of getting lucky and winning the pot! Since I don’t know much about basketball, I usually just pick the higher seeds for the most part and hope I get lucky. I never do.

So I started thinking that there must be an easier way to do this. And there is! ** With the help of Microsoft Excel and it’s functions, I was able to come up with a spreadsheet that will randomly pick the winners of each game all the way through the tournament. ** However it does it based on the tournament odds that Vegas puts out. Those people know basketball and they tell you what they feel the odds are for each team to win.

So I created the following spreadsheet which will pick the winners of all the games with the press of one button. You can then use it to submit for your office pool. Save yourself time researching stuff and filling out all those games and get a better chance of winning the pool.

**The link to the spreadsheet is here:**

http://excelbyjoe.com/wp-content/uploads/2015/03/NCAA-Picker1.xlsm

The spreadsheet now has 2014 data in there because the 2015 tournament isn’t announced. Once it is, I will add it.

**How does this spreadsheet work**

I use one formula all the way through in picking the games. Let’s go over it. Let’s take the first games from the 2014 tournament as an example.

We are going to look at the game of 5 VCU vs 12 SF Austin. The numbers to the left are how they are ranked in the tournament. The lower the number, the better they are.

In cell B9, I use the following formula which will randomly pick one of the teams of that game:

=IF(RANDBETWEEN(1,VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)+VLOOKUP(A8,’2014 Odds’!A:B,2,FALSE))<=VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE),A8,A9)

Let me break this down. This is an IF statement which will randomly pick a number from 1 to the total of the odds of the two teams and come back with the winner.

=IF(**RANDBETWEEN(1,VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)+VLOOKUP(A8,’2014 Odds’!A:B,2,FALSE))<=VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)**,A8,A9)

The part in bold above is the criteria that the IF statement is checking. If it is TRUE, then it returns A8, which is VCA. If it is FALSE, then it Returns A9 which is SF Austin.

Let’s look at this criteria for the IF statement now. This is where the magic happens.

**RANDBETWEEN(1,VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)+VLOOKUP(A8,’2014 Odds’!A:B,2,FALSE))**<=VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)

So the part is bold above is what Excel is calculating. RANDBETWEEN chooses a random number between the two numbers you supply. So the begining number is 1. The end number is the sum of two VLOOKUP statememnts.

Here is the first one.

VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)

This is looking up cell A9, which is SF Austin in our table of odds. That is the other sheet on this file. Then it pulls the second column which is the odds.

So you will see SF Austin’s odds of winning it all are 500 to 1. Not good odds.

The second VLOOKUP which our formula will add up are the odds for the other team, VCU.

VLOOKUP(A8,’2014 Odds’!A:B,2,FALSE)

It is virtually the same formula except it is looking up A8, which is VCU.

Here you will see VCU has odds of 66 to 1. A lot better than 500 to 1, so they should win more often.

So now, the complete IF statement criteria is :

IF(RANDBETWEEN(1,VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)+VLOOKUP(A8,’2014 Odds’!A:B,2,FALSE))

Essentially the formula will pick a random number between 1 and 566 (500+66)

Now comes the spot where Excel will evaluate it.

=IF(RANDBETWEEN(1,VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)+VLOOKUP(A8,’2014 Odds’!A:B,2,FALSE))**<=VLOOKUP(A9,’2014 Odds’!A:B,2,FALSE)**,A8,A9)

So the bolded part above is asking if the random number chose is less than or equals to the odds of the team in A9, which is SF Austin. Those odds are 500. So if Excel is picking a random number between 1 and 566, its very good odds that it IS LESS THAN 500.

If it is, then it returns cell A8, which is VCU. If it isn’t, then it returns cell A9, which is SF Austin.

So this would make sense because most times Excel will choose a number lower than 500 if only picking between 1 and 566. And odds are likely that VCU will most likely beat SF Austin. However, there is a 10% chance roughly that VCU will lose.

And that’s where the fun of this spreadsheet is!

You can press the macro button I made to recalculate this over and over and it only takes a second.

So I hope you learned something about stringing together some Excel functions to see how powerful they can be when you know how to get them to work!

If you would like more information on our program Master Excel Functions, which is a 114 page ebook and 4.5 hours of video, then go to :

http://excelbyjoe.com/excel-software-solutions/master-excel-functions/

## Leave a Reply

You must be logged in to post a comment.