Excel_Geek...I'll Do That in Excel for $50

2013 March Madness Pool Spreadsheets

noreply@blogger.com (Excel_Geek) — Mon, 18 Mar 2013 01:51:00 +0000

Here we go, everyone! The much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet) is out! Here's a cute little article to get you some quick facts while you're filling out your brackets.

The system works the same as last year. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to automatically pull their picks into the master.

Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Like I first did last year, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it -- I've automated the process.

What you will do is the following:

Download the "master" file by clicking on the link above.
Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
Copy the "lock code" the file presents you.
Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
At the end of the transaction, you'll be redirected to a confirmation page with my pretty little avatar on it. It will display (among other things) the "key code" you'll need to activate the file.
Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!

Build Up to March Madness 2013 - Another Crazy Pool Format

noreply@blogger.com (Excel_Geek) — Thu, 14 Mar 2013 04:34:00 +0000

I thought I'd continue to stoke the excitement fire for March Madness 2013! Many of you know that besides college football bowl season, March Madness is the biggest event for ExcelGeek.com each year.

To reiterate from my last post, this year the ExcelGeek.com March Madness bracket situation will work like year's past. Once Selection Sunday is over, I'll post this year's bracket files -- both the "Master" file that pool organizers use and the "Picks" file that individual participants in pools use to track how they're doing and to send to the organizers to make it easier for them to input picks into the "Master".

I'll also be using the same online file registration process as last year. That really worked well. If you don't remember it, how it works is that you FIRST download the Master bracket file (the one pool organizers use to manage the pool) and open it (with macros enabled). It provides a Lock Code. Then you click the PayPal button link to submit payment of $2.00. It's at that time that you provide the Lock Code. Once your payment is confirmed, you'll be provided with a Key Code. With that key code you'll unlock all the wonder that is the ExcelGeek.com March Madness Master bracket file.

Awesome, right?

So...what are you supposed to do between now and this coming Sunday night?

Here's another interesting March Madness pool sheet I just did for a client. This one involves randomly assigning participants to each of the 68 teams in the tournament. Then, the winners of each game are determined by the final score vs. the official betting line on the game. Example: If you've got Missouri in a match-up vs. Duke, and Duke's favored by 5, you win so long as Missouri beats the spread. In the event your team actually wins the game and beats (or matches -- a "push") the spread, you advance with your team to the next round. If your team loses, but beats the spread, you advance to the next round AND (here's the fun part) you steal the loser's team (since they won the game). Fun, right? Anyway, you can do different payouts for wins at each round of the tournament, and this client wanted to actually do pay-outs for the Championship loser and Semifinal losers, too, so I built that in. All in all, it was another example of a pool where your ability to pick teams is not really a factor. It's about your luck.

I believe this client is still rounding up participants for the pool. If you're interested, let me know and I'll pass along his email address.

Excel_Geek Insiders Subscribers, I'll send along a copy of that file.

Later,

Excel_Geek

Getting Excited for March Madness 2013? An Appetizer

noreply@blogger.com (Excel_Geek) — Tue, 12 Mar 2013 01:37:00 +0000

Hey folks! Are you getting excited for March Madness 2013? You know I am. Many of you know that besides college football bowl season, March Madness is the biggest event for ExcelGeek.com each year.

This year will be a like year's past. Once Selection Sunday is over, I'll post this year's bracket files. I'll also be using the same online file registration process as last year. That really worked well. If you don't remember it, how it works is that once you've downloaded the Master bracket file (the one pool organizers use to manage the pool) and open it (with macros enabled) it provides a Lock Code. When you submit payment of $2.00 via PayPal, you'll have a place to type or copy-paste in that code. You'll then be provided with a Key Code. With that key code you'll unlock all the wonder that is the ExcelGeek.com March Madness Master bracket file.

Awesome, right?

