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

Google Analytics Data in Excel

Have you seen this? Very cool. (Thanks for finding this, Bart.) I haven't dug into it yet, but plan to soon...

Stay tuned.

Chat Support RE: Custom Number Formats

Here's a short one from today:

[15:39] meeboguest######: hi, Im trying to import 11 digit UPC numbers into my software, but can't find the correct format to keep the digits at 11. All my UPC numbers that start with a 0 are cut off and made 10 digits every time I try to import
[15:39] meeboguest######: I tryed formatting the columns into text and it still shows 10 digits if it start with 0.
[16:15] excel_geek: go to Format --> Cells...
[16:15] excel_geek: from the Number tab select Custom
[16:16] excel_geek: then replace where it says "General" with 11 zeros.
[16:16] excel_geek: tada!



Gotta love customer number formats.

$50 Project - Parse Google Search Results within a Domain for First Result

I recently completed a $50 Project for a client who wanted a spreadsheet where he could have a list of domains in which to search and a list of search terms and return the URL for the first returned search result from Google while searching withing the specific domain. It took a little modification of some other stuff I've done with parsing Google search pages, but it seems to work nicely.

Excel_Geek Insiders, your file is on it's way.

Later,

Excel_Geek

Chat Support RE: Counting in Pivot Tables

Here's another one from yesterday:

[15:40] meeboguest######: hi excel_geek - looking for a cont function in a pivot table... can you help?
[15:40] excel_geek: cont?
[15:40] excel_geek: count?
[15:40] meeboguest######: oups.. "count"
[15:42] meeboguest######: have multiple entries by various "agents" and wnat to find how many agents are ther in total... regardless of the entries
[15:42] excel_geek: sure
[15:42] excel_geek: right click in the table detail part
[15:42] excel_geek: and pick field options
[15:42] excel_geek: then select "count"
[15:44] excel_geek: follow?
[15:44] meeboguest######: ok
[15:46] meeboguest######: it gives me the total count of entries, not count of unique individuals... (1 individual = multiple entries)...
[15:47] excel_geek: hmmm
[15:47] excel_geek: maybe i should back up
[15:48] excel_geek: so drag the agents field into the left part of the pivot table
[15:48] excel_geek: then drag any other column into the right (detail) part
[15:48] excel_geek: the right click the field you put in the right part and field settings to "count"
[15:50] meeboguest######: ok... i think i figured it out!
[15:50] meeboguest######: thx a bunch :)
[15:50] excel_geek: np
[15:50] excel_geek: thanks for stopping by
[15:50] meeboguest######: :D

10 Minutes to understanding. Not too bad. Some of that pivot table stuff is unintuitive.

Chat Support RE: =INDIRECT() Function

More and more I'm helping folks via the MeeboMe chat window I've embedded in the blog. I thought it'd be interesting for you all to see the sorts of conversations I'm having this way:

