W

orking with Custom Number Formats

If you’ve worked with Excel very long, you have probably gone through all of the number formats available to you and seen some custom ones that you wish you knew how to do.

BTW, just to be clear we’re talking about Format Cells –> Number –> Custom

I’m going to cover a couple of my current favorites just to get the conversation going but it would be great if others chimed in with their favorite custom number formats or with their questions!

In science we have scientific notation, which Excel handles but it’s ugly and somewhat mysterious. Ten Billion (10,000,000,000) would be displayed as 1.00+E10 (one times 10 to the tenth power). In business we often shorten big numbers too right? 10 billion, 300K, 15M(illion), etc and it would be helpful if we could shorten really big numbers in a similar fashion because we often don’t need the full degree of accuracy to be shown (it will still be there but we want to shorten the number to a reasonable bite-size). So here’s the trick that I learned recently…

We’re probably all familiar with this number format:

#,##0.00 - this is Excel’s default number format (in the US) and displays the number with commas separating thousands, millions etc. and two significant digits e.g. 10,000,000,000.00. Well we get a lot of business speak around here and don’t need that level of accuracy - in most cases - so why not have the same shorthand in the reports that we do in language?

By adding successive commas to the custom number format, we can “divide” the original number by a thousand.

For instance, take our 10,000,000,000 and apply the custom number format of #,##0.00,

Now our number is 10,000,000.00 thousands I can even indicate this by adding the following to my number format: #,##0.00,”T” (or “M” for you Romans) and the number is displayed as 10,000,000.00T

