125|EXCEL – Debugging Escape Room

BYU Student Author: @Jonathan_Weston
Reviewers: @Brett_Lowe, @Donovon
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

  • Excel

Note: This challenge uses a VBA module to enable regular expressions that is not supported by Mac OS.

Need a program? Click here.

Overview
You are the new intern at Dunder Mifflin Paper Company, Inc. You want to go home and begin your relaxing weekend, but the evil HR representative, Toby, is holding you hostage. He has an Excel file filled with results from various training modules and quarterly reviews to calculate next year’s raise for each employee, but it is riddled with errors and bugs, and he wants you to fix it. You worry that he really could be the Scranton Strangler, so you decide to help him.

As soon as you agree, lightning strikes, and thunder booms. Toby has locked you in the Annex and won’t let you out until the job is done. To ensure that you do it right, Toby already counted all the errors and used those numbers to create a password that you must share with him to escape the annex. To create this password, you will use the number of each error type to assemble pieces of a regex statement that will extract the correct password from the “Scary Wall of Text” tab.

The Excel file for this challenge is a macro-enabled workbook. It’s possible that your computer will only let you access the file in View mode. To unblock this file, go to your file explorer, right-click on the file, click properties, and then click “un-block”.

Instructions
Here are the rules of the game:

  • In cells B2:B6, manually enter the count of each type of error. It may be helpful to highlight the cells containing errors and color code those highlights to the error types. Below you will find an explanation of each error.
    • Hard Codes: An actual number was manually entered in the cell where a formula should be.
    • #REF! Errors: The cell returns a #REF! Error.
    • Formula Errors: The formula in the cell isn’t the correct formula, references the wrong cell, or does not reference the correct range.
    • Missing Data: No data is contained in the cell. This error type does not include cells or rows that are supposed to be left blank.
    • Wrong Data Type: numbers are treated as a string data type rather than numerical.
  • In cells C2:C6, write an xlookup statement that will use the number of errors found for the error type, and return the corresponding regex statement segment. For example, if one error was found for Hard Codes, cell C2 should return “\$”
  • In cell C9, concatenate all the regex segments in C2:C6 into a single regex statement.
  • Use the regex statement created in C9 to extract the correct password from the wall of text in the “Scary Wall of Text” tab. To do this, you will use this formula “=RegExpExtract(text, regex)” in cell C12. FYI, RegExpExtract is a user defined function, it does not come built into Excel. This function is created as a VBA module that you can call upon, which this file enables you to do because it is a macro-enabled workbook.
  • Present this password to Toby and he will release you.

Congratulations! You escaped the annex!

Data Files

Suggestions and Hints
  • Here is a link to a website that has a regex cheat sheet and explains this and other user defined functions for regex in Excel.
  • A great way to find bugs in your worksheet is to go to Formula Auditing under the Formulas tab.
  • The “Show Formulas” button will show the actual formula in the cell, not the output. This is a good way to find cells that are hardcoded.
  • The “Error Checking” button will go over each cell that doesn’t follow the patterns to the cells next to it. This is indicated by the green triangle in the top left corner of the cell.

Solution

An entertaining little challenge that combines humor and some good introductory level excel functions. Error checking is vital! Also a good introductory space for RegEx without the intimidation of actually creating the expression on your own.

That was a fun challenged that helped me refresh my Excel skills. It was been a while since I took the class so this was a chance to refresh simple skills like xlookup and VBA while learning new ones like using regex on excel


Haven’t had a chance to use Xlookup before. This was a great learning experience, and I got to escape Toby!!

2 Likes

This was a fun challenge and allowed me to use certain functions like Xlookup for the first time.

1 Like

Excel Challenge

This challenge allowed me to develop my Excel skills and learn about XLookup. The Office theme made it fun to complete!

Challenge125_Data.xlsm

I really enjoyed this challenge, it was relatively quick to complete and the theme was fun.
Challenge125-DataSolve

Time to Complete: 30 Minutes
Rating: 4.5/5
Overview: This was a super fun exercise, I feel like I learned a lot about troubleshooting with Excel! I love that it is The Office themed! I have included my solution/attempt down below.

Time to complete: 30 min
Rating: Intermediate
I liked this course and learned that I can show the formulas which I’m sure will come in handy.

Time: 1 hour
Rating: Intermediate
Love the office theme! This was definitely a challenge for sure but was fun learning new tools.

Time to complete: “20 minutes”
Rating: Easy

I thought this was a fun challenge to complete that gave me more practice with X-lookups.

Time: 45 min
Rating: 4/5
Super cool puzzle! I also love the Office.