Archive for May, 2014

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

This is a continuation of Session 9 – Averages are mean

In the earlier episode, we talked about AVERAGE and why it should be avoided. In this session, learn about 8 power analysis techniques that will lift your work above averages.

In this podcast, you will learn,

• Re-cap – Why avoid averages
• 8 Techniques for better analysis
• #1: Start with AVERAGE
• #2: Moving Averages
• #3: Weighted Averages
• #4: Visualize the data
• …
• Conclusions

As I am doing a road-trip across parts of southern India (we are in the beautiful temple town of Madurai as you read this post), here is something to keep you busy.

Fill in the blanks & post in comments.

I struggle doing _________________ with Excel.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 9th session of Chandoo.org podcast, lets raise above AVERAGEs.

AVERAGEs are a very popular and universal way to summarize data. But do you know they are mean? Mean as in, AVERAGEs do not reveal much about your data or business. In episode 9 of Chandoo.org podcast, we tackle this problem and present solutions.

In this podcast, you will learn,

• What is AVERAGE?
• Pitfalls of averages
• 5 statistic concepts you must understand
  • Standard Deviation
  • Median
  • Quartiles
  • Outliers
  • Distribution of data
• What next?

This article is written by Michael Hutchens from Best Practice Modelling.

In the first article on Modular Spreadsheet Development, we got a high level overview of Modular Spreadsheet Development principles. This article discusses the practical implementation of these principles in Excel.

From my experience using Modular Spreadsheet Development over the past decade, there are three increasingly-efficient methods of implementation in Microsoft Excel:

1. Manual implementation;
2. VBA automated implementation; and
3. Commercial add-in implementation.

This article provides a comprehensive overview of each of these methods and a summary of their advantages and disadvantages.

Hello friends,

First a quick update about state migration visualization contest.

We got more than 50 wonderful entries for this contest. I have compiled 50% of the entries. Due to personal reasons (its annual vacation time at our household), I had to delay the rest and resume it after first week of June. So the results will be announced in 2nd week of June. Thanks for understanding and your patience. Watch this space for mind-boggling dashboards in 3 more weeks.

Click “Read more” to get a sneak-peek at 3 random entries & 4 useful Excel links.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

Here is a problem we all face once in a while. We inherit this bulky, bloated, leaking at the edges workbook from a colleague. Now the onus of maintaining it is on us. The person who made this workbook is nowhere to be found. May be she is vacationing in Hawaii sipping pineapple juice. May be he became a vice president and roaming the country in your company’s private jet.

So what do we do? How do we handle this inheritance?

That is the topic of our podcast, episode 8.

In this podcast, you will learn,

• An overview of the inheritance problem
• 6 Tips to understand workbooks made by someone else
• Tip 0: Talk to the creator
• Tip 1: Model the workbook on paper
• Tip 2: Locate the engine, ie the formulas
• Tip 3: See what else is under the hood – hidden sheets, names, VBA code
• Tip 4: Annotate (add comments) as you learn
• Tip 5: Locate the controls – inputs, assumptions, scenarios
• Tip 6: Re-construct from scratch
• Deep dive in to understanding the formulas
• Deep dive in to understanding VBA code
• Conclusions

Today, lets learn how to make a simple timer app using Excel. First some background…,

Recently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.

So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.

This way, I can see my progress over next few weeks and may be plot the trend.

This article is written by Michael Hutchens from Best Practice Modelling.

This article provides a high level overview of Modular Spreadsheet Development principles. In next part the implementation of these concepts will be discussed.

Modular Spreadsheet Development – An awesome concept

I want to share a concept with you so awesome that once you understand it you may never use Excel the same way again.

This concept, called Modular Spreadsheet Development, makes it possible to build spreadsheets exponentially faster while reducing the risk of errors and making spreadsheets much easier to understand.

This concept is not completely new, but I’m writing this article because the spreadsheet modelling world would be a much better place if it was more commonly adopted.

Here is an interesting scenario.

Imagine you are responsible for customer satisfaction at ACME Inc. Every month you track customer satisfaction rate for the 3 products you sell which are conveniently named Product A, B & C.

You also have bands for the satisfaction rating.

• Rating of 85% or below is Average
• Rating between 85% & 95% is OK
• Rating above 95% is good

At the end of the year, you want to visualize the ratings for last 12 months for 3 products along with bands.

Something like above.

Unfortunately, there is no “Insert Banded line chart” button in Excel. So what to do?

That is what we will learn today. Ready?

This weekend, lets get passionate 😉

Few days ago, Cheryl, one of our forum members asked this question:

How do you know Excel is your passion? Or is it?

I am searching for my passion, you know that thing that makes my heart sing. I mean I am listening for the pitter-patter in my ticker. So how do I know if Excel is it? Or anything for that matter. I am looking for that thing that will make me turn my tv off. (TV is my crack, I am truly addicted). I thought it was database development and honestly I am not altogether sure that it isn’t. Excel may be a substitute. A more attainable passion.
Give me some insight peeps. Some thoughts, musings, ideas.

As usual, many of our forum members chipped in with words of wisdom. Hopefully Cheryl saw their replies, if she ever managed to turn off that tv.

That gave me an idea for this week’s poll.

What are you passionate about?

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 7th session of Chandoo.org podcast, lets make you aweSUM().

Imagine for a second that Excel cannot add up numbers. And no it cant subtract them either. What would that look like?

A glorified Notepad. That’s right. Excel’s ability to add up numbers, along with features like formulas, charts, pivot tables & BHATTEXT() are what make it such a lovely software. May be not the BHATTEXT(), but we all agree that Excel is so versatile and useful because it can add up numbers (and perform other calculations) with ease.

But how well do you know the SUM formulas of Excel?

In this podcast, you will learn,

• Special personal fruit announcement 😛
• + operator
• Status bar & total rows in tables
• Auto Sum feature
• SUM() function
• SUMIFS function
• Special cases of SUMIFS function
• SUBTOTAL & AGGREGATE functions
• Other summing functions – SUMPRODUCT etc.