Highlights of Gary Cokins Business Analytics seminar

Business Analytics

         Last month I attened Business Analytics seminar by “an internationally recognized expert, speaker, and author in performance improvement systems and cost management” : Gary Cokins.

         My slideshare presentation in the bottom of this post summarizes Gary’s informative and thought-provoiking presentation. In fact, some statements in this post were provoked by this seminar. Gary started by quoting Jeanne X. Harris :“40% of important decisions are not based on facts but rather on intuition, experience, and anecdotal evidence.” Arguably, we could improve the world by basing our decisions on facts, and making better decisions in the process. In addition, better decisions result in better actions, saving us time, effort, money, and other resources. The “actions” part is really important in terms of differentiating Business Analytics from Business Intelligence.

Continue reading

VLOOKUP() vs. INDEX(MATCH()), which should you use?!

VLOOKUP() vs. INDEX(MATCH())

         I recently read an excellent article, where its Excel MVP-author makes a very compelling, albeit biased argument towards using INDEX(MATCH()) function combo, instead of taking the VLOOKUP() route. That article jokingly proclaimed the end of the VLOOKUP vs. INDEX(MATCH()) debate. Interestingly enough, some of the heavy-weight Excel MVPs and expert users weighed in on this issue, in essence continuing the debate. One of the commentators, published an equally brilliant rebuttal, skewing the victory towards the VLOOKUP function. Being a biased VLOOKUP proponent myself, I concur with his conclusion completely. In addition, I’d like to throw in a crazy idea of using SQL as a better, faster, more flexible, and easier solution, comparing to either: VLOOKUP(), or INDEX(MATCH()).

Popularity

         I believe there is no disagreement here: VLOOKUP enjoys tremendous popularity. People have different opinions whether this popularity is justified. I belong to the “ease of use” and “ease of learning” VLOOKUP camp: two of many factors that help us explain high adoption rate of this function among all Excel users.
Continue reading

Coding your first JavaScript application – BMI Calculator.

Error: BMI Calculator could not be displayed, please upgrade your browser.

         We already created a BMI (Body Mass Index) Calculator using Excel VBA last month. How difficult would it be to rewrite that calculator in JavaScript to embed it on any webpage for people to use? Not that difficult , thought I, remembering happy days of browsing the original, NOT corporate owned, HTML Goodies website in search of the coolest JS mouse-over effects, some 15 years ago. You can see the working calculator above of this post, but I would be lying saying that its creation was painless. Surely, “World’s Largest Web Development Site” provides more than enough reference material for all elements required: HTML and JavaScript, as well as optional, but handy styling language – CSS. However, tweaking my code and making it work, was a very time-consuming process. Looking at my final product and comparing it to top 10 Google search results for BMI Calculator makes it all worthwhile…. Not only it incorporates more enjoyable design, it is also equipped with error handling logic.
Continue reading






Solving ModelOff Data Analysis problem using Microsoft Access SQL.

MS Access Solution for ModelOff 2013 Data Analysis problem

         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.”
Continue reading