So...what are you supposed to do between now and this coming Sunday night? Well, I just recently created a different kind of March Madness pool sheet for a client. It's a whole different animal than the typical bracket challenge type pool that I've done in the past. It uses a 10x10 grid format where the top axis of the matrix corresponds to the last digit of the winning team's score at the end of regulation and the left axis corresponds to the losing team's score at the end of regulation. Participants are assigned, randomly, the number of squares in the grid that they've purchased/earned/were given. Then the numbers along the top and left sides are randomly sorted. Having the correct square for each game awards that participant an increasing number of points or money from round to round. It's a pretty cool arrangement. It keeps everyone interested all the way through, even if your pickem' teams have long been beat out.

I believe this client is still rounding up participants for the pool. If you're interested, let me know and I'll pass along his email address.

Excel_Geek Insiders Subscribers, I'll send along a copy of that file.

Later,

Excel_Geek

Excellent use of Excel for Heat Mapping of Data

noreply@blogger.com (Excel_Geek) — Mon, 17 Dec 2012 14:34:00 +0000

Best quote from the article: "John Nelson's charts and maps on traffic fatalities are a reminder that a good data [visualization] doesn't always require graphic design chops -- or software."

And how!

Check out the rest of the article to see some really lovely heat maps. If you want that powerful pattern recognition factor built right into the human brain to work, feed it visuals like this.

Thought you'd all enjoy,

Excel_Geek

Updated: I forgot to link to some of my own Heat Chart related stuff.

Inventor of the Barcode Dies

noreply@blogger.com (Excel_Geek) — Sat, 15 Dec 2012 19:26:00 +0000

N. Joseph Woodland, inventor of the barcode, has died. So there's that. Reminded me that I actually did this in Excel.

2012-13 College Football Bowl Pool Spreadsheets

noreply@blogger.com (Excel_Geek) — Mon, 03 Dec 2012 13:28:00 +0000

Sorry for the wait, folks. Here are this year's college football bowl season pool files. Enjoy!

http://www.excelgeek.com/downloads/collegebowls/2012-13_Bowl_Pool_Summary_revb.xls

http://www.excelgeek.com/downloads/collegebowls/2012-13_Bowl_Pool_Entry_Form.xls

For the uninitiated, the "Summary" file is the one the manager of the pool will use to keep track of everyone's picks and points. The "Entry Form" is the file the manager of the pool sends out to the participants, so they can make picks and assign points. Then the manager can copy-paste them into the "Summary" file.

Also, I routinely get requests for a version of this file with room for more participants. While I haven't updated the "expanded" version to this year's teams and bowls yet, you can yourself. Here's where I posted that version:

click here

UPDATED: I've since fixed the issue some folks (who've commented on this post) were having relative to picks not being displayed in the Individual Charts tab for users #21 and above. I've also added in the feature requested by one commenter for calculating negative confidence points for incorrect picks. Good idea, I thought. I added a user option to select either "Zero" or "Negative" for points for incorrect picks. Check it out.

Single Question Post-Election 2012 Survey

noreply@blogger.com (Excel_Geek) — Wed, 07 Nov 2012 13:52:00 +0000

I don't get all political very often, but this morning I've got a burning curiosity. (And I apologize in advance to my international readers for my US-centric focus here.)

You have experienced the past four years. You've awaken this morning to see the same President in office and the balance of power in Congress largely the same. How optimistic or pessimistic are you about the next four years?

Take my single question post-election 2012 survey here: http://excelgeek.com/election2012_survey.htm

Highlight First Blank Cell With Conditional Formatting

noreply@blogger.com (Gadi) — Mon, 23 Apr 2012 20:52:00 +0000

Hey, just wanted to share a quick trick about conditional formatting.

If you have a data entry sheet with input in adjacent cells within the same row or column, and you want the next blank input cell to be highlighted with a color, you can apply conditional formatting based on a formula.

The following formulas assume that your input is starting from cell A1 and all the input is in adjacent cells:

