Bottom Line: Take this Excel Challenge to create a solution that calculates the points and the winner for a guessing game.
Skill Level: Intermediate
Watch the Video
I went live on YouTube to walk through the solution and show some great submissions from our community members (that's you). The recording of the session is below.
This should be a great opportunity to learn some new functions and formulas. I covered the IF, ABS, HOUR, ROUNDDOWN, TEXTBEFORE, LEFT, SEARCH, RANK, FILTER, and LET functions. Plus conditional formatting and many other data analysis techniques.
The SOLUTIONS file below contains a table with a list of all submissions file from the community. There is a column with a description of the file and a link to download the file.
Download the Excel File
You'll need this Excel worksheet to complete the challenge:
And here is the solution file that I will use on the YouTube Live training session.
Here is a version of the file that will work on older versions of Excel, if you experience any issues with the file above.
File for Hosts
If you're hosting a baby shower and just need the Excel file to run the game, then the following file is for you. We've prepared a blank file that contains all of the calculations. You just need to enter the participant names, guesses, and actual results. The winner(s) will automatically be calculated.
And here is a Google Sheets version of the file. Go to File > Make a copy to save a version that you can edit and share.
Baby Shower Guessing Game
My wife recently went to a baby shower where they played a game. All of the attendees had to guess what day the baby would arrive, the time of day the baby would be born, and the weight and length of the baby as well.
The problem is, they didn't have a practical way to score the guesses, since there are different levels of variance for the four different categories.
I've come up with some scoring rules, but I'd like to have the points tallied automatically based on the actual stats. That's where YOU come in, my friend.
I've entered everyone's guesses into an Excel worksheet and left two blank columns for you to fill as part of an Excel challenge.
Here are the rules for scoring each attendee's guesses.
Each participant starts with 50 points per category.
The following points will be deducted from each category when there is a variance between the actual and guessed value.
- Date of Birth: 2 points for each day
- Time of Birth: 1 point for each hour
- Weight: 5 points for each pound (lb)
- Length: 2 points for each inch (in)
Round down to the nearest hour, pound, and inch before calculating the difference for time, weight, and length.
The participant gets 100 points for an exact match in a category. This is the total possible score for the category. They do NOT get 50 + 100. Just 100 points for the category.
To check your work, you can see the example score in cell F6.
Here's the challenge. I want you to use formulas or other features of Excel to calculate the points for each player and determine who the winner is.
Bonus: Determine who came in second and third place and indicate those placements on the spreadsheet as well.
Upload Your Solution
If you are up to the challenge, we want to see your solution!
Please leave a comment below the post with a brief explanation of how you solved the problem.
You can upload your file with your answers at the link below.
We'll then create a video tutorial with the most popular solutions and explain how to solve the challenge.
Have a question? Leave a comment and we will answer it.