Solving ModelOff Data Analysis problem from the second round of 2013 competition.

Solution for ModelOff 2013 Data Analysis problem


         As some of you might know, Model Off 2014, championship kicked off last Saturday, October 25th. With the first round of this modeling competition behind us, I thought that this might be a good time to tackle one of their older problems. I might be biased here, but starting with their Data Analysis problem made a perfect sense to me. Luckily enough, this could arguably be one of the easiest problems they have ever presented. You can download both: Questions/Answers worksheet and Excel workbook with source data for this problem directly from their website.

         As usual, I will not pretend to have the best possible, optimal solution, I simply offer one which works. Since in the real world, we are not constrained by challenging time restrictions, imposed on ModelOff contestants, I was not concerned about the most efficient solution, but rather the one that is more presentable. As an example, my solution heavily relies on Top Ten filtering feature available for PivotTables, this might seem like an extra step comparing to simply presenting all records and then sorting them in order of preference. In my humble opinion this is a better way to present top 1/3/5/n results, rather than looking at extra records of data.

Continue reading

Top 10 Free resources to learn Microsoft Excel

Top 10 Free resources to learn Microsoft Excel

         Students often ask me to recommend best online Excel resources for further reference. After fighting the urge to LMGTFY them 🙂 , I suggest to utilize the largest search engine in the World, and trust Google to present the most relevant results for their query. (Searching for specific terms within relevant time frame would improve your search results dramatically.)

         In addition, the second largest search engine in the World can stream very helpful How-To videos on magnitude of topics. This means that one can search YouTube to learn not only how to unclog a toilet, but also how to create a PivotChart.

         If you are in a hurry, and don’t feel like watching a 10 minute video;SlideShare comes to the rescue, and offers a plethora of presentations, including Excel tutorials. Heck, you can browse through the SlideShare version of this post below.

         That being said, some of us feel safer when using credible and trusted sources for all of our Excel needs. I started compiling the list of useful resources before YouTube and Slideshare came to existence, and added others over time. I hope that this post will satisfy your thirst for Excel knowledge. Many of these sites include tutorials, blog entries, discussion forums and YouTube videos.

Continue reading