105|EXCEL – Buried Treasure

Challenges
, , ,
1

BYU Student Author: @Spencer
Reviewers: @Mark, @Marco
Estimated Time to Solve: 45 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
Rumor has it your grandpa buried a stash of gold coins years ago. However, no one has proven this to be true. One day, while cleaning out some files from your grandpa’s old computer, you stumble upon an interesting looking document. The document contains four questions and a note stating, “Here lie the key to unlock the chest, but first you must answer these questions best.” Remembering how corny your grandpa was, you realize that this document is the key to his long-for-sought treasure! At the bottom of the document, you notice a small clue: “Tis buried beneath the two white cars.” Two white cars? Where are those?

Instructions
You must answer the four questions on the “Coordinates” sheet to receive the coordinates to the treasure. All questions are based on your grandpa’s lifelong geography research (grandpa was a passionate geography professor). His research is found in the “Forest” and “World Pop” sheets. For the sake of conserving the memory of your grandpa’s work, DO NOT manipulate the “World Pop” and “Forest” sheets in any way. This includes adding/deleting columns, sorting, copying, etc. Instead, you must connect his data to Power Query and manipulate it there. Be sure to round any numbers to two decimals places and to merge the queries using an inner join. After you have answered the questions, coordinates will spit out on the “Coordinates” sheet. To check and see if the coordinates are correct, plug them into Google. Google will pull up a map with a red pin. From there, click into the google map itself and select “street view” of the pin’s location. If you see two old white cars, then you have found the resting place of the treasure!

Data Files

Suggestions and Hints
  • Input your answers into the light blue boxes
  • You may need to “unpivot” certain columns within the power query editor.

Solution

Challenge105_Solution.xlsx
Solution Video: Challenge 105|EXCEL – Buried Treasure

2

I think this was an awesome challenge. I decided to solve it using PowerQuery in the Mac version of Excel, because I am less proficient using it compared to the windows version. It was great to brush up on some PowerQuery skills.
Challenge105_Data_Dutton.xlsx (552.3 KB)

3

Time to complete: 40 minutes
Rating: Intermediate
I really enjoyed this challenge! I liked using power query and that it was more than manipulating the data.
Challenge105_CoordinateSolution.xlsx (39.9 KB)