Vertical data entry: =ROW(A1)=COUNTA(A:A)+1
Horizontal data entry: =COLUMN(A1)=COUNTA(1:1)+1

If there are empty cells before the actual input range, you need to modify the formula. Increase the "+1" with every empty cell above (or to the left of) the actual input range. This type of conditional formatting can be applied on the entire column, entire row, or custom range, and there would only be one highlighted cell at a time.

Excel Geek insiders will get the sample file from Eric.

2012 March Madness Bracket Files Are Here!

noreply@blogger.com (Excel_Geek) — Mon, 12 Mar 2012 04:56:00 +0000

Here we go, everyone!

Despite being in the middle of a bit of traveling, I've managed to get you the much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet). Thank goodness Hampton Inn is a comfortable place to work, right?

The system works the same as last year. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to pull their picks into the master.

Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Unlike years past, though, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it. Instead, I've automated the process.

What you will do is the following:

Download the "master" file by clicking on the link above.
Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
Copy the "lock code" the file presents you.
Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
At the end of the transaction, you'll be redirected to a confirmation page with my pretty little face on it. It will display (among other things) the "key code" you'll need to activate the file.
Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!

Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.

(Actual submission form has been removed, so people don't accidentally send me $2 for LAST YEAR's bracket files.)

Best of luck, everyone!

Excel_Geek

2012 March Madness Bracket is coming...

noreply@blogger.com (Excel_Geek) — Sat, 10 Mar 2012 03:14:00 +0000

We're under two days until Selection Sunday! You know what that means, right? That's right. It's almost time for another version of the ExcelGeek.com March Madness Bracket pool manager spreadsheet (and associated participant version).

I've got something new to show you all this year. OOOOooooooo..., right? Well, actually the pool master spreadsheet and associated participant picks sheet both will look and feel and work pretty much like they did last year. The big difference this is that you won't have to email me the "Lock Code" you get when you first open the (locked down and protected) "master" file. Instead, I'll give you a place to enter that code so that it is submitted when you pay the $2.00 via PayPal. Then, when the payment is processed, you'll be taken to a page that automatically generates the associated "Key Code" you'll need to put in the spreadsheet to make it work.

How about that? No more waiting on me. Instant gratification! (And, I don't have to struggle to keep up with emailing people codes.) You didn't know I was also a budding web developer did you? In all seriousness, I have a good friend, Craig, to thank for being patient enough to "teach" me a bit of .php. Thanks, Craiggers!

As soon as all of the picks are announced on Sunday, I'll get the files posted.

Talk to you all again in 48 hours or so.

Excel_Geek

Data Validation Quirk When Using Named Ranges

noreply@blogger.com (Excel_Geek) — Thu, 23 Feb 2012 15:03:00 +0000

So here's a goofy item I stumbled upon today. (Others of you, I realize, have known about this for some time.)

Here's the situation:

You are using Data Validation to restrict input for a particular cell to items from a list (Data --} Data Validation --} List).
You have the "Ignore Blanks" option checked.
The list you are referencing is a Named Range.
The list you are referencing contains one or more blank cells.

What happens, you ask? Well, it lets users input any value into the cell that's supposed to be data validated without warning. Nice, right?

Three simple options to fix:

Uncheck the "Ignore Blanks" option.
Don't have any blanks in your Named Range.
Don't use a Named Range.

Just thought I'd pass it along...

Later,

Excel_Geek

Bowl Season Wrap-Up

noreply@blogger.com (Excel_Geek) — Tue, 10 Jan 2012 13:58:00 +0000

Well, another college bowl season is in the books. For those of you keeping score, Mrs. Excel_Geek beat Excel_Geek for the third straight year (or so). And in a nail-biter, new-comer Excel_Geek Jr. matched his daddy for the number of correct picks, but was narrowly edged out by 8 confidence points. So you know, I picked for the little guy, using the =RANDBETWEEN() function and the =RAND() function -- maybe I'll do a post describing how to do that -- to make his picks completely randomly. That's right. My own, well-thought-out, analyzed, expert picks did only very slightly better than completely random picks and point assignments. Oh well...back to the drawing board. See you all next bowl season!

