Bowl Season Wrap-Up

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

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

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!

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

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

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

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.