Tornado Diagram Add-In (2K +)

Topic

New Reply

Excel does not offer a very good tornado diagram – a failing I thought I could quite happily ignore. Until one of our managers decided that tornado diagrams were the best possible way to convey information. Okay – they do work pretty well, but they are (in my experience) a bit of a pain in the neck to create, with lots of fiddling with repetitive formulas and the like. I decided that this was a good subject for automation, so I wrote the attached add-in. I have included a “test” file that you can use to create a tornado chart – it is cluttered with explanations in text boxes – I hope they help more than they hinder…

It would be very helpful to get comments from people here – what works, what doesn’t, and what should be added. Coding issues are also up for discussion (even moreso) – I intended this as a learning opportunity for myself!

The Add-In allows you to:

• Create the chart
• Reverse the “direction” of colours – so if “good” outcomes are always blue you can accomodate both Costs (low is better, hence blue) and profits (higher is better…)
• Change the colours applied to the chart (because I am a finance guy, not a graphic designer!)
• Save or restore a default colour palette (this and the last option require that you click on the “select colours” button…)
  [/list]I developed the add-in in XL2000, and I have tested it in XL2003. Other than the toolbar being ugly in ’03 it works fine there. I don’t have XL-XP – if there are specific problems there I may end up asking for help.

  To run the Tornado Tool

  • Install like any regular add-in – on installation, it will add a “Tornado Chart” option to your tools menu
  • when you have your data ready (or before) click Tools | Tornado Chart – it will bring up a utility toolbar
  • with the cursor in the data range (this is very important) click on “create Tornado Chart – from there is should be fairly evident what to do. It will overwrite data below the data range (a warning / exception handling for that is on the list for version 1.2)
    [/list]Thanks for your time

Reply | Quote

Replies

Nice add-in, but there are two problems using it on a non-English language system:

(1) The “Tools” menu has a different name in other languages. Perhaps you can use its ID: 30007.

(2) In the GetChartRange function (in the MUDFs module), you retrieve the formula for each series:

Sf = cht.SeriesCollection(1).Formula

The Formula property uses US format, yet you parse it using ListSep = Application.International(xlListSeparator). This leads to incorrect results, but since you have On Error Resume Next, the function fails silently. Since Formula is in US format, you can simply use ListSep = “,” here.

After correcting these two problems, it worked well when I tested it in the Dutch language version of Excel 2002 (XP).

Reply | Quote

Thanks Hans – I will make those two changes.

Dean

Reply | Quote

Very nice! I wrote a regular VBA version four years ago on request from another team where I work and it’s used a lot. I did the same thing you did with the reverse colors (can’t assume + on right, – on left). I’ll definitely give this a run through. I like the descriptive titles, my version doesn’ t have that. I can appreciate the work that went into this having done it myself. I also set the ‘mean’ to be 0 not configurable like yours so I calculate the absolute values and plot the deltas. I like your option better. Sticking to 8 variables as you did is probably good idea too; my version is any number of variables.

One advantage of using an add-in is that there are problems using the inputbox dialog to fetch the cell address across workbooks. My version requires that the user opens their workbook with the data to be analyzed and open my Tornado tool workbook. Then from the Tornado tool you point to the cells that contain the low/base/high values and identify their names (either manually or by clicking the cell), and finally identify the ‘result’ cell (a formula that is updated when any of the inputs are changed typically NPV, payback, etc.). I find that it’s very awkward to do this across workbooks as the Inputbox only works with a mouse and I tend to use keyboard shortcuts to get between workbooks, sheets, cells, etc.

Thnx, Deb

Reply | Quote

[indent]

---

I like the descriptive titles, my version doesn’ t have that.

---

[/indent]

I borrowed a bit of code (or at least, a lot of understanding of how chart labels work) from Rob Bovey’s XY Chart Labeller for that! Note that the labels remain live – changes will be updated on the chart – and can be formatted with line breaks so they fit better on the chart.

Please try it out and let me know what you think could be improved – feel free to have the other team that uses your version whack away at it as well.

Reply | Quote

I have fixed the two problems Hans noted, and I have also added the facility to adjust the shading on the colour bars. I will be very interested to hear your comments.

Reply | Quote

Hello Dean,

The add-in now works well on my Dutch language system

One small suggestion: when creating a new tornado chart, you don’t check whether the selected range/current range is suitable. If not, the user gets an End/Debug/Help error dialog, since you haven’t added error handling. It would be nice if you could check the selected range/current range, and/or error handling.

Reply | Quote

[indent]

---

… now works well on my Dutch language system.

---

[/indent] Glad to hear it!

[indent]

---

…you don’t check whether the selected range/current range is suitable…

---

[/indent] But error handling is no fun… Actually, Hans, that is a very good suggestion; thank you!

I have been thinking that I should add error handling for the situation where the user is going to overwrite existing data – first; it will aggravate them, especially since the VBA will clear the undo stack – (see Jan Karel’s recent posts at Daily Dose of Excel – but I am not going to go to that length), and second; it may screw up the creation of the chart. I should add the error condition of a poor selection of starting point, as well.

At the moment the logic assumes that if a single cell is selected then the user intends to chart all of the ‘current range’ – I think that is a pretty reasonable assumption, although it is not infallible. If there is more than one cell selected then only the selected range will be charted – the idea is to match the way Excel creates charts, but the matching isn’t exact since the user selects the data points, not the comments. I am concerned that a confirmation dialogue will be aggravating, and had considered a confirmation with a “don’t show this warning again” option – I suppose the response is usually recorded in the registry, but since I am already storing information in the Add-In worksheet, I could save it there.

Any thoughts or comments would be welcome.

Reply | Quote

The idea to use the current range if the user has selected a single cell, and the selected range otherwise, is excellent. But you could build in a simple test – for example, if the selection is a single cell, and ActiveCell.CurrentRegion is also a single cell, it will clearly be impossible to create a chart.

Reply | Quote

Okay – here is the third installment of the Tornado Chart Maker utility. In this iteration I have:

• Completed the implementation of Hans’ suggestion not to use “Tools” to identify the menu I am adding an item to, but use the ToolBar ID instead, so as not to screw up in foreign language editions. My first time ’round I forgot to make the change to the toolbar removal logic (smooth, eh?)
• Added error handling if the user is about to overwrite existing data – the routine informs him/her of the problem, tells him to try again, then exits
• Added some error handling for unsuitable selections of starting range or the median value range
  [/list]Comments or suggestions are welcome. The zip file also includes a sample data file – more extensive than would usually be found in the wild.

Reply | Quote

Thanks! The toolbar is now removed correctly in my Dutch version of Excel.

Reply | Quote