Using Google Sheets to make a March Madness Bracket Pool

I've had a request for a template to use for keeping track of a Pool.  You can find the template below.  Instructions for use are on the Help tab.  Basically, this template can hold up to 50 participants.  Each participant has their own tab.  The tab must be named with the person's name followed by 's Bracket.  For example, mine would be called Ted Juch's Bracket.  You will also need to put the players name on their individual sheet in the name field and on the Bracket Participants tab.  People's selections are put in the yellow fields on their individual bracket tab.  If everything is named correctly, everything will work automatically!  Enjoy!

Google Sheets March Madness Bracket Pool 2013 Template

If you have any questions, please post them in the comments and I'll support from there.

Using Google Sheets for March Madness 2013

Google did not release a new bracket so I've updated mine a little bit and posted the template below.  Click on the link below, go to File>Make a copy.  Make sure to check out the Helper tab for how things work.  Enjoy!

Google Sheets - March Madness Bracket 2013 Template

Barack Obama, Alex Morgan, Ted Juch and March Madness

Well, we know who the Elite 8 are for the NCAA Men's College Basketball Tournament.  How is your bracket doing?  I'm sitting somewhere between Alex Morgan and Barack Obama.

I've seemed to iron out most of the kinks for using Gdocs to run your blog so I'm posting a small pool that I created below.  There have been a few more feature requests but unfortunately I haven't gotten around to doing them.  This just leaves room for more improvements for next year!

Alex, Barack, and Ted's Pool

Enjoy!

Using TedTournament() vs GoogleTournament()

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:

Group Bracket

Individual Bracket

An update to vlookup and arrayformula

For those trying to use arrayformula in Google Spreadsheets, I thought I would give a few examples that might help when designing your function.  In addition, I've provided a general example where you can fill in your corresponding ranges to build the function.  Let me know if you have any problems!

To customize use this key.

=arrayformula(if(row([range1])=1,"Impossible",vlookup([range1],[range2],{N}*sign(row([range1])),FALSE)))

where

[range1]: range of values to be looked up

[range2]: table of data where look up values are in column 2

N: number of column that has the data you want to return


I've also embedded a spreadsheet here that shows a few examples of how to use this:


Also, if you want to play with this spreadsheet feel free to make your own copy here:
Using arrayformula with vlookup template

Cheers!

Methods of Depreciation: A Template

One of my classes in grad school is called Health Care Accounting.  We are tasked with concepts from Financial Accounting, Managerial Accounting, and topics from Financial Reporting.  Most recently we discussed methods of Depreciation.  In our context, we looked at depreciation in terms of how we allocate costs of an asset that has a usable life of some x years.  The template I created requires the user to input original cost of the asset, the salvage value (if applicable, otherwise $0) and the usable life in years.  The three methods I created the template for are Straight-line, Double-Declining Balance and Sum-of-the Years' Digits.

If you are interested in any of the functions feel free to comment!

Check it out!  This is also my first post to the Google Docs Template Gallery (which is kind of broken right now). Please rate it once its up! If you would like your own copy or to play around with it, simply choose File>Make A Copy from the view-only version below.

Enjoy!

Depreciation Methods Template