This weekend starts the ModelOff competition.
“ModelOff is the world’s largest professional competition for Microsoft Excel, Financial Analysis, Investment Analysis and Financial Modelling.” -Modeloff Site
If you haven’t been to the site, you should check it out: ModelOff
So this Friday challenge is based on a 2014 question on Data Transformation. I have experience using Excel for a large data migration from a client database to Salesforce.com, so I know that people all around the world use Excel for these types of activities. This is a fun challenge that will definitely make you think.
The Data
You can download my sample file here: Data-Transformation-Challenge-Sample-Records.xlsx
Here is what the data looks like:
| Sample Data |
| __11 AM 27-Mar-2016___4.970 kbps |
| __1 AM 29-Mar-2016_4.429 kbps |
| 8 AM 10-Jun-2016_5.284kbps |
| __11 AM Wednesday 04-May-2016___5.540kbps |
| ___1 PM Monday 18-Jul-2016_6.454kbps |
| _8 PM 7th-Apr-2016_2.785 kbps |
| _6PM 8-Oct-2016_5.463 kbps |
| __8 AM Tuesday 16-Feb-2016___2.244 kbps |
| ___2 AM Wed 28-Sep-2016_7.637 kbps |
| _12 AM 07-Aug-2016_9.54 kbps |
| _1 PM 10-Nov-2016__4.795 kbps |
| _4 AM Wednesday 02-Mar-2016__8.180 kbps |
| _10 PM Mon 25-Jul-2016___0.440 kbps |
| ___3AM Thu 7th-Apr-2016___4.931 kbps |
| __1 AM Thu 28-Jul-2016___5.740 kbps |
| __2 PM Wednesday 06-Jan-2016___8.960 kbps |
| 1 AM Sunday 22-May-2016__8.282 kbps |
| 10 AM 30-Mar-2016___6.649 kbps |
| ___10 AM Friday 23rd-Dec-2016__9.323 kbps |
| __8 AM Tue 27-Dec-2016__2.764 kbps |
| __7AM 7-Mar-2016___1.136 kbps |
| _8 AM 23-Jul-2016_8.157 kbps |
| ___1AM Thursday 13th-Oct-2016___6.430kbps |
| 7AM Sun 09-Oct-2016_1.192kbps |
| ___6AM 18-May-2016__3.582 kbps |
It doesn’t seem too tough until you dig into the data. There are varying counts of spaces and underscores, some of the dates are text and some are numbers (like 23rd-Feb-2016 and 23-Feb-2016). So it will take some doing.
Challenge Questions
See if you can answer these 2 questions:
Question 1: What is the average KBPS for 2016?
Question 2: What is the average KBPS May?
Now this can be done with many helper columns, but see if you can get the answer with just 2 formulas:
a) One for the date of every row
b) One for the KPBS for every row
It took me several attempts, but it can be done.
Test Grading is Set as Follows:
Grade C = You get the correct answers with helper columns and not just 2 formulas
Grade B = You get the correct answers with NO helper columns and just 2 formulas (but you use more than one Excel IF function)
Grade A = You get the correct answers with NO helper columns and just 2 formulas (One for Date and One for KPBS) and only use 1 or less IF statements.
See if you can find the answers and test your Excel Data Transformation abilities!
Steve=True
