Tuesday, December 27, 2011

2011-2012 NBA Schedule Spreadsheet Download

I know this is a little bit late, I had wanted to get it out before the season started but things have been pretty crazy. Anyways, it's finally here, the full 2011 - 2012 NBA schedule. The spreadsheet includes the a complete list of all 66 games for each team. When the NBA playoffs roll around we'll have a new spreadsheet schedule for that too.

This spreadsheet is very useful for 2011 NBA fantasy basketball players. I also may update the Excel file with a list of the TV schedule and what stations each game will be broadcast on. Anyone know where I can find that information?

Click here to download the NBA 2011-2012 Spreadsheet Schedule.xls

You can find all or our other sports schedules and other spreadsheet templates to download here.

Update: get the 2012-2013 NBA schedule here!

Monday, December 26, 2011

Mechanical Engineer: Evolution of Computer Skills

A friend sent me this graphic and I thought it was pretty funny, mainly because it's so true! Ironically, I never learned how to use Excel in school, or didn't even how to write a simple code. Excel is a great and powerful tool. I think you could group PowerPoint into that category too. Learn more about Matlab, CATIA, AutoCAD, Pro-E, and more over at my CAD Systems Help blog. I also find this engineering flow chart to be hilarious.

Monday, December 19, 2011

Basic Excel VBA Macro: Loop through all worksheets

What is an Excel macro? If you perform a task repeatedly, you can take advantage of a macro to automate the task. A macro (or makro to some people) is a series of functions, written in a scripting language, that you group in a single command to perform the requested task automatically. You can record a macro but it is highly recommended that you learn VBA in order to write and use macros of your own creation. I will help you learn VBA through a series of Excel macro tutorials, starting with how to scroll through all the tabs in a workbook.

In this Excel macro example, I want to loop through all worksheets in order to copy a specified range of cells and then delete the contents of a couple of other cells. To loop through all worksheets I will utilize a function call a For Loop. Here's a programming syntax tip for you: Add comments to your statements using an apostrophe ('), either at the beginning of a separate line, or at the end of a statement. It is recommended that you add comments wherever possible, to make your scripts easier to understand and maintain, especially if another user has to make changes to it later on down the road. I use comments in my example code below to help explain each step of the program. I have indented all the code which takes place inside the for loop.

Sub WorksheetLoop()

   Dim WS_Count As Integer
   Dim I As Integer
   Application.ScreenUpdating = False

   ' Set WS_Count equal to the number of worksheets in the active workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count

           ' Begin the loop through all worksheets.
           For I = 1 To WS_Count

              'message box displaying the name of the worksheet
              MsgBox ActiveWorkbook.Worksheets(I).Name
     
            'active sheet i    
            ActiveWorkbook.Worksheets(I).Activate

        'select the old data and insert new rows
            Rows("5:39").Select
            Selection.Copy
            Rows("5:5").Select
            Selection.Insert Shift:=xlDown
            Range("C39").Select
       
        'delete old data
        Range("H17:AA22").Select
            Range("W17").Activate
            Application.CutCopyMode = False
            Selection.ClearContents
            Range("AB11:AB39").Select
            Range("AB39").Activate
            Selection.ClearContents


   Next I
   
 Application.ScreenUpdating = True

End Sub

Now you can answer the question what is a macro! Please comment below if you have any questions.

Learn how to export to Excel from CATIA.

Tuesday, December 13, 2011

Updated 2011 NCAA College Football bowl sheet

With the help of David Tyler (creator of one of the best NCAA college basketball Excel brackets) I have made some modifications to my 2011 college football bowl sheet.
·         By using the sumproduct formula and conditional formatting we have replaced the VBA COLORFUNCTION, making the spreadsheet much more user friendly. No need to press crtl+alt+f9 to update the formulas!
·         I moved the scoreboard to its own individual sheet and included a few new stats, such as the bowl game picked correctly by the most number of players.
·         I expanded the default number of players to twenty but there’s still the issue where new players beyond the first twenty added to the pool are not automatically accounted for in the leader boards.
Please keep in mind; this is just the beginning of the evolution of this spreadsheet. The end goal is to automate inserting the exact number of players along with all of their picks. There will also be some sort of tie-breaking function, like guessing the score for the BCS championship game or using a confidence interval to rank each pick.
And hopefully by the time this new spreadsheet is completed we can throw the whole thing out because we’ll finally have a college football playoff!
Download the previous version here.
*******UPDATE Dec 27 - latest version has been uploaded

Monday, December 5, 2011

2011-12 NCAA College Football Bowl Pool Spreadsheet Dowload

Now that the2011-2012 NCAA college football bowl game match-ups have been announced, I was able to quickly put together a schedule in Excel spreadsheet form. I have made it available to download. Pictured below is the schedule of each and every bowl game as well as the time of the game and what TV channel it will be shown own.

I have also created a simple college football bowl predictions pool. When I get the emails of folks picks I put them in the spreadsheet and then when games are played I color the winners the green as in box A2 and the wrong ones in red. Then out in the far column it counts all the picks that are colored green using the COLORFUNCTION formula

=ColorFunction($A$40,C3:C38,FALSE)

You may have to double click on the sum cells in order to get them to compute. Please let me know if you have any questions.

A few interesting bowl game notes.
  • 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.
Update: The download link should now be working! Please comment below if there are any issues. Thanks!

Download the 2011 College Football Bowl Prediction Pool Spreadsheet.xls


Check out our other sports spreadsheets, including a college football helmet schedule.

Latest download is here 12-27-11 

Update: Download the 2012 - 2013 version here.

Sunday, December 4, 2011

2011 NCAA College Football Bowl Schedule Announced - Spreadsheet Download Coming Soon!

The 2011-2012 NCAA college football bowl game match-ups have been announced. I am currently working on a bowl prediction spreadsheet and schedule which you will be able to use for your betting pools at work or with friends and families. Hopefully, the football spreadsheet will be available for download within a day or two. Updates coming soon!

Download the 2011 College Football Bowl Game Prediction Pool Spreadsheet.xls

Follow me on Twitter for the latest information.

Update 12/6/11:
The spreadsheet download link for the 2011 NCAA college football bowl game predictions pool has been posted here. 

Update 12/27/2011
Latest spreadsheet