Later,

Excel_Geek

Oh, almost forgot some traffic stats: the 2011-12 Bowl Season (12/3/11 - 1/9/12) increased ExcelGeek.com website traffic by nearly 3.9x the average daily site traffic for the year leading up to it, whereas the 2010-11 Bowl Season (12/4/10 - 1/10/11) increased traffic by (only) nearly 3.4x. 2011-12's peak 20-day moving average traffic day (12/23/11) was nearly 44% higher than that from 2010-11 (12/24/10). The single day peak from 2011-21 (12/5/11) was 72.8% higher than the single day peak in 2010-11 (12/6/10). Overall...a great year.

Next up...March Madness!

Problem with Individual Charts in College Pool Corrected

noreply@blogger.com (Excel_Geek) — Sat, 17 Dec 2011 19:03:00 +0000

Oops!
Zach let me know that he found a problem with the Individual Charts tab in the 2011-2012_Bowl_Pool_Summary file. Participants 21-40 were't working properly - showing "no picks made" even if valid picks were input into the Picks tab.

I've fixed this. If you download it now, it'll work properly.

Sorry about that!

Eric

Expanded (Again) Bowl Pool Spreadsheet

noreply@blogger.com (Excel_Geek) — Wed, 14 Dec 2011 04:55:00 +0000

It's done. At least I think it all still works. Now you have room for up to 100 participants. I also added in 5 extra bowl game rows for future expansion if necessary. For those of you with large pools, this outa work just fine. Again, you can thank JMorein and EGeierman for footing the bill.

Also, again, this new file is only compatible with Excel 2007 or 2010, as it's far wider (in columns) than is supported in Excel 2003 and older.

click here to download the file.

Enjoy!

Excel_Geek

Another expansion of the College Bowl Pool Spreadsheet is afoot!

noreply@blogger.com (Excel_Geek) — Fri, 09 Dec 2011 15:30:00 +0000

I caved (again). I'll be expanding the current College Bowl Pool Spreadsheet to accommodate up to 100 participants. There could be some other minor changes as well. Thank JMorein and EGeierman for spearheading the effort (and footing the $50 bill I'm enforcing to do this). If any others would like to contribute to the $50, simply email me (you should be able to find my email address on this website somewhere) or send me your email using the little chat thingy indicating that you'd like to contribute, and I'll send you out the new files once they're done.

UPDATE: This expanded version will definitely only be for those on Excel 2007 & 2010, given the width in columns it'll take.

2011-12 College Football Bowl Pool Spreadsheet

noreply@blogger.com (Excel_Geek) — Sat, 03 Dec 2011 15:41:00 +0000

It seems like the first time in a few years (not sure if this is true, but it feels like it) that I don't have to modify the spreadsheets to add another game. "Just" 35 bowls again this year.

The Humanitarian Bowl has been replaced by the Famous Idaho Potato Bowl. The Texas Bowl is now called the Meineke Car Care of Texas Bowl. The old Meineke Car Care Bowl has been replaced by the Belk Bowl. Yeah...Belk. I had to look it up too. "Charlotte, N.C.-based Belk, Inc. (www.belk.com) is the nation’s largest privately owned mainline department store company with 305 Belk stores located in 16 Southern states. The company was founded in 1888 by William Henry Belk in Monroe, N.C., and is in the third generation of Belk family leadership. The belk.com Web site offers a wide assortment of fashion apparel, shoes and accessories for the entire family along with top name cosmetics, a wedding registry and a large selection of quality merchandise for the home." Okay. Anyway...the reason I even bring any of this up is that while I know that the sponsors of these bowl games spend a lot of money to get their brands out there, where convenient, I'm just going to leave the sponsor name off in the spreadsheet -- e.g. i'll just put "Music City Bowl" instead of "Franklin American Mortgage Music City Bowl". In cases where the sponsor name IS the bowl name (e.g. "Champs Sports Bowl"), I'll obviously just use that. The new "Famous Idaho Potato Bowl" i'll just call the "Potato Bowl" in the whole "Orange Bowl", "Cotton Bowl", etc., tradition.