[14:36] meeboguest######: eric!
[14:36] meeboguest######: do you have a sec?
[14:36] XLgeke: for u?
[14:36] XLgeke: always
[14:36] XLgeke: and now it's over... ;)
[14:36] XLgeke: what's up
[14:36] meeboguest######: excel question
[14:36] meeboguest######: umm
[14:36] meeboguest######: trying to think how to explain
[14:37] XLgeke: reboot
[14:37] XLgeke: that should do it
[14:37] meeboguest######:
[14:37] meeboguest######: ok, I'm wondering if there's a way to dynamically reference a cell in a formula
[14:37] XLgeke: yes
[14:38] meeboguest######: so that if I was to reference cell B
[14:38] XLgeke: right
[14:38] XLgeke: use =INDIRECT()
[14:38] meeboguest######: and that number is a value generated in another cell
[14:38] XLgeke: so like
[14:38] meeboguest######: oooh
[14:38] XLgeke: =INDIRECT("B"&B1")
[14:38] meeboguest######: woah
[14:39] meeboguest######: nice
[14:39] XLgeke: in B1 you'd change the number for BX
[14:39] meeboguest######: right
[14:39] meeboguest######: I'll give that a shot
[14:39] meeboguest######: thanks!
[14:39] XLgeke: np

And that was it...less than 3 minutes and issue solved. This happens scores of times each month.

Finally - Pool Administrator version of March Madness Spreadsheet is up

UPDATE - 03/27/09 8:52 AM CDT: Another bug found! Ugh! The "master" file was not properly updating the results of last night's games. It's fixed now in this update. (Relatively) good news: it only affected the "master" sheet, so you only have to re-input your picks in the master and retype in the filenames, etc. of the individual sheets in the summary page.

UPDATE - 03/22/09: I found a problem with the files. It's not summarizing the regional semifinals round properly. I've worked out the fix and here are the corrected versions of both the freebie individual file and the "master" file.

It's done. It's late. I've tried to debug as much as I could think to. There's a lot new going on behind the scenes, though, so if you run into trouble, just drop me a note. I'll try to help you out.

Download the file. When you open the "master" file, you'll be given a lock code. Email that to me after you PayPal me $3.00, and I'll get you set up.

Good nite,

Excel_Geek

2009 March Madness Bracket -- Freebie for tracking your own picks

Okay...so I thought I'd get out this before too late tonight.

Here is the free spreadsheet that anyone can use to track his or her own picks versus actual results in the 2009 NCAA basketball tournament.

What I'm still working on is the companion spreadsheet intended for those of us who coordinate the thousands of March Madness office pools. When it's done I'll simply add it into the zipped directory with this file. It'll cost $3.00 to use. If you like you can PayPal me $3.00 now and I'll put you on the list to get the 2009 version as soon as it's ready.

2009 March Madness NCAA Backetball Bracket

The inquiries are piling in now...

Yes. I am doing a new bracket spreadsheet for 2009. I'll probably be working on it again tonight for awhile. You can still find the older versions I've done here:

2007
2008

If you do download an older version, though, please don't ask me to make a bunch of feature changes to it. They're old. I'm working on a new one. If you like you can PayPal me $3.00 now and I'll put you on the list to get the 2009 version as soon as it's ready.

What'll be new in the 2009 version? Well, I'm going to put more focus on those "pool administrators" out there. This version will actually have two companion spreadsheets. The first is for the "pool administrator" where he or she can both set up the points system for each round, track his or her own bracket, as well as those of all the people in there pool. The second will be the bracket file for each of the participants. The "pool administrator" file will be the only one locked down and password protected as it was in prior versions. The other spreadsheet will be available for anyone to download and use.

Oh and if you're an Insiders subscriber, as always, you can get the files for free. Just drop me a note.

More to come...stay tuned...

Excel_Geek

Heat Charts for Crime Stats Analysis

I recently got a request for a $50 Project from Kurt Smith with the San Diego County Sheriff's Department Crime Analysis Unit. (Cool, huh?)

Kurt wanted to apply the heat charting techniques I've posted about before to crime statistics on a Time-of-Day vs. Day-of-Week form. He shot me over some sample data on vehicle thefts. A simple Copy --> Paste Special... --> Values operation later and here's what we've got...



I'm obviously biased, but I think heat charts is a great way to visualize this sort of data, which can tend to get lost in tabular form. In this example, you can easily see the clustering occur just after midnight on the weekends.

The SDSD's apparently got a few Excel geek's on staff, as Kurt tells me that their resident Excel afficianado, Ted, has "...played with it and built some other ranges that approximate standard deviation, 'thirds' and so on from percentiles..." and that they're "...going to begin using it to replace our Excel surface charts for when particular crime types are being reported (we use split times and some aoristic analysis, depending on the crime)..." Whoa...slow down...you're losing me, Kurt.

Kurt also tells me that Lincoln's very own Police Chief Tom Casady is a stats/spreadsheet junky, too. Who knew this? Apparently there's a whole hidden world of Excel geeks: Cops! I'll have to subscribe to Chief Casady's blog.

Excel_Geek Insiders subscribers, I've sent this file out before, but just in case you've lost it, this new version is on it's way.

Later,

Excel_Geek