How I fell in love with The Bachelorette through a Spreadsheet

For months now, some work friends and I have been using the Bachelorette as an excuse to regularly hang out each week. Everyone has their reasons for watching this show: In our case we love to provide color commentary as well as use it as a chance to share our values about love and dating throughout the Bachelorette’s journey to “love”.

However, the show really cemented itself as a Monday mainstay once I realized that I can make a game of it using spreadsheets. Every week my friends and I each offer an analysis on which contestants will do well. So I figured,  “Why don’t we nail this down in a spreadsheet and see once and for all who had the most accurate predictions on who will win?”

You may be thinking, why not use the official Bachelorette fantasy league already created for this purpose? Well 1) That’s no fun, as I’m trying to understand the best way of scoring this game from the ground up. But 2) The official fantasy league requires everyone to put time into creating online accounts and updating each week. With my method I can coax my guests into the game with almost no effort on their part: they can simply verbally tell me their guesses in person and I can nerd out in my spreadsheet.

The Tools

I opted to use Google Sheets instead of Excel for this, so that players can enter and view their guesses and scores. We’re a small group of close friends, so NO I did not lock down players ability to edit the sheet, or also to be influenced by seeing other people’s guesses. It turns out all four of us were too stubborn to be influenced anyway! If the competition grew into a larger group of acquaintances where influencing guesses, cheating or honest mishaps might occur, I could ensure data validation by having players fill out a Google Form to populate the sheet each week, thus removing the need for anyone but the Game Master to access the sheet!

It was fun to use the TV to show the scores/guesses on the spreadsheet after each episode. I did this using Google Chromecast, but I also had an HDMI output on my laptop as a low-tech backup. For a sense of showmanship, I like the idea of not showing anyone the sheet, only taking in the guesses using the Google Form, then queueing up some music and making a fun Google Slides presentation out of the final score reveal.

The Game

Because I’m motorsport racing fan, I see this in terms of Gold, Silver and Bronze. Each week, at the end of an episode, each person in the game guesses who they think will finish 1st, 2nd and 3rd place. In the case of the Bachelorette, 3rd place means being eliminated 2nd to last, 2nd place is the last person to be eliminated, and 1st place is never eliminated and is chosen by Bachelorette Rachel Lindsay to offer a proposal. The spreadsheet is split up into 4 tables:

Weekly Voting Table

The centerpiece of this spreadsheet is the Weekly Voting Table where each player enters their guesses. The episode number is the primary key (the main unit of data). Each player selects a name from the list of contestants as their choice under the 1st 2nd and 3rd place headings. So for four players, we have 4 sets of 1st, 2nd and 3rd place headings.

The Cast List Table

This table contains each cast member as the primary key and a binary input for whether they are still on the show with each episode. For each episode they are still a contestant, they get a point. This helps the sheet determine who is 1st, 2nd and 3rd place. The top 3 contestants (aka point leaders) are pulled placed on the Final Results Table.

Final Results Table

This table pulls the 1st place, 2nd place, and 3rd place contestants from the Cast List Table. This is done by putting the cast member with the most points in the 1st Place slot, second most points in the 2nd place slot, ect.

The Scoring Table

The scoring table matches the names from the Weekly Voting table with the names in the Final Results Table. Each match of names is a correct guess and is worth 10 points as a base. However, each episode is weighted in value, the earlier the episode, the more points you get for guessing 1st, 2nd, and 3rd place correctly. This is done with a points multiplier, with the first episode being 10x multiplier and the second to last episode (the last chance to guess) being a 2x multiplier. Players that can pick a winner early on with so little information really soar in the points. However with a bigger-than-ever cast of 31 contestants, and many dramatic twists along the way, this can prove daunting. Even if you don’t guess the winner early on, you can still win by identifying who will be third place, as an example. I love the idea of pegging a certain contestant as destined for 3rd place. I certainly had my focus on this slot, thinking “Who is truly ordinary enough to not be disqualified, but just charming enough to keep getting a rose?”

Post Mortem

