2024 College Football Bowl Pick'em and Playoff Bracket

It's the most wonderful time of the year - college football bowl season! The college football conference championships were played this past weekend which means the 2024 NCAA college football bowl season is here again. It’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season is being able to talk trash to your relatives about their terrible bowl picks.

This year has the added bonus of the new, expanded college football playoff - growing from four to twelve teams. As such, I've included a college football playoff bracket within this Excel file. There is the option to play just the bowls, just the playoff bracket, or both! If you've used my Super Bowl Squares template, you'll be familiar with how it works - there is a setup screen where you pick your options then click a button and the spreadsheet adjusts to how you want to play.

The bowl prediction sheets include the football helmet designs for every team (taken from my college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!

There are multiple ways to play college football bowl pick'em. What I call "regular" is every game is worth 1 point. The other way I call Confidence Picks: each player "bet" points 1 to 35 on each game based on how confident they are their choice is correct. Player with the most points wins                 Example: player bets 35 points and gets it correct = 35 points. Bets 25 points on another game, gets it wrong = 0 points awarded.

For the college football playoff bracket, the pool manager can decide if each game is worth the same amount (1 point) or if each round the points increase: round 1 = 1 point, round 2 = 2 points, etc.

Download the 2024 CFP Bowl Prediction Pool Manager.xlsm file here

2024 College Football Bowl TRIVIA

• First year of 12 team playoff bracket
• 35 bowl games with 70 teams
• No teams with a losing record
• Best record: Oregon 13-0
• There are only 2 teams left in the Pac-12
• The Alamo Bowl features two teams from the same conference (Big 12 vs Big 12)
• 18 teams with 6-6 record (21 teams last year)
• 21 teams with double digit wins (23 in 2023,  16 in 2022, 23 in 2021, in 2020 there were 5, 22 in 2019)
• One loss teams: Army, Indiana, Notre Dame, Boise State
• Lopsided matchups (in terms of records): Memphis 10-2 vs WMU 6-6, UNLV 10-3 vs Cal (6-6)

Download the CFP Pool Manager and Single Entry Form here

Have fun! As always, I welcome any feedback or ideas for improvement.

Generate A Random Number In Google Sheets

I was thinking about trying to transfer one of my most popular spreadsheet templates, Super Bowl Squares, from Excel to Google sheets. However, macros do not work in Google sheets. I was thinking about trying to recreate at least some of them, and the first problem to solve was how to make a random number generator script in Google Sheets.

Here's how you can create a Google Apps Script to generate random numbers between 0 and 9 in cells A1 to A9 without any repeats. 

Steps to Create the Google Apps Script:

1. Open your Google Sheet.
2. Click on Extensions in the menu.
3. Select Apps Script.
4. Delete any existing code in the script editor, and paste the following code:

function generateRandomNumbers() {
  // Create an array with numbers 0 to 9
  var numbers = Array.from({length: 10}, (_, i) => i);

  // Shuffle the array
  for (var i = numbers.length - 1; i > 0; i--) {
    var j = Math.floor(Math.random() * (i + 1));
    var temp = numbers[i];
    numbers[i] = numbers[j];
    numbers[j] = temp;
  }

  // Get the active spreadsheet and sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Place the shuffled numbers in cells A1 to A9
  for (var k = 0; k < 9; k++) {
    sheet.getRange(k + 1, 1).setValue(numbers[k]);
  }
}

5. Save the script by clicking the disk icon or pressing Ctrl + S. You can name it something like RandomNumberGenerator.
6. Close the Apps Script editor.
7. Back in your Google Sheet, go to Extensions -> Macros -> Import and then select your generateRandomNumbers function.

How to Run the Random Number Generator Script:

• To run the script, go to Extensions -> Macros -> generateRandomNumbers.
• The script will place random numbers between 0 and 9 in cells A1 to A9, with no repeats.

If this is your first time running the script, Google Sheets may ask you for permission to run the script. Approve the permissions to proceed. 

In my templates, I make macros easy to use by running them from a button click. Yes, you can run the script from a button in Google Sheets too! Here’s how you can set it up:

Step 1: Create the Script

1. Open your Google Sheet.
2. Click on Extensions in the menu.
3. Select Apps Script.
4. Delete any existing code in the script editor, and paste the script provided above.
5. Save the script by clicking the disk icon or pressing Ctrl + S.

Step 2: Add a Button to Google Sheets

1. Insert a Drawing (for the Button):

   • Go to your Google Sheet.
   • Click on Insert -> Drawing.
   • Click on the Text Box icon in the Drawing toolbar and draw a text box.
   • Type in a label for your button, such as "Generate Numbers."
   • Format the text and shape as you like.
   • Click Save and Close. The button will now appear on your sheet.

2. Assign the Script to the Button:

   • Click on the drawing (the button) you just created.
   • Click on the three vertical dots in the top right corner of the button, and select Assign script.
   • In the text box that appears, type the name of your script function, which is generateRandomNumbersInRow.
   • Click OK.

Step 3: Use the Button

• Now, whenever you click the button, the generateRandomNumbersInRow script will run, and random numbers will be placed in the cells G3 to P3.

This provides a user-friendly way to trigger the script without needing to go through the menu every time. The first problem is solved! Onto the next one...

Using OpenAI's ChatGPT to Update My Year in Pixels Excel Template

I've been seeing more and more videos on TikTok about this mysterious new artificial intelligence program called ChatGPT. People are saying it will eventually replace Google search. But what really got my attention was a demonstration of ChatGPT writing VBA macros for Microsoft Excel. Now I knew I had to try it for myself. 

I made a Year in Pixels Excel template to track my daily mood back in 2018 but haven't updated it lately. Since it's the start of a new year, I recently received some new comments and questions about how to use and modify the spreadsheet so I figured now would be a great time to update it and add a few new features. Then I had an idea - why don't I use this opportunity to update my mood tracking Excel template to try out ChatGPT? Let's give it a go!

How to have cell fill color conditional formatting change based on the color of another cell?

The request from a user that got me thinking was "how to easily change the colors in my year in pixels sheet?" An experienced Excel user could probably ascertain the colors are applied by conditional formatting formula rules. However, for users who are not Excel savvy, it's not very obvious and there should be an easier way to change the colors. As far as I know, there is none, or at least no easy way in my current version of Excel, to have the fill color in the conditional formatting reference another cell's color. So instead, I will have a macro update the conditional formatting rules at the click of a button. Let's jump over to ChatGPT.

How does ChatGPT work?

Here's the answer after asking it directly: 

GPT (Generative Pre-trained Transformer) is a type of language model developed by OpenAI that uses a transformer neural network architecture to generate text. GPT is trained on a large dataset of text and uses this training to generate new text that is similar in style and content to the training data.

ChatGPT is a variant of GPT that has been fine-tuned on a specific task, such as having conversations with humans. To fine-tune GPT for this task, ChatGPT is trained on a large dataset of conversation data and learns to predict the next word in a conversation based on the words that came before it.

When you input a message to ChatGPT, it processes the message and uses its knowledge of language and conversation patterns to generate a response. It does this by predicting the next word in the conversation based on the input it has received and the context of the conversation so far. The output of ChatGPT is then a response that is generated based on this prediction.

One thing to keep in mind right off the bat: ChatGPT is not always right. It says it right there on the main page under limitations: may occasionally generate incorrect information; may occasionally produce harmful instructions.

For example, I asked "list the ten tallest roller coasters in the world" which should be fairly easy and instead it produces a list that is incorrect and doesn't even make sense.

Not a great start but let's see how it does creating a macro from scratch.

Creating Custom VBA Macros Automatically with ChatGPT

I wasn't sure how specific I needed to be so I decided to err on the side of caution, plus I thought the more detailed I am the less modification I'll probably need to do later. Now I ask ChatGPT: "create a vba macro to make a new conditional formatting rule where range is C4 to N34, if cell value = 5 then change the fill color to the same color as in cell P12." Then it began typing and my jaw dropped.

I copied and pasted the code into Excel VBA editor and it worked! One thing I forgot is this macro will just add new rules applied to the same range. I need to delete all the existing formatting rules before adding new ones, so I ask ChapGPT:

I added the above code to the earlier response and linked it to a newly added button on my sheet. Now a user can change the fill color of a cell, click the button, and the fill colors update automatically to match.

If you want to see all this play out in real time, watch the video I recorded below:

Thoughts on the Future of ChatGPT

It's awesome that it doesn't just spit out the code, but it also suggests how you might need to modify it AND tells you how to run the macro as well. Even though I've only asked it to make simple macros, I already see how this program could save a lot of time.

I'm not done experimenting but so far ChatGPT seems like a much better option over Recoding macros or Google searches that might take you a few tries to find exactly what you're looking for. Especially when you can get custom code on the fly. On one hand, I feel a little obsolete, but on the other I also don't think ChatGPT will completely be replacing programmers just yet as you can see I still had to understand the code and modify it to fit my exact needs.

Download my Year in Pixels template for free here and try it for yourself. Open the macro editor to view the final codes written by ChatGPT.