Google updated their function yesterday and released a template today. I had nothing to do with the template that was released today but it was a copy of last year's with the year replaced as 2012. This proof is in 'Full Bracket'!C7 and any of the play-in games if you are curious. So if you want the updates you'll have to use my versions.
That being said, my templates should be functioning correctly. I've modified the back end so that Google's shortnames match up with Adam Bartlett's guesses at the shortnames. Therefore, assuming everything flows correctly on Google's end, I won't have to manually update my master spreadsheet. It will be updated as soon as GoogleTournament() gets updated.
Since this is a Docs blog I'll explain how I automated the back end. My master sheet before had empty cells where I would update the teams as they won. This means that if I am not near a computer or internet, you would have to wait and this really wasn't acceptable to me. Now, my mastersheet combines a vlookup, googletournament, and iferror. I created a table, which I will call "Helper" that lists Google's shortnames in column A and column B has Adam's names.
It looks something like =iferror(vlookup(googletournament(year,"men",round,slot,"statistic,team),Helper,2,FALSE))
This finds the shortname of the team using Google Tournament, matches it up with Adam's version of the shortname and returns Adam's version (which matches our template). If vlookup fails then it returns blank. The vlookup only fails with googletournament fails which indicates a game hasn't been played yet.
Anyways, I think my template is more robust that Google's template and easier to use, but the bugs are still being ironed out. Unfortunately, I can only update my template for any bugs that are found and not your own copies of my template. I'm trying to make any updates easily translatable.
Update:
There was an error in the point calculations, which I should have noticed from the beginning. There is an extra row in the full template vs the blank template. This means that my method of assigning points wasn't matching correctly. I've updated the Points sheet to calculate correctly. You can either make a new copy of my template or copy and paste D5:F67 from the points tab on my template to yours. You will have to unhide D:E and then rehide after you make the adjustments. The Single Bracket has also been updated. Replace G4 on the points tab with:
=if(F4="TBD",iferror(1/0),indirect("'"&G$1&"'!"&$E4&$D4-1)) <--Drag this all the way down to G66
and I4 with
=arrayformula(if(len(F4:F)>0,if(G4:G=F4:F,C4:C,0),0)) <--You don't have to drag this.
Next week I'll update the Google Sites template to show how all of this could be displayed through Google Sites. I'm planning on using 5 brackets in the pool along with Obama's. Should be fun to see how people match up!
Happy Madness!
The follow links are the same links from my original post:
"Michigan St." is what was selected as Adam's short name, but "Michigan St" (without the period) is what it is being compared to, so they are not identical, so no one is getting credit for the win.
ReplyDelete