For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #70

The alert system in this chart has been set up to ignore random variations and to raise an alert only when performance is statistically exceptional.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

The alert system in this chart has been set up to ignore random variations and to raise an alert only when performance exceeds random variations. Too many traffic lights and other alerting systems merely highlight random variations.

By doing so, they not only waste the reader’s limited reading time and attention span, they can hide truly alarming results in a clutter of false warnings.

This Swipe File started as a way to fix that problem. It displays an alert for a line chart when current performance is beyond an area of random variation.

The alert is the blue circle in the top-left and bottom-right figures.

But then I thought it would be useful to show the area of random variation itself. This I defined as the standard error of the estimate, which is like a standard deviation around a trend line. So I drew the trend line, the blue range of the standard error, and its upper and lower boundaries.

Because I couldn’t decide which elements most users would want, I added all of them to the Swipe File. This allows you to delete unneeded elements, including the alert, as shown in the other three figures.

Note that the text in these figures is called “Greeking;” but it’s actually meaningless Latin. And the default data consists of random numbers, so the display changes each time Excel calculates. You replace these items with your own text and numbers, of course.

Usage Ideas

Adding a standard-error band, as shown in the bottom-left figure, could improve many line charts. Not only would it highlight the trend, it would provide an intrinsic alert system.

(Continued in the documentation.)

Order Details

Excel Versions:

Excel 2007 and after, for either Windows or Mac

Availability:

Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.

Guarantee:

One full year, unconditional.

Currency:

All prices are in US Dollar currency.

Licensing:

Two copies: One for work plus one for home.

Credit Cards:

Credit Cards
 

Only $16.95

New Excel