Everyone! We've moved to WordPress.

Visualizing.org: Garbage In, Garbage Out

0
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:


In theory, plotting a country's relevant indicators should give some visual cues and insights. However, their three-dimensional forms obscure much of this data. There is no scale and no way to tell while looking at each figure if a system is balanced, or even how much gain is too much gain. Moreover, so much is represented that we must keep deferring to the legend to figure out exactly what we're looking at.

But there are larger problems that seriously undermine the team's work.  First, this paragraph I excerpted from their write up:
By abstracting data into these virtual objects, they can then be tested for physical properties such as center of gravity, balance point, mass, inertia, aerodynamics, and any number of precise simulation results...The center of gravity of a cube gives us indications as to where a country's development is distorted and which direction the country needs to move in order to achieve balance. Inertia allows us to test for direction of past and future movement - or lack thereof. Aerodynamics would reveal holes and outliers in a country's cube, indicative of a crisis or a boom/bust capitalist cycle.
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,
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.
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

0

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

11
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!



How?

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.  


Turn an Excel Spreadsheet into a Bitmap Image

3
So I've created a spreadsheet program that will import a selected bitmap and use Excel as a canvas to display it.  Each cell is a pixel.  Here's a picture of president Obama in 8-bit glory:


And here's my current March Madness bracket in stunning 24-bit:

You can download the file below, and instructions are included on the Instructions tab.  Don't worry, it's not hard to use.  If you can use MSPAINT, you'll be making your very own Excel art in no time!

For now, the code is locked, but I might make it available later after I'm sure it's error free. Speaking of which, there are a few problems you may (but hopefully won't) run into:

1.  The picture shows up discolored and slanted.  While I'm fairly certain that I've fixed this issue, I admit that I am by no means perfect.  When bitmaps are saved, they are "padded" in the file to make their height evenly divisible by four.  The program should compensate for this padding, but if it doesn't -- again, I'm fairly certain this issue is resolved -- just resize the bitmap it to the nearest number evenly divisible by four.  (That number is at most three digits away.)

2.  Too many Different Cell Formats error.  Sometimes you'll get this annoying popup.  If that happens, try zooming in on the canvas screen first and then reuploading your file.  If that doesn't work, either crop the original image or resize to a smaller one.  Or, if you are uploading a 24-bit bitmap, consider resaving as an 8-bit and then reuploading.

If you find other issues, please, let me know.  It would be great help.

Also, this is best used with Excel 2007 and above.  It will work in 2003, but remember that 2003 has a column size limitation, which may cause in an error for files that are too large.  If you are concerned, smaller is better: resize or choose to save in the 8-bit format.  When you are done, remember to save as in the Excel 2007 file type (check to see if you are in compatibility mode) so that you get all the columns required to display your most productive spreadsheet, ever.

I'm releasing this now because I've been sitting on it for way, way too long.  I was hoping I'd come up with some real, professional use for it, but I haven't thought of anything yet.  For a moment, I was trying to apply a Hough Transform to the bitmaps to see if I could take images and turn them into cool free form shape, but this proved too taxing for Excel.  I also made an attempt at Eigenfaces which ended ridiculously.  So if anyone out there has some good ideas, please email me and we'll work together.

Lastly, if you like what you see and choose to host a spreadsheet made with this program (or screen capture of one) on your blog or website, I'd be so grateful if you mentioned this blog!  Have fun.

File
Bitmap to Excel.xlsm



Numbering Grouped Data for Pivot Tables

3
We've all had some experience with Excel's automatic numbering.  For example, if you have a simple list and you would like to add consecutive numbers to a column next to your data, you'd simply select the cells containing your first two numbers and then drag accordingly.  Take a look at the sheet below to see an example.


But real-world lists aren't always so simple.  And for certain data, a simple consecutive list just won't do.  Sometimes we receive spreadsheets that have some type of intrinsic grouping but no unique group identifier. The challenge is that we would really like to apply Pivot Tables to this data, but we can't do anything until there exists some type of group identifier.

Problem #1: Items grouped without collation
Take a look at the datasheet below from my nonexistent accounting information system.


Because I'm smart(ish), I know that a new group begins every third row of data.  The first three rows of data (Excel rows 2,3, and 4) represent store 1, the next three store 2, and so forth.  I would like to add an additional group_id column such that each grouping is numbered consecutively.  Something like this:


How?  I use this formula, 

=INT((ROW()-2)/3) + 1

then fill downward.  This works by applying integer division in the amount of each item in a group to the current row. So, for row 3, we have 

=INT((ROW()-2)/3) -> =INT((3-2)/3) - > =INT(1/3) -> = INT(0.33) = 0.  

(If it's been a while since your last algebra class, just think of it as dividing and then "rounding down" the result.)

I add one ("+ 1") at the end of the formula so that my grouping doesn't start at zero.  That's optional.

Notice though, that I subtract 2 because my data starts on row 2.  If I had started on row 1, I would likewise subtract 1; for row 3, subtract 3.  Here's a cheat sheet:
For Items Grouped Without Collation
=INT((ROW()-First row of data on spreadsheet) / # Items per Group)[+1] optional  

Problem #2: Items grouped with collation


In the following spreadsheet, we've culled information from many different spreadsheets maintained by many different people.


The problem is that each spreadsheet administrator used a different naming convention for the same account (see the highlighted accounts).  And take a look at the final Retained Earnings and note that it is labeled "  RE."  Those extra spaces can creep into the spreadsheet and easily go unnoticed.  What a nightmare.

But wait, our list has some semblance of order: accounts can be grouped every sixth row. We just need to group each item, one through five, until the end of the list.  Like so,


How?  With this formula,

=MOD(ROW() - 2,5) + 1

This should look familiar to the one above.  But instead of using integer division, we're now using modulo division; that is, we're interested in the remainder.  Take row 3

=MOD(ROW()-2, 5)+1 ->  =MOD(3-2, 5)+1  - >  =MOD(1, 5)+1  -> = 1+1 =2.

Here's the cheat sheet:

For Items Grouped With Collation
=MOD((ROW()-First row of data on spreadsheet,# Items per Group)[+1] optional  


Final Thoughts

1.  Once you've created your new group_id column, it's a good idea to select your new work, copy, and then paste as values.  If the groupings aren't going to change later, there's no reason to keep it as a formula.  Remember, fewer formulas means better Excel performance -- especially if you plan to use a Pivot Table later.

2.  If you're not into the numbering scheme, create a lookup table that maps the numbers to a proper name.  Create another column at the front of your data and use a lookup method, like Index, to map the correct names.  Then do a copy/paste values.

3. If you have a list scheme you use quite often, you can actually save it as a custom fill series and then use it later.

Remember:

For Items Grouped Without Collation
=INT((ROW()-First row of data on spreadsheet) / # Items per Group)[+1] optional  



For Items Grouped With Collation
=MOD((ROW()-First row of data on spreadsheet,# Items per Group)[+1] optional