2010-11 College Football Bowl Pool Spreadsheet

Looking for the 2011-12 files? Go here.

Okay, folks. You've waited long enough. Here's this year's college football bowl pool sheet:



2010-11 Bowl Pool Summary Sheet



And here's the individual picks sheet you can pass around to those participating in your pool to copy-paste their picks back into the Summary sheet file (just remember, Copy --> Paste Special... --> Values is your friend):



2010-11 Bowl Pool Individual Picks Entry Sheet



As always, if you find any issues, please let me know. If you've got a cool idea for an enhancement, do the same.



Enjoy,



Excel_Geek

Yes, I'm doing a college bowl pool spreadsheet

Recent MeeboMe inquiry:

[12:25] meeboguest######:Are you going to have a college bowl spreadsheet for this year since there is one extra bowl from last year? The spreadsheet is wonderful.


Yes. Don't worry folks. There will be a new 2010 NCAA Football Bowl pool spreadsheet. Any ideas how to make it cooler than last year's?

SUMPRODUCT that only includes visible rows

The other day a reader contacted me through my MeeboMe chat window. He (or she...I'll use "he" throughout for simplicity) wanted help transforming a SUMPRODUCT formula that he currently uses to be useful in situations where the data is filtered or for some other reason certain rows are hidden. Basically he wants the formula only to work on rows that are visible. Now, this reader reached out a couple of times, but I was never able to get him a solution (I had some clarifying questions.) before he went silent on me. I thought providing a solution to all of my readers on this issue might be warranted, though. This is a fairly common issue Excel users might face in real life.

The answer, basically, is the SUBTOTAL function coupled with the OFFSET, ROW, and MIN functions being embedded into the SUMPRODUCT function. Whoa! Right? Let's break this down.

SUMPRODUCT works like this:

=SUMPRODUCT(<Range1>,<Range2>,[<Range3>...])

The values in each row of each range are multiplied together and then added up. In its simplest form, it's a great way to calculate things like weighted averages. The problem is that is will include every row in the ranges, even those hidden due to filtering or otherwise.

What do we know that doesn't include hidden rows? That's right SUBTOTAL has options to exclude hidden rows. Using subtotal type "109" is a sum that ignores hidden rows. So here's how to incorporate SUBTOTAL with OFFSET and MIN to make this work:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(<Range1>,ROW(<Range1>)-MIN(ROW(<Range1>)),,1,1)),<Range2>)

The crazy OFFSET, ROW, MIN combo is a way to coerce Excel to treat each cell in Range1 individually, like it does automatically when simply referencing the range in SUMPRODUCT, while still having the doesn't-count-hidden-rows benefit of SUBTOTAL type 109.

It also occurred to me that many Excel users may be using SUMPRODUCT to perform summing of data based upon multiple criteria -- you know, SUMIFS before Excel 2007 came out and we had SUMIFS. Here's how you'd do that using this technique:

Doesn't exclude hidden rows:

=SUMPRODUCT((<Range1>)*(<Range2>="West")*(<Range3>="Product A"))

This would add up "Sales" (Range1) where the "Region" (Range2) is equal to "West" and the "Product" (Range3) is equal to "Product A".

Excluded hidden/filtered rows:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(<Range1>,ROW(<Range1>)-MIN(ROW(<Range1>)),,1,1))*(<Range2>="West")*(<Range3>="Product A"))

I hope this explanation has been helpful. Excel_Geek Insiders subscribers, I'll fire you off a sample spreadsheet illustrating these techniques.

Later,

Excel_Geek

Pie Chart Style Status Icons

Today a visitor stopped by the blog and sent me this message via my MeeboMe chat window:

[14:40] meeboguest######: I'm trying to insert a 4 section circle icon that is shaded in different ways. I'm using it to track progress on different projects. So I need a total of five different icons. completely unshaded, 1/4 shaded, 1/2 shaded, 3/4 shaded, and 4/4 shaded. where do i find these icons?

Sadly, I was away from my computer at the time. Here's my suggestion anyway:

I don't know of a good set, but these are nice to have for your dashboards and status reports. I say just create your own. Here's a simple set I created in 5 minutes using Microsoft Paint. I'm sure others among you are far more creative, so feel free to comment and share your icon collections.

Combine these images with my post on Pulling Images in Excel, and you could have a pretty slick status report. Be sure to remember the work-around when using Excel 2007.

Enjoy...

Excel_Geek

Chloropleth Maps in Excel

A colleague recently pointed me to a very cool example of creating a chloropleth map directly in Excel.

Wow. Just wow. And if you look at the map itself, you'll see that someone (apparently) meticulously drew out each province as a shape object. (Right-click one of them and you'll see the tell-tale "Edit Points" option.) The VBA written to make this work is also very succinct and simple to follow. Anyone seen one of these for the U.S.?

This is clearly the best example of mapping directly in Excel that I've ever seen. I thought you all might enjoy it as well.

That's all for now...

Get Your 2010 March Madness Pool Manager Here!

Alright, folks. It's done. And, as I've been telling you...it's going to be absolutely free this year. Here's how it'll work: the "Master Bracket" file -- the one you use as the pool manager -- still requires you to input and submit a "key code" to unlock the file for use. I've pre-filled this in for you. You'll simply need to click "submit" and accept the terms of use to begin using the file. You'll notice that the file will be open for use until May 1st of this year. (Hey, if I even want to think about ever charging for this again, I can't simply give away all the goods, right?)

The "Standard Bracket" is the file that others in your pool will use to make their picks and track their own progress.

Now, I'm not going to be available to help people with technical difficulties this year (part of the reason I'm giving it away for free this year), so let me tell you the most common reason people have trouble using the file. They don't have macros enabled in Excel. If this happens to you, simply go to Tools --> Macro --> Security... and make sure your security level is set to Medium or Low. I'd recommend Medium, as it will always prompt you to enable macros upon opening a file with macros embedded. Once you've done this, close the file and then reopen the file. You should be prompted to enable macros.

Here are the files:

March Madness Master Bracket by Excel_Geek v.2010a.xls

March Madness Standard Bracket by Excel_Geek v.2010a.xls

Enjoy!

2010 March Madness Pool Spreadsheet Coming Soon

Yes. I will be doing a March Madness Pool Spreadsheet this year, just like last year's with one exception: this year's will be free! I'll still have some lock-down on the file, though.



It should be ready after all the selections are made this Sunday.

Ideas for the College Bowl Pool Sheet for Next Year

Well, folks, it's wrapping up again. Thanks for making this my biggest college bowl pool spreadsheet year ever!



Four remaining games in one of our most beloved sports pool events of the year -- college football bowl game season. This is that point in many of our pools where some age-old questions start to emerge...

• "Do I really have a shot to win this thing?"


• "Do I even have a chance?"


• "Who else is in the running?"


• "Which games are really most important to my chances?"

I intend to give you the power to answer these questions (and maybe more) in next year's pool sheet. Stay tuned...but in the meantime best of luck in this year's pool! (oh, and get ready for March Madness...)