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

well done !

where can I sen my excel file ?