Now that that's out of the way, here are the files:

Click here for the "Master" file used by the pool organizer. This is the one with the fancy charts and group scoring summary, etc.

Click here for the "Individual Picks" file used by each person participating in the pool. This is the one the pool organizer typically would email out to his or her participants to have them fill out to make it easier for him or her to copy paste their picks into the "Master" file.

Enjoy, everyone!

Excel_Geek

noreply@blogger.com (Excel_Geek) — Fri, 02 Dec 2011 13:54:00 +0000

I've already gotten a slew of inquiries as to when this year's college football bowl pool spreadsheet will be ready. Patience, Grasshopper(s). Once all of the bowls have selected their teams, it will be ready.

Not familiar with the spreadsheets? Click the "College Bowl Game Pool" tag to see what I've done in the past.

Later,

Excel_Geek

Custom Filled Pie Chart

noreply@blogger.com (Gadi) — Tue, 14 Jun 2011 16:08:00 +0000

I recently completed a project where the client asked me to design a pie chart which can be filled with colors according to selections made by the user. Each pie slice represents a task which the user had to complete. Once the user marks a task as "Completed" the pie slice should be filled with color, otherwise the slice should be white. Each task has a different importance and the client wanted the pie slices to be sized accordingly.

In my example there are 10 tasks. The data is arranged in columns as follows:

Column A - The list of tasks.
Column B - The status set by the user for each task.
Column F - Relative importance of each task (in percentage). Determines the size of the slices.

I added a pie chart with column A as the axis labels and column F as the values series. I formatted the data series in the chart to have no fill. Then I added the following code in the sheet module where the chart resides:

Private Sub Worksheet_Change(ByVal Target As Range)

PieColor = Array(10973765, 4409002, 5154185, 9394289, 11507777, 4031707, 13609363, 9606097, _
    9883065, 12426153)

If Not Intersect(Target, Range("B1:B10")) Is Nothing Then

    For i = 1 To 10

        If Cells(i, 2).Value = "Completed" Then
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = PieColor(i - 1)
        Else
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = -2
        End If

    Next i

End If

End Sub

This is how the chart looks like when tasks 1, 3, 6 and 9 are marked as completed:

(Click on the image above to view its original size)

Because the code resides in the sheet module under the Worksheet_change event, every task which the user changes to "completed" is being updated dynamically in the chart. If the user clears the "completed" status, the slice of that certain task would return to be white.

Insiders will shortly receive from Eric the file which contains this chart.

Usage of 3D Chart To Ease Data Analysis

noreply@blogger.com (Gadi) — Thu, 19 May 2011 16:52:00 +0000

Ever wondered how a 3D chart can come in handy? I want to give an example where a 3D chart can turn a tedious task into a simple procedure. It can be very useful for a quick analysis of a data set with a glimpse of an eye.

I used to work in a call center where the shift managers had to schedule shifts for their team agents every couple of weeks. The main guideline was to conduct a periodical rotation of shifts between the agents. The shift mangers used to look on the summarized count of shifts per agent and get a big headache. For roughly 20 agents, most humans I know would get a headache... Well, I came up with a remedy in the form of aligning all that data in a 3D chart. There were only 3 types of shifts and I set the axis the following way:

X - Agent names
Y - Count of shifts
Z - Shift types

If you click to on the above image to enlarge it, you will see two inputs at the top for the selection of the Start date and the End date. These are actually drop-down menus which determine the range of dates that would be shown in the chart. The data which is feeding the chart is based on SUMPRODUCT functions which sum up the shifts per each agent for any given date range. This is also known as SUMPRODUCT with multiple conditions.