We’re in the home stretch of the season, so the writing is on the wall as to who will win this season. (I will edit this article upon viewing the finale and tallying the winner). So far, so long as the show’s results are as expected, the scoring system appears to have worked fairly. Players 2&3 had significantly more Bachelorette viewing experience than players 1&4, and they were much more successful in guessing each week. Subsequently they had higher scores. The player in the lead did have the greatest number of correct guesses regardless of point multipliers, however the multiplier system gave runner up a chance to surpass the leader in points with an early correct guess for 1st place.

Some improvements I’d like to make for the future:

-Make a drop down list of contestants for data validation purposes when entering guesses.

-Explore Google Forms as a way to collect everyone’s guesses for each episode

-Make a Google Slides document out of the final scores reveal , crossing my fingers that Google Slides can pull data live from Google Sheets.

-I will be seeking feedback on this scoring system for any holes in logic that I’m missing.

-I would like to add more chances/complexity to win the game without it turning into a total beast like the Bachelorette Drinking game is. I simply can’t be bothered to watch the episodes closely enough to follow details that we can place bets on, however, milestones like the First Impression rose are an easy one to add in.

-I will be signing up for the fantasy league in the future, to contrast/compare its scoring system with my own.

This was definitely a fun way to play around managing data in a spreadsheet while designing a scoring system for an elimination game. The more unexpected challenge of this spreadsheet was making it fun and easy to collaborate with other players, but also providing an added spectacle to the weekly episode viewing. I hope to add an increasing level of intrigue, showmanship and excitement around this little game I’ve created.

What a helicopter taught me about job training

Helicopters are just plain difficult to learn to fly for beginners. They have super sensitive controls: A simple gesture of only a few millimeters can put the helicopter in a bank or bring the nose down and fill the windscreen with nothing but a view of the ground. With added maneuverability, there’s so many ways to suddenly go plummeting if you don’t know what you’re doing.

Add to this that I’m a person that gets “flustered” when presented with multiple things that I need to learn at the same time. I have too many questions about each individual part of a process that I need to square away before I can gain a sense of awareness. I’ve noticed that job training does not account for this, and often it is an abrupt “sink or swim” situation. Since I love planes and everything aviation, it’s times like this I ask “what do pilots do?” For this particular issue of getting flustered, I found out exactly what a pilot instructor would do, one calm winter day at an airport tarmac for my first helicopter flight lesson.

For this first lesson, my task was to hover in place. I studied the instructor pilot’s hands and feet, each foot controlling a pedal and each hand with its own control (called a cyclic and collective). We were simply hovering a few feet off the ground, and yet both his hands and feet were making constant adjustments just to keep the aircraft still.

Now my turn. Despite a lifetime of video gaming experience, I could not balance the heli. As soon as the dual controls were eased over to me we dipped into a slight roll or downward pitch. The instructor had to constantly override and correct for my sloppy maneuvering.

Calmly, he suggested I focus in on one control at a time while he handled the others. I got a feel for just rolling left to right. Then I tried adjusting only pitch. By eliminating the other axis of control, he allowed me to focus and get feedback on one isolated part of the controls at a time until I could do it naturally and focus on the next part. I also benefitted from the power of repetition in this method. If you do a small piece of a process repetitively, the cycle of repeating a similar task is much shorter. With conventional training, I’m accustomed to the step-by-step process being a journey with a long slow cycle in the beginning. By the time I’ve made it back to the beginning again, I haven’t retained what I’ve learned from the previous go around.

Now when faced with a complex process to learn, I can’t help but compare it to the helicopter. Any chance I get, I try to break a process down, one element at a time. As each element becomes second nature, I can focus on the next one.

What does this mean, practically, in terms of training in e-commerce operations? I’ve noticed that new hires are often given a step-by-step approach to learning a complex process. Often this means switching between types of tasks, interfaces and people. One minute you’re trying to cross-reference a part number with a SKU in the database, the next minute you’re fussing over pricing and cost in excel, and then calling vendors. Nowadays I can switch with ease through these moving parts, but I found this to be completely overwhelming in my last job training experience. I believe I would have learned faster using the pilot’s method of isolating each moving part first. I would love to hear about any corporate training methods that utilize this concept!