Fortnightly Data Challenge #1 – Museums

Challenge your Data Analytics skills!

How it works:

Each fortnight we will present a data challenge to be solved. These challenges will be a mix of Excel, Power BI, Tableau and SQL and are designed to increase your proficiency in skills across the data pathway. We encourage everyone on the Data Pathway to take a crack and challenge yourself!

While you will be able to compete in the challenges with just the skills learnt in the course, to really excel and come out on top we encourage you to do some research of your own! After the challenge is complete, I (Jimmy) will go step-by-step through the challenge with a video walkthrough. Submit your answers through Discord – and follow along for all updates and discussion!

The winners will be announced on the SitRep, fortnightly on Fridays at 4 pm AEST.
The stream can be found here: https://www.twitch.tv/withyouwithstream
and winners will receive a badge to proudly display on the WYMW Data Discord!

Challenge #1 – Museums

Download the Workbook here

The workbook contains the following sheets:
1. museums_USA
Your dataset for the challenge. Contains name, type, location, income and revenue for museums in the USA.

2. challenge questions
This sheet has 10 questions to be answered using COUNT, SUM and AVERAGE formulas, including IF and IFS.

3. Selector Challenge
This sheet contains the scenario, the challenge and points score.

Scoring

You will be scored on both the Challenge Questions (score out of 10) and the Selector Challenge (score out of 5) for a total of 15 points.

Selector Challenge

Scenario

You are a Data Analyst at Museum HQ™ in the USA.

You keep recieving requests from managers of states asking: 1. How many museums are in their state, 2. the sum and average revenue per museum, and how does this differ by museum type.

Rather than querying the data each time you receive a request, you have decided to build an interactive worksheet for users to make a selection and receive the metrics they need. 

Challenge

Build an interactive worksheet that shows the Count of Museums, Sum of Revenue and Average of revenue for the selected Museum Type and State. You can use the cells to the left or create a new worksheet. Points are awarded based on the levels of interactivity for users as per the below table. 

Points Score: Levels of Interactivity

Users can type or copy/paste directly into cells1 point
Users can select from a dropdown2 points
Users can type & the dropdown list filters3 points
Allow the use of wildcards+1 point
Users can select any number of selectors+1 point

Submit your answers

Submissions close 30/10/2020 at 12:00pm AEST.
Submit your answers directly to Jimmy via the Data Discord.
Good luck!

Not yet on the Data Analytics pathway? Click the button below to explore our courses:

Help out a mate

Help a friend kickstart their career through thought leading digital career content. Everything from Data Analytics through to Cyber Security. 

Share on facebook
Share on linkedin
Share on twitter
Share on reddit