## Tuesday, December 27, 2011

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?

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.

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!

## Monday, December 5, 2011

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.

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