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...)

Alternate Requests for College Bowl Pool Pick 'em Sheets

I've been getting flooded for requests for all types of variations on my traditional college football bowl pool pick 'em sheet. For those of you familiar with mine, it's a straight up pick the winner (not picking against the line) and you assign points to each pick between 1 and 34 (each number used only once) depending on how confident you are in your pick.

I cannot possibly keep up with each and every variation people want, but I have gotten a number of requests for a simpler version that is more like my NFL Weekly Pool sheet that I did not that long ago. This version is picking a winner straight up (no line) and one point is assigned for each correct pick. The tie-breaker is the total combined score of the championship game. This version I made with 30 possible games (I know there are 34 bowl games, but you can figure out how to add the rest if you want to.) and is set up for up to 100 participants.

Excel_Geek Insiders subscribers will get this file for free as part of their subscription. Not a subscriber? Sign up. It's cheap and easy and you get copies of all the $50 Projects I complete.

I'll probably do another version of two of pool sheets.

Later,

Excel_Geek

2009-10 College Football Bowl Pool Spreadsheet

Okay folks...it's ready. Well...sort of.

We won't know whether or not it's Army or UCLA vs. Temple in the EagleBank Bowl until Army plays Navy on December 12th. Army has to win that game to become bowl eligible. My money's on Navy, so I'm guessing we'll see UCLA. Whenever we know, you'll have to make that last change for yourself.

Here are the files:

• Click here for the corrected main file.
  
  Note: I just found out that I had a problem with this file. It wouldn't let you select the winner of each game in the "WINNER" column. I've since corrected this and this download now works. If you've already got all your data in the file you previously downloaded don't worry, the fix is simple. Go to Tools --> Protection --> Unprotect Sheet... Then select all of the cells where you determine the winner in the "WINNER" column and go to Format --> Cells... --> Protection tab, and uncheck the "Locked" option. Then go back to Tools --> Protection --> Protect Sheet... and click OK without setting a password.


• Click here for a simply entry form you can pass around to other to make their picks.

Let me know if you have questions. Also, please pass along any great improvements you make. Remember, last year Jonathan chipped in some nice new features.

Later,

Excel_Geek

Code Crackers Challenge: Round One

Ok everyone...here's something that I've been wanting to do for some time, but am just finally getting around to: a series on encoding and decoding data.

I've always had an interest in "breaking secret codes" etc. which has led me to creating those secret codes and encryption techniques. Some of you may already know I've done a bit of code-creating. My March Madness bracket spreadsheet is protected using my own code and methodology.

I have a hunch that there's a group of code-cracking geeks out there like me just waiting to be challenged. So here's the challenge:

• I'm going to provide an encrypted message.


• You may ask questions (use the blog's comment feature so all can see and benefit from the answers).


• I'll give answers and hints as I feel appropriate.


• The first person to submit via the comment feature the message "Excel_Geek, I have cracked your code." encoded using the same technique I've used wins. (This helps prevent "brute force" type attacks, which tend to give answers without knowing how they were derived.)

What do you win? I dunno...let's see...how about your pick between two free $50 Projects or 2 free hours of Excel consulting/instruction via Skype? Oh, and you'll obviously also be held out as the Code Cracking Champion (if that's meaningful to you).

So here is the Code Crackers Challenge Round One Encoded Message:

R0#4y,4y, y3'44yudiyI'*/46%my)-48&4x

I also feel compelled to give you a first hint, and it's been encrypted using the same technique (which is also kind of a hint in and of itself!):

P$/.s0+2%,. s12 ./s$%s&. 2,.e

So...pass this along to your code-cracking geek friends and family. Ask questions. I'll do the best i can to respond to each question, though not always as directly as you'll probably hope.

Good Luck!

Excel_Geek

Pull numeric value from string

The other day I had an interesting request for help via my little MeeboMe chat window. James wanted a formula to pull a numeric value from a string contained in another cell. The string was something like this:

"Nylon rope 300Ft"

He had a whole column of this type of data and wanted to pull out the number of feet of each item. The "Ft" would always be immediately after the numeric value desired, and a space would always immediately precede it, but here's the kicker: there could be any number of other spaces before the numeric value and there could also be other number before the numeric value desired.

It took me a bit to get where I needed to be, but here's the logic built into a pretty long, nasty, nested formula:

1. First get the string that contains everything up to and including the "Ft" using LEFT and FIND.


2. Count the number of spaces in the string identified in #1 by comparing the length of that string versus that string if all the spaces are replaced with nothing (""), using LEN and SUBSTITUTE.


3. Replace the last space found in the string identified in #1 with an odd character not likely to be in any of the data (I used a tilde ~) using SUBSTITUTE.


4. Pull out the numeric value, which is the remainder of the string after the tilde using MID, FIND, and LEN.

I put together a sample spreadsheet of this process both broken out into parts and also all nested together in a single formula.

Excel_Geek Insiders, enjoy!

Later,

Excel_Geek

NFL Weekly Pool Template

The other day I got a request for a simple template for a weekly NFL office pool. After a few more clarifying questions, here's the scenario:

The requester is the organizer for a weekly NFL football office pool. Each person picks the winner of each of the 16 games each week during the regular season -- no scores, no picking against "the line", etc. -- just a straight up pick, and for each correct pick, that person gets a point. The person who has the most correct picks at the end of the week wins. In the event of a tie, each person also predicts the total combined points scored in the Monday night game. Whomever is closest wins.

I whipped together a quick template that can either be printed and used manually or filled out in Excel as actual results come in, automatically calculated scores and tie-breakers, if necessary. I thought it was decent, so I thought I'd share it -- with everyone, not just Excel_Geek Insiders (though, Insiders, I'll be emailing your copy to you personally, and everyone else has to download it on their own.)



Later,

Excel_Geek

Mail Merge and Named Ranges

Okay, so today I was helping out a friend over at Husker Diesel who's pulling together a customers/contacts list into Excel. One of his planned uses is for printing mailing/shipping labels. "No problem," i thought. "Let's just crank up a good old-fashioned mail merge in Word.

Turns out it was pretty easy, but i ran across some knowledge along the way i thought I'd share:

For a variety of reasons that are immaterial to the story, the Excel contacts list we were working with wasn't just a simple top-row-contains-headers type of list. There's a form at the top of the page and the column headers started further down the page -- say row 11 or so. Sooo...when i browsed for the Excel file in the mail merge process what it pulled back wasn't very useful -- the form part of the worksheet was included in the contact records since it's at the top. "No problem," i thought. "Let's just create a dynamic named range that will define just the contact records part of the sheet .

Turns out that mail merge doesn't recognize dynamic named ranges. ?! Odd.

I couldn't find a lot out there on this issue, so I ended up just using the work around of created a "hard defined" named range of 10,000 rows of the contacts list, just to be sure there was ample room for all the contacts he'd be adding. Doing this caused me to have to select only non-blank rows during the mail merge process, but it worked just fine.

If anyone out there has encountered this and has a better work around, I'd love to hear it.

Later,

Excel_Geek