Friday Challenge – Data Transformation

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, 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!