-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
March Madness
- Thread starter thom5962
- Start date
Excel Facts
Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
MrExcel
.
- Joined
- Feb 8, 2002
- Messages
- 3,410
- Office Version
- 365
- Platform
- Windows
Thanks for the reminder!
I've been posting results regularly at http://www.mrexcel.com/marchmadnessresults.html, but I will have to write up more tips about how the actual spreadsheet works.
Bill
I've been posting results regularly at http://www.mrexcel.com/marchmadnessresults.html, but I will have to write up more tips about how the actual spreadsheet works.
Bill
MrExcel
.
- Joined
- Feb 8, 2002
- Messages
- 3,410
- Office Version
- 365
- Platform
- Windows
Here is how I handle that....
Until a game is played, I have a formula that builds a text formula of all the possible teams who can win a game. For example, one of tonights games consists of the possibilities of "CT,WA". (Each team has a 2-character code).
The formulas for later rounds continue to build this text string. Right now, the formula for the final game consists of this value: "LS,TX,ME,LA,CT,WA,GM,WS,VI,BC,FL,GE". This indicates that any of LSU, UCLA, Texas, Memphis, UCLA, UConn, Washington, Geo Mason, Wichita State, Villanova, Boston College, Florida, Georgetown are still valid possible winners for the final game.
Each text string is comprised of a formula that joins the possible winners from the previous 2 games. =BL4&","&BL5.
Tonight, if UConn beats Washington, I will overwrite the formula for Round 3 Game 5 with CT. This will make the possible teams for Round 6 Game 1 have one less possible entry.
I have a large matrix of everyone's picks. To figure out if player 14 still has a possible chance of winning Round 6 Game 1, I use =FIND(BL27,BL$7). This looks for this player's pick within that long text string of the possible winners. If it is found, I get a number. If it is not found, I get a #VALUE! error. So, the actual formula to track a known future loss becomes:
=IF(ISERR((FIND(BL27,BL$7,1)-1)),1,0)
This will make more sense when I include screen shots this weekend.
Bill
Until a game is played, I have a formula that builds a text formula of all the possible teams who can win a game. For example, one of tonights games consists of the possibilities of "CT,WA". (Each team has a 2-character code).
The formulas for later rounds continue to build this text string. Right now, the formula for the final game consists of this value: "LS,TX,ME,LA,CT,WA,GM,WS,VI,BC,FL,GE". This indicates that any of LSU, UCLA, Texas, Memphis, UCLA, UConn, Washington, Geo Mason, Wichita State, Villanova, Boston College, Florida, Georgetown are still valid possible winners for the final game.
Each text string is comprised of a formula that joins the possible winners from the previous 2 games. =BL4&","&BL5.
Tonight, if UConn beats Washington, I will overwrite the formula for Round 3 Game 5 with CT. This will make the possible teams for Round 6 Game 1 have one less possible entry.
I have a large matrix of everyone's picks. To figure out if player 14 still has a possible chance of winning Round 6 Game 1, I use =FIND(BL27,BL$7). This looks for this player's pick within that long text string of the possible winners. If it is found, I get a number. If it is not found, I get a #VALUE! error. So, the actual formula to track a known future loss becomes:
=IF(ISERR((FIND(BL27,BL$7,1)-1)),1,0)
This will make more sense when I include screen shots this weekend.
Bill
PA HS Teacher
Well-known Member
- Joined
- Jul 17, 2004
- Messages
- 2,838
I've used a similar strategy, creating a list of "losers" after each round.
I use a different convention for keeping track of Teams.
East1
East2
...
South16
I also use a naming convention for each game,
East101
East202
West 401 etc.
The 100's are rd 1, into the rd of 32
The 500's are into the Semi's, but I also have a Final Four Sheet where the games are called SemiEastWest, and SemiNorthSouth. The final winner is called champion.
I use lookupfunctions and a table to return this years actual team names. This year, I went a little crazy and used userforms for entering the brackets, and allowing for upset picks for each round. Its not at 100% yet, but it scores correctly, enters data correctly, and most of the statistics are working. If anyone wants to take a look at it and give me soem feedback, just pm your e-mail.
I use a different convention for keeping track of Teams.
East1
East2
...
South16
I also use a naming convention for each game,
East101
East202
West 401 etc.
The 100's are rd 1, into the rd of 32
The 500's are into the Semi's, but I also have a Final Four Sheet where the games are called SemiEastWest, and SemiNorthSouth. The final winner is called champion.
I use lookupfunctions and a table to return this years actual team names. This year, I went a little crazy and used userforms for entering the brackets, and allowing for upset picks for each round. Its not at 100% yet, but it scores correctly, enters data correctly, and most of the statistics are working. If anyone wants to take a look at it and give me soem feedback, just pm your e-mail.