Two commas (#,##0.00,,”M”) reduces it to millions as in 10,000.00M

Three commas (#,##0.00,,,”B”) reduces to billion - 10.00B

Effectively, we’ve taken a very large number and reduced the space it takes to display it - higher data density. We’ve given up a bit of the precision, from a display perspective, but when working with very large numbers, the significant digits can be effectively reduced and still convey the important information.

My other example today relates to a pet-peeve of mine - consistency. When working with currency - $1,000.00 - one of the choice formats is to display negative values in red and in parenthesis. Apparently people who spend all their time in ledgers appreciate this format. However, there is no matching format for percentages and I’m always calculating percent change. So, I want my negative percent changes in revenue to match my negative revenue numbers.

Again, the default currency format for negative currency is

($1,000.00) which is the equivalent of the custom format $#,##0.00_);[Red]($#,##0.00)

Ok, first an admission I don’t know what some of the above means - specifically the _) just before the semicolon, if someone wants to explain that would be awesome. But I do know that everything before the semicolon deals with positive numbers and everything after it deals with negative numbers. So [Red] in brackets tells Excel to display the text in red and the parentheses surrounding #,##0.00 tells Excel to surround it in parentheses.

So it’s pretty easy to map the same formats to percentages. First we take the custom format equivalent of Excel’s standard percentage display: 0.00%. Notice that it doesn’t do anything with thousands or with negative, so we just plug it into the custom format from above thusly:

#,##0.00%_);[Red](#,##0.00%)

1,000.00% | (1,000.00%)

Effectively, all we did was add the percent sign (%) after the significant digit declaration and we now have percentages that will be similarly formatted to currency.

What about you? What’s your favorite number formatting trick in Excel?

So, it’s no secret that I’m a number and visualization geek. I mean you have been here before right?

Well, I also happen to have a 1st (British!) Edition translation of Homer’s Odyssey by T.E. Shaw . It’s just the most prized of the 4 or so translations I have (not to mention the Odyssey in its original ancient Greek). So, I’m a history geek too (Shhh, don’t tell anyone).

To my delight, I found this combination of visualization and Greek mythology. It’s the genaeology of the Greek Gods in an interactive Network Diagram. Ok, I think it’s a network diagram, but the folks at Spicynodes say it’s a "sophisticated radial mapping technology" - which doesn’t seem to match what I know so maybe it’s a hybrid of some kind or I’m just ignorant.

Here’s a screen grab of the starting point…

 It’s definitely fun to explore the Gods’ family tree. There are some quibbles that I could raise - more like constructive criticisms.

I wish that the descriptions at the bottom of the map were highlighted a little more - even if only briefly. I’m not a fan of the navigation wheel that appears on the central node when you roll over it so I’d rather have the bottom navigation be a little more visually important.

Even though it probably has little data value, it’d be a lot more fun if, when you searched for a God (or Goddess), the visualization ‘zoomed’ through the tree to your selection a la Google Earth. Frankly with a little thought into the speed of the zoom and some appropriate pausing, you could probably do a fairly decent job of showing a God’s direct line of descent.

Ok, on the mythology side … Greek mythology is complex and most Gods have at least one genesis story…it’s hard to show that in the visualization so the authors rightly include tidbits of that complexity in the descriptions. BUT, it bothers me that for Aphrodite they list Ouranos (The Sky) as her father. This is technically true as she sprang from the sea foam created when the -uh- remains of his castration were cast out over the sea, but ummm, Zeus did the work so shouldn’t he get at least half credit? (Nitpicky - I know, I know).

I also think that it would be great if the Web Exhibit folks would provide links to additional reading/details on each God/Goddess and ancient Greece in general for folks who stumble upon this (literally) and want to learn more. Hey, I started out on Bulfinch’s Mythology waaaaay back in the day.

What do you think? Is this a good visualization? A good use of visualization tools? What do you love or hate about it? What would you change?

Both images (the screenshot and the Temple of Zeus) are used with permission under creative commons licenses:

Attribution-Noncommercial-No Derivative Works 3.0 Unported - Web Exhibit

Attribution-Noncommercial 2.0 Generic - Feuillu

Ok, so about ten days ago I challenged my readers to solve the following charting problems with the Google API - Bullet Chart, Sparkline and Square Pie. I provided a sample bullet chart but asked if anyone could improve upon it. Unfortunately, only one brave soul, Jeff Donnici, took up the challenge and provided two solutions for Sparklines.

I am inviting Jeff to write his own discourse on how to create sparklines with the Google API, so Jeff, please email me (my email is at the bottom of the right-hand nav) with your description of how to create the sparklines.

First, let’s cover some basics of the API.

The API is pretty simple, it’s just a URL with a bunch of query parameters attached to define the chart and its elements. It can also be a bit annoying when you’re encoding the data sets and their associated information. My guess would be that to really get the power out of the API you would want some sort of programmatic interface to it as building the chart request ‘by hand’ can be laborious.

So, components that are used in these examples:

• Base API URL: http://chart.apis.google.com/chart?
• Required Elements
  • Chart Type Parameter: cht - Allowable Chart Types
  • Chart Size: chs - Chart Size in pixels [w]x[h] (chs=250×250)
  • Chart Data: chd - Chart data encoding methods (simple, text, extended)
• Optional Elements Used
• Chart Title: chtt - Text Title for the Chart
  • Chart Title Text Size: chts - Title font size and color
  • Bar Chart Height: chbh - Bar width
  • Chart Colors: chco - data series colors
  • Chart Background Fill: chf - Chart fill options

For me, let’s start with the Bullet Chart. The stumbling blocks here are that

1. The Google Chart API does not support combination charts, or at least not a bar chart / scatter plot combo, which is how I create Bullet Charts in Excel.
2. The API does not seem to support single data point properties. In other words, I can set the color and width properties for the stack (e.g. series) but not for data points in the stack.

If you remember the Excel example of the Bullet chart, you will recall that we have 3 data series (top, middle, bottom) each with 4 data points to create the basic visualization of the Bullet chart. We then combine into that chart a scatter plot to create the target line. Well this API doesn’t support coloring for a single data point (which is required in the Excel solution) so what does Google offer as a replacement? Well, with the background fill, we can go a linear stripe that will take up some percentage of the chart space - ok, I think I can work with that:

But first things first. Let’s define the chart type:

http://chart.apis.google.com/chart?cht=bhs

• cht=bhs tells Google that we are working with a horizontal bar chart

Next, let’s set the chart size

http://chart.apis.google.com/chart?cht=bhs&chs=250×40

• Remember, chs wants [width]x[height] in pixels and it’s required

While we’re at it, lets go ahead and set the bar width (or height if you prefer)

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25

• chbh lets us define that bar width and optionally the spacing between bars. In this example the gap is set to 25 to make sure that the chart fill area shows up around the bar itself.

Ok, for the last of the easy bits, let’s go ahead and set the Chart title

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%

• Note the plus (+) symbols in the Chart Title text, this tells the API to use a blank space when creating the title

On to the data. As noted above, there are 3 options for encoding data - simple, text, and extended. I haven’t really figured out why you would use simple because it’s basically a substitution scheme that feels more annoying than anything. However, in this example we are using text encoding because it allows us to use regular numbers with up to 1/10th precision.

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0

• when adding the chart data, you must first tell chd how you are encoding the data (chd=t) and then supply the data itself (:90.0)

The above gives us a basic bar chart:

Now we need to add the elements that will make this look like a Bullet chart and we’re going to get it all done with chart fill!

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0&chf=c,ls,0,000000,0.50,cccccc,0.25,000000,0.005,efefef,0.245

chf (chart fill) takes the following arguments:

• fill area: background (bg) or chart area (c), in the Bullet chart, we use the chart fill method
• fill type: solid (s), linear gradient (lg), and linear stripes (ls)
• angle: angle of the stripe between 0 (horizontal) and 90 (vertical)
• stripe color and width pairs: color hex value and length expressed as a percentage

If you look at the URL snippet above in bold, you will see that we have defined

• the background element as "chart fill" (c)
• the fill type as "linear stripe" (ls)
• the "angle" as zero
• The first section is black and extends to 25% of the length
• The second fill section is the same as the first
• the third section is also 25% but is a dark gray
• the fourth section, only 1/2 of 1 percent in width is black - It’s a trick, this is the target line!
• The last section is 24 & 1/2 percent of the length and is a very light gray

That’s it, now we have a functioning bullet chart

I sure wish that I could remove the axes lines, and add data labels to the bar and target line, but oh well.

Ok, I need to take a break as this is quite a long post. In part 2, we will walk through the pie chart solution (which is not that great),

I’m not quite sure how long I’ve been ignoring the Google Chart API but I saw it in a post the other day which reminded me, or at least gave me the inkling, that I had seen some discussion of it before.

Well, that was several days ago and I finally had some time to play with it and in a couple of hours created the following bullet chart:

So here’s the challange

1. Create a Better Bullet Chart than the Above
2. Create a Sparkline
3. Create a Square Pie Chart

Using the Google Chart API of course.

Once you’ve created your solution, post a link to it here in the comments. For the solutions that we like best, I’ll ask the author to provide a description and instructions to be published here in a follow up post.

Update: 12/15/2007

Jeff Donnici sends us two sparkline examples.

Simple:

With Banded Background:

Update 12/19/2007

Well with only Jeff taking up the challenge after 10 days, here’s the final solution: a so-called square-pie chart. Of the 3 types this is the most poorly supported by the Google API mostly because on bar charts you cannot control the scale of the axes.

Update Feb 29, 2008

Geoff offers us this austere Bullet Chart solution:

And this slightly "dressed up" version:

In his keynote at the inaugural SEMPhonic XChange Conference, Eric T. Peterson said he was going to be controversial, that he wanted to stimulate the discussion. That he was tired of the ‘me too’ and ‘that’s exactly what I think’ commentary. Fast-forward a bit and Eric goes ahead and lobs a controversial bomb my way…

"Reporting Is Evil"

You might as well say Seeing is evil.

Seeing, after all, is just your eyes reporting received electromagnetic stimuli to your brain.

I can understand the sentiment behind it - I really can. It comes from a frustration with organizations and people who think web analytics is nothing more than regurgitation of data. A frustration with people and organizations that don’t "get it" and can’t "grok it". The frustration comes from web analysts within those organizations (I’ve felt it, believe me), from consultants trying to help companies get to the next level and vendors trying to sell their wares.

The problem is that reporting is NOT evil. It is vital to the web analytics process.

Analysis, web analysis, is a process - or at least it should be.

Analyses, like any process, have outcomes. The outcomes might be changes to the website, changes to advertising creative, changes in SEM strategies, changes to the web analytics implementation itself and many more that I’m not thinking of at the moment. One of the over-arching deliverables of a process is communicating its results. If the results of the web analytics process are not communicated, then no outcomes are possible. How can recommended changes to the website be made if they aren’t communicated and substantiated with an analysis of the data?

Yep, that’s my way of saying that reporting is an expected outcome of the analytic process. A process that doesn’t communicate its results is a failed process. Thus, if we excise reporting from web analytics because it is evil, we are left with a process that can give us no insight because there is no way to extract information from it. What is the point of going through the process at that point? We might as well use ‘Pin the Tail on the Donkey’ in all its blindfolded glory to make a decisions.

There are many definitions of reporting, but in this context I think that there are two that are most important:

1. Reporting is a required outcome of the analytics process
2. Reporting is a communication tool

The frustration that leads people, Eric included, into thinking and saying that reporting is evil derives from organizations treating reporting as the whole analytics process.

So, if you are spending time, money and resources on web analytics and all you are doing is reporting the data, then you probably feel like you’re not getting nearly enough value out of your investment.

Heck, without reporting there is no feedback loop in analytics and then your just running one blind test after another - throwing spaghetti at the wall as it were - until something sticks.

Reporting without analysis is just a regurgitation of facts. Analysis without reporting is impossible because then there is no mechanism for creating the feedback loop within the process.