Monday, June 1, 2009

Different metrics

One of my hobbies is playing with MS Excel. I do this at home, at my assignment and in the past I learned it on

The power of MS Excel is collecting data and transform it into information. Initially I tried to create a dashboard which I can use in every project. Unfortunately this was barely possible. Each time, the challenge was to translate the provided data in such a way it represents the same overview of tables and charts. Also requests for information from management differed each time.

I noticed there is a difference between information I need to control the test process and information management need to get informed.

As a project is dynamic and also data is dynamic during the process I start always easy and make the information I can provide also dynamic.

Information for me would be:
- # issues found
- # issues found in combination with system component
- # number test cases /issues open for test
- # test cases /issues in combination with location (what is the status and who should act
- percentage ready (when possible in relation with time left)

Often the difficulty is that information I need is stored in other applications. When possible I download them. Disadvantage can be here if the structure of information is changing or objects are changing in names it become hard to compare. Therefore I start always easy and let the dashboard grow when necessary, or reduce when possible.

Personally I like tables with data, because it enables you to discuss how to understand the information. This is often a complex process, not only you have to explain what information is there, you also have to explain that the data which is shown is correct. Often you can draw several different conclusions out of those tables.

The pitfall here is that this is sometimes hard to explain to management because the like charts. The risk of charts is the discussion behind cannot be shown, information might be missing, people can be mislead. In certain cases I like to keep it simple and fancy.

Here some examples I used over the years:
- the progress meter, can be used per process and for a total. For detailed explanation see: Peltier's Speedometer

Another chart I use is a traffic light to show how the testers feel about the quality, this is often supported by written remarks. Although it is not objective, you still show the feeling and possible threats.

Another daily chart I often use is to see the location of issues. This is a chart which changes on daily basis which I use in combination with available time

A chart managers often like is a chart planned vs actual. This is often hard to get as the data for planned numbers of test cases depends on the availability of testers and also if they are dedicated available for testing. Another remark can be the origin of planned figures. As planned is different then intended.

One of the powerful functions in MS Excel to obtain data unstructured sources is SUMPRODUCT. This one I use often above pivot table, as they avoid growth of workbooks with MB's. Using them wise full, it also enables you to change when data sources are changing.
My advice is start simple and avoid using standard templates as each project will be different. It cost more time to change a template based on provided data and information then start from scratch. Working this way you avoid that unnecessary data has to be maintained and management is informed with non-required information.


  1. I've removed the speedometer chart from my website, for two reasons. First, I was receiving too many emails from people who didn't have enough grasp of simple algebra to make the chart. Second and most important, the speedometer style chart is really a very poor means of communicating information.

    The problem with graphical dashboards is the name "dashboard". Too many people equate dashboard with cockpit. They think their business dashboard should emulate the cockpit controls of a jet fighter, with dials and gauges and lights and bells and whistles.

    This is absolutely wrong. A gauge chart first is ineffective because people are less accurate when estimating angular position than linear position. Second, it presents only one point in time, where a simple line chart could easily show performance stretching back a number of weeks, months, or years.

    By the same token, the stoplight only shows Good-Fair-Bad. It doesn't say how good or bad, it doesn't say what defines good or bad, and it doesn't show which direction the parameter is going: Good and Getting Better, or Good but Declining.

    I talk about dashboards on my blog.

  2. Hello Jon,
    Thanks for replying, I can Imagine you removed the speedometer chart. I agree with you that it somehow poor in communication, in my profession it gives a good first impression how we stand. I mainly use it to start discussions and explain the details and figures behind the chart. The same is with the traffic light. It is not representing objective values, as stated: I use this one just for subjective opinion how the tester feels it is going, and if they think they have stuff under control.

    Your correct about the mistakes people make between dashboard and cockpit. I know I also do on occasional basis.

    Thanks for informing me and pointing out to your site. I use it on frequently basis to show others what can be done with MS Excel and charts. You are a good example how to provide information and to teach people.