Everyone! We've moved to WordPress.







Health level - a measure on the overall health of a country




One-Way Sensitivity Analysis




Then multiply by the new group weight...

...our weights again add up to 100.0%

Easy One-Way Sensitivity Analysis in Excel

Step 1 - Set up your calculations table

This is how I set up my calculations tab tab:





Step 3 - Link each Scroll Bar 



Step 4 - Adjust Each Value



Step 5 - Sum and Define Proportions






Download the file
One Way Sensitivity Analysis.xlsx

Visualizing.org: Garbage In, Garbage Out

There are a lot of awful visualizations and info graphics floating around the internet. I've sent some of the worst I've found to my new internet friend, Kaiser Fung, at JunkCharts. His blog hosts a formidable, if unfortunate, collection of chartjunk and he attempts both to make sense of them as well as to provide suggestions on how to make them better. 

So where do I find these graphics?  I go to Visualizing.org, a site I am finding harder to respect as time goes on.  That might sound harsh, but if the aim of the site is to "make sense of complex issues," I think they often miss this point while giving praise and monetary rewards to data designs that, in fact, make issues harder to understand.  To demonstrate this point consider a recent winning visualization, E-CUBE-LIBRIUM, from a team at Columbia University.  According to the team: 
By configuring social, economic, and environmental data in terms of a "Rubik's Cube" analogy, the cube represents a country's growth with inversely proportional categories placed opposite each other. The 3D extrusions on each cube face is a sustainability indicator, showing volumes where data increases or decreases. From these models, we are able to quickly draw connections and visually identify how each factor affect the equilibrium of the entire system....For example, how do economic gains adversely affect social and environmental health? In each cube, we are searching for stable equilibrium and positive, balanced development that can be sustained over time. Unstable equilibrium may appear to be balanced, but it is not sustainable, due to an irresponsible levels of CO2 emissions, or alarming income and social disparity, for example.
Here's how the country of Chad is represented in the Rubik's cube analogy:


But there are larger problems that seriously undermine the team's work.  First, this paragraph I excerpted from their write up:
I'm not sure what to say here except...no, these things aren't possible.  There's a reason we don't test a bar chart for its weight in pounds - or how far we can toss a three-dimensional pie chart like a Frisbee.  Graphs are representations of abstract values in made-up space; they don't have inherent physical properties.    

Which brings me to the second major problem.  Because humans are pattern-oriented, ideas that manifest symmetry and balance can be appealing and convenient.  Consider American politics: you might sit on the left, the right, or smack dab in the middle.  It's an arguably useful simplification; but when you consider where you fall on the political spectrum, is the "ideal" policy truly at the center point?  Put another way, what reason exists to assert that if a country's indicators aren't in "balance", or tending toward some abstract and invented equilibrium, national turmoil will result?  Nothing, of course.

But a steady rise in CO2 emissions sounds like sustainable growth, right?  Maybe, but E-CUBE-LIBRIUM never speaks to this point visually - it's a point asserted in the team's write up.  In fact, nothing new is gained by the visual Rubik's cube.  The actual country indicators by themselves, as numbers, likely do a better job of telling this story, if it exists.

Which brings me to the biggest problem of all: the entire thing is rather heavy-handed.  I believe the designers knew going in which countries they wanted to portray as "unsustainable." Consider,
Because there's nothing in this visualization that indicates system imbalance, or, for that matter, irresponsible CO2 emissions or alarming social disparity, I’m left wondering if these items aren't just the preconceived notions the designers imposed upon their visualization.  Indeed, the passage appears to argue that their design could give a misleading impression about a country’s stability, that knowledge of a country not presented here (such as social disparity) might mean the appearance of sustainability is incorrect.  Put another way, it doesn't matter what our visualization says, it can't be sustainable because we can't have a country that pollutes at an alarming rate violate our ridiculous analogy. Sounds like confirmation bias to me.  

Apparently, Visualizing.org, Columbia University, and the United Nations disagrees with me (and, to some extent, Newtonian Physics). This team's nonsense won their school $10,000 to continue visualization education.  Hopefully, Columbia will invest in some books on the subject

I'll leave you with this.

Find Me at an Upcoming Conference


Change the Font Size, Color, and Style of an Excel Form Control Label

Anyone who has used a Form Control Label likely knows its limitations: you can't increase the font-size, -color, or style.  Below, you can see that these formatting items have been "grayed out" in the Font group on the Excel Ribbon.

To be sure, the Label control has received a lot of flack for these deficiencies.  A look through some Excel forums shows suggestions to use a TextBox shape or an ActiveX Label instead of the hapless Label control.It's a tragedy since the other form controls are lightweight and easy to use. Some forum posters even said Labels are best used to cover cells you don't want the user to click. So sad.   

But things are looking up.  I've since discovered you can take a boring Label Control from this...

...to this!


As it turns out, Labels take on the text-font and -size features of a referenced cell.  So, to make my label look this this, I simply linked it to a pre-formatted cell, like G2, below.

In fact, this is the same mechanism to link a Textbox shape to a cell.  The difference here is that a Textbox can take an unformatted cell and apply new formatting on the front-end, when it's displayed to the user.  Above, we see that the cell must first be formatted, then linked.  In fact, whatever formatting exists in the cell when you first make the link, the label will maintain this format until a new link is created.  For example, if I were to change G2 to a black color and a smaller font, the label would not show these new changes (however, it would change its text if I changed the value in G2 to something else).  So to change the Label's formatting -- even when it's linked to the same cell -- you'll need to click the label, click the formula bar, and retype the cell link.  

Admittedly, everyone else might have already figured this one out.  However, I'm still very excited.  Don't get me wrong, Textbox Shapes are great, but having too many could become expensive on your spreadsheet, especially if you are constantly updating the screen (how many redraws can your computer handle before things start to slow?).  Labels, as Form Controls, inherently carry less bloat and overhead.  They're perfect for dynamic dashboards.