August 2015

You are currently browsing the monthly archive for August 2015.

Another thing that happened to me since 2009 was home ownership: bought a place and put my name on a serious mortgage contract.  As I go about doing my daily business, I often wonder: how much of this home do we actually own, and how much do we still owe??

What came immediately to mind is the fundraiser tracker.  You’ve probably seen something like this in conjunction with your alma mater, local animal shelter, or kid’s day care asking you for money:

0823bars2_0

 

I wanted to create a visualization like that, in Excel, next to my family’s finances.  Like a true engineer, I absolutely hate manually updating anything, so this had to be automatable.  How do I do it?

 

 

Let me spare you of the gruesome details of my experimental process, and just say that you can draw pictures with bar charts.  Wanna draw a circle with an absolutely rectangular bar chart?  Let’s do it visually:

Step 1 – Create a stacked bar chart.  The number of stacks that you need depends… and trust me for now that you need 4 for a circle:

0823bars2_1

Step 2 – Make sure you have enough bars.  100 is always a good starting point in my opinion.

0823bars2_2

Step 3 – Set the gap between bars to 0%.  Now this chart looks like some European country’s flag.  Still quite rectangular:

0823bars2_3

Step 4 – Adjust the height of these 100 bars so they look like this.  Note how some stacks are much taller than others:

0823bars2_4

Step 5 – Resize your chart into a square.  Clean up the colors and update the axes ranges, and…

0823bars2_5

Ta-da!  Here’s the circle!  Don’t like how pixelated it is?  Add more bars!

0823bars2_6

 

The key here is to think of a picture as a rectangle with w-by-h pixels.  Then you can replicate it with w stacked bars each having a maximum height of h.  How many stacks you need depends on how complicated your graph is.  Is it possible in theory to draw a Mickey Mouse?  Sure, but I intend to stick with simpler images that can be abstracted into a few layers of colors.  You’d need to either set up formulas or a lookup table for the height of these stacks on each bar.  For a circle centered on (a,b), remember the formula is (x-a)^2 + (y-b)^2 = 0.

 

Back to my original goal of drawing a target tracking graphic.  I decided to go with a house complete with a chimney, even though our home looks nothing like it.  It involves a handful of vertical, horizontal, and sloped lines:

0823bars2_7

 

The shape of my house, including where the chimney is, is entirely parameter-ized.  So by updating a few cells I can get to alternate versions like these:

0823bars2_8

 

Lastly, I needed to fill it in with the progress tracker, as follows.  Note that my blue bars and white bars within the house are two separate data series, with heights adjusted dynamically to the input value:

0823bars2_9

0823bars2_10

 

Just to illustrate how flexible this setup is, if you don’t like my vertical split, the complete/remaining portions can be divided horizontally as well:

0823bars2_11

 

This way, I’ve got a bar-based graphics that updates automatically.  The only thing left to do is actually paying off that mortgage!

Only because my soon-to-be-former coworker Monica asked about this topic, I decided to dust off this blog (finding that login was no easy task!).  It’s hard to believe but the date stamp on the most recent post was… six years ago!  It’s not that I’ve forgotten about this blog, but I’m borderline at a point in life where you can’t teach an old dog new tricks.  And when I don’t learn new tricks, it’s hard to blog about them.

Well, I did learn one thing more recently than 2009: bar charts.  Remember when we all first learned about these fundamental expressions of statistics, like, in fourth grade?  Drawing the axes with a pencil and measuring out the height of each bar with a ruler was among the most painful part of any math class that I can remember… and yet, this two-post series is about those.  A steroids-injected version of those boring bar charts.

In its first part, we will examine how to create waterfall charts with Excel’s native capabilities.  A waterfall chart is one where floating bars are used to illustrate the progression of a number.  For example, I used one in my recent guest blog entry on Hungry for Points, to outline how my points & miles balance changed between two points in time:

0823bars1_4
Added some, subtracted some, and a new balance!

 

On the surface, it seems impossible to draw floating bars with Excel’s charting tool.  That may be true, but the trick is to craft an optical illusion with white bars.  The same idea can be applied to both overlapping clustered bars and stacked bars.  Let’s see how the former works:

Step 1 – Determine the step-wise cumulative value, and the increment/decrement amount:

0823bars1_0

Step 2 – Create a clustered bar chart where each step has two bars: for increments, bar 1 should be the previous cumulative amount and bar 2 should be the new cumulative amount; for decrements, it’s the opposite.  For the first and last steps representing the balances, bar 1 should be 0:

0823bars1_1
“Bar 1” is orange here.

Step 3 – Make sure bar 1 is in front and set the overlap to 100%:

0823bars1_2

Step 4 – Fill bar 1 white, or whatever your background color is:

0823bars1_5

Step 5 – Color the individual bars:

0823bars1_3

Step 6 – Add data labels to explain your chart.  These can easily be formulaic and linked to your numbers:

0823bars1_4

 

Voila! This may look exotic, but it really isn’t!

Stay tuned for the next article in this series where we take this technique to the next level!