By inspecting the chart, the shift managers could immediately conclude for instance, which agents had a majority of night shifts and prioritize these agents to do other shifts in the upcoming schedule.

Eric will dispatch the file which features the 3D bar chart and the functionality behind it to the Insiders Subscribers.

Verifying Sheet Changes

noreply@blogger.com (Gadi) — Sun, 20 Mar 2011 13:38:00 +0000

Hello Everyone,

I'm Gadi, Eric presented me in the blog last month. Starting from today I'll periodically write posts in ExcelGeek.com, it's something I promised to help Eric with, as part of our collaboration on projects. It is my pleasure to share my knowledge with others. I'll try to post neat Excel tricks, which some of you may find useful (I hope).

My first post I would like to dedicate to a solution which I lately applied at my work place, related to verifying with the user, whether the changes he made to certain critical fields in the worksheet were made deliberately or by accident. An alternative solution could have been to protect the workbook, however I did want to allow users the ability to change these values. The solution is based on a message box, that pops up whenever a sensitive cell is being changed. It prompts the user to confirm the change of the cell.

The following code has to be placed in a sheet module. Copy paste it into the modules of the sheets where you want to enable user's confirmation on any changes made to sensitive data cells. Change the VerifyRng to the range where you want the confirmation message to popup. In the following code it is set to any cell in column A.

Public Trigger As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Resets the trigger if a new cell is selected.

    Trigger = False

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Verifies that the user deliberately changed data in column A.

    Dim LastRow As Integer
    Dim VerifyRng As Range
    Dim Response

    If Trigger = False Then

        Set VerifyRng = Columns(1)
        'Change this variable to your desired range

        If Not Intersect(Target, VerifyRng) Is Nothing Then
            Response = MsgBox("You are about to change the cell " & Target.Address(False, False) & _
                "." & Chr(13) & Chr(13) & "Are you sure you want to modify this cell?", _
                vbYesNo, "Cell Change Alert")

            If Response = vbNo Then
                Trigger = True
                Application.Undo
            End If

        End If

    End If

End Sub

2011 March Madness Bracket is Here!

noreply@blogger.com (Excel_Geek) — Mon, 14 Mar 2011 01:56:00 +0000

Ok, people. Here they are -- this year's March Madness Bracket spreadsheets. Again, there's a Master sheet for the organizers of pools, as well as a Participant version, just for people to make their picks and send them to the organizer.

Master File

Participant File

Once you've downloaded the Master file and open it (make sure you've got Macros enabled), it'll prompt you to send me a "Lock Code". When you do, I'll send you back a "Key Code" to unlock the file for use. Before I send you this code, however, you'll need to pay the $2.00 price tag.

Incidentally, it took me a bit longer that I'd planned to get these out because I was caught a bit off-guard by the NCAA's use of the four play-in games. I'd ASSUMED (yes, I know what ASSUMING can do.) that each of the four play-in winners would be the 16 seed for each of the respective quarter brackets -- EAST, WEST, SOUTHWEST, and SOUTHEAST. This was not the case, though. Two of the play-in game winners will effectively be 16 seeds (EAST & SOUTHEAST), one an 11 seed (SOUTHWEST), and one a 12 seed (EAST). If you didn't catch that, yes the EAST quarter bracket has two play-ins, meaning that the WEST has none. Very odd, I thought.

As always, let me know if you've got questions.

Insiders, if you need a Key Code, let me know. It's part of your subscription.

Happy March Madness, everyone!

Later,

Excel_Geek

Wait until this evening to download this year's March Madness spreadsheet

noreply@blogger.com (Excel_Geek) — Sun, 13 Mar 2011 14:50:00 +0000

In an effort to try to stem the many emails, instant messages, etc., that I'll get today as people are looking for this year's version of the ExcelGeek.com March Madness Spreadsheet, let me just say this:

After all of the tournament selections have been made, and the bracket has been laid out by the Selection Committee, I'll post the two spreadsheets -- one for individual participants and the other for pool organizers. That will be late this evening.

For those of you who've decided to pay the $2.00 in advance, you'll still have to wait for the files to be posted. I plan to email you, if you've paid in advance, when the files are available. Then, when you open the "master" spreadsheet (supposing you've got your macros properly enabled), you be given a "lock code". Send this to me via email (address you may already know, but if not, it's provided in the file), and I'll send you back the associated "key code" to give you access to the content of the file.

Now...back to updating those files...

Heat Charts are so easy in Excel 2007

noreply@blogger.com (Excel_Geek) — Sun, 27 Feb 2011 23:49:00 +0000

Before you roll your eyes and say, "Welcome to life after 2003, Excel_Geek," let me just say, "I know, I know." The driver for this project was that I was analyzing my website traffic using my Google Analytics account. I noticed a few things.

One was that "heat chart" or "heat charts" seems to be a pretty popular search term for folks to find me. I've obviously done a couple of heat chart projects, but back when I was still using 2003, so in order to create them with more than four colors (the limit to Conditional Formatting in 2003), I had to do write some VBA code to accomplish it.

Another thing I noticed what the rhythmic up and down of my site traffic. There were a few major spikes -- March Madness season, college football bowl game season, and one recently when @MSExcel mentioned me in their Twitter feed. What I wanted to further analyze, though, was if there was any pattern to traffic patterns based on Day of Week and recency of blog posts.

I combined these two findings and decided to do a heat chart incorporating both Day of Week and recency to blog posts. Here's what the chart itself looks like:

Along the way I used some other favorite techniques along the way: indexing the data (so I'm not sharing my actual web stats...hey, that's my business), a trick to convert a list of dates into a list of days of the week, VLOOKUP using TRUE instead of FALSE (whatever for? right?), and using SUMPRODUCT to create COUNTIFS and SUMIFS functions that will be backward compatible to Excel 2003 and earlier. I highlighted the creation of the heat chart and these other techniques in the source file, which I'll be sharing with my Insiders Subscribers.

Later,

Excel_Geek

2011 March Madness Bracket is coming soon

noreply@blogger.com (Excel_Geek) — Mon, 21 Feb 2011 14:19:00 +0000

Mark your calendars, folks. March 13th is Selection Sunday. In what has now become a five year tradition, I'll be releasing the ExcelGeek.com March Madness Bracket spreadsheet late that evening, once the picks are known.

In years past I've charged $3 for the "Master" version of the sheet -- the one for the organizer of your office pools, etc. I used the opportunity to showcase (read: show off) a system I've developed for locking down and protecting, then individually licensing access to my spreadsheets. I plan to keep it locked down (damn, right?), but I'm dropping the price for the Master Spreadsheet to $2. Once you pay via PayPal and send me the "Lock Code" given to you by the spreadsheet once you open it (and have your Macros enabled properly), I'll send you the "Key Code" to gain access.

Can't wait...

Excel_Geek

Updated: Parse Google Search Results for First URL

noreply@blogger.com (Excel_Geek) — Mon, 21 Feb 2011 00:10:00 +0000

Those of you who've been following me for some time may know that I've done quite a few projects related to parsing Google search results for various data -- URLs, number of results, etc. Those of you using the code likely know that some time ago Google apparently made some changes to how they put together their site causing my stuff to stop working. Well, recently one of the requesters of one of these projects - one that returns the first non-paid URL returned - came back and had me update it to work with how Google's site currently displays search results.

This time I made it into a user-defined function. You simply enter the function...

=getFirstGoogleResultURL(A2), where the value of cell A2 is the search term.

It works pretty slick, but know that Google will not allow this sort of thing to be used on large volumes of searches. I'm pretty sure they'd much rather have you using their APIs for this sort of usage.

Excel_Geek Insiders subscribers, your copy of this file is on its way.

Later,

Excel_Geek