This is the third in our three-part series of designing spreadsheets entirely on our mobile devices. Part 1 was a New Year’s resolution keeper and Part 2 was a weighted lottery to make a group decision on where to go for lunch. Today we’ll build a spreadsheet on the iPad that allows you and your friends to play along with the Jeopardy TV show, all while keeping score for a friendly competition.
The motivation behind our mobile spreadsheet is a club that my friend Andrew started in high school called the Jeopardy Squadron. This club was not meant for falsely fattening up college applications by going through empty motions. Its main purpose was fierce head to head Jeopardy competition, as well as honoring Alex Trebek by pasting a photo of his face on a life-sized cardboard cutout of John Wayne. Anyways, I digress…
To build this spreadsheet, we first install Google Sheets on our iPad by searching for it in the iTunes store:
Hit the red plus button to create a new sheet (see the yellow arrow below):
Type in the names of our players at the top. Then create our board for the Jeopardy and Double Jeopardy rounds (and yes, we had to google how many categories there are, the answer is 6 which we depict with the generic A-F below):
So how did the Jeopardy Squadron Club work? Members would watch an episode of Jeopardy live at 7 pm on ABC with a couple of friends. As each question (answer..) comes up, the person who shouts out the answer (question..) first wins the space. There isn’t a penalty for wrong answers. You can set your own house rules for whether or not you need to answer in the form of a question.
In a similar spirit, let’s enter in some hypothetical results for the Jeopardy round. When Player 1 (Andrew) gets a correct answer for a given question, we put his assigned number (1) in the appropriate square:
Now we want to sum up the scores of all the boxes each player got right. First, we add a few lines at the bottom to help with the tabulation:
We enter in a sumif() formula for each column, where we sum the values of the score column in Column A, if Player 1 got the right answer. Note our use of relative references ($ signs) in the formula, which allows us to easily copy the code to columns B:G and for rows 24 and 25:
Tap cell B23, choose Copy, then highlight cells C23:G23, tap and select Paste:
Now we go back up to the top, cell C2 and sum up the scores to get Andrew’s Jeopardy round score:
Let’s now do the same and copy the formula for Players 2 and 3:
Now we can repeat this process for Double Jeopardy, and add a column for the Players’ Double Jeopardy Scores:
We add a column to sum up the score totals for each player after Jeopardy and Double Jeopardy:
For Final Jeopardy, all players secretly write down their wagers on a piece of paper after the category comes out, and then write down their answers as the final question comes out and the classic theme song plays on TV. We add in columns for the wager amount, whether or not each player was right or wrong, and then a formula that adds to the players’ scores if they were right and subtracts if they were wrong:
And that’s pretty much it! Try building one for yourself or check it out here: Jeopardy Mobile Spreadsheet. Remember: to edit the spreadsheet, first save a copy of it on your Google drive.
