Solving ModelOff Data Analysis problem using Microsoft Access SQL.
Last week we solved ModelOff’s Data Analysis problem from their 2013 championship. Since the second round of 2014 Model Off competition takes place this Saturday, November 8th, let’s pay respect to the data superheroes making it thus far. Our previous ModelOff solution involved using PivotTable feature of Microsoft Excel. Would you believe that we can realistically conceive a solution to the Data analysis problem, using Microsoft Access, or even better, Microsoft’s flavor of the SQL language?
I am as big of an Excel fan as the next guy, much bigger, actually, on the second thought. However, I also believe that when possible, using the right tools for the job will yield better, faster results, than duct-taping your workarounds. So, why Access? Why on Earth, SQL? Well, let’s go to the source: ” If you often have to view your data in a variety of ways, depending on changing conditions or events, Access might be the better choice for storing and working with your data. Access lets you use Structured Query Language (SQL) queries to quickly retrieve just the rows and columns of data that you want, whether the data is contained in one table or many tables. You can also use expressions in queries to create calculated fields.”
Solving ModelOff Data Analysis problem from the second round of 2013 competition.
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.
Creating user-defined Excel functions (UDFs) – Temperature Conversion and BMI Calculation functions from scratch.
Our last two posts focused on using, or working with somebody else’s VBA code. This article will focus on creating UDF functions from scratch. We will start with a simple temperature conversion from Celsius to Fahreheit, and vice versa. We will then calculate metric Body Mass Index (BMI), graduating to an imperial BMI calculation with 3 variables.
Since we’ve already worked with VBA code, we know some basic syntax rules for our future functions:
1. Start out with
Option Explicit statement.
Note : Option Explicit statement forces explicit declaration of our variables, and gives us an error message if we have a typo instead of performing incorrect calculations.
2. Define our function name and its parameter(s)
Function FunctionName(Parameter DataType) As DataType.
Note : VBA makes definining data types optional, but it is a good habit to have.
3. Perform our calculations:
Your code goes here.
4. Exit function and return it’s value:
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.