The "Infozak" Pandemic

The word "Muzak" has existed since at least 1954 when it was registered as a trademark in the USA. In a similar manner to the way the name "Hoover" became a generic word for vacuum cleaner, the word "muzak" became generalised too.

Muzak now variously refers to the range of background music played in shops, waiting rooms, during telephone calls when on hold, during breaks in television programme transmission, and so on. The purpose of muzak seems to be to create a calming effect, to create a desired ambience or to fill in problematic gaps

I have coined the term "infozak" to refer to the information equivalent of muzak. The purpose of infozak seems to be to create a calming effect, to create a desired ambience or to fill in problematic gaps.

When presented with a long report replete with tables of data and eye-catching graphs, the prospective reader may immediately gain a sense of calm. Whatever the report is recommending is bound to be the result of a detailed and robust analysis. We can rest easy.

Those involved in the production of the report may know otherwise. How often are reports written "back to front" in the sense that the conclusions are arrived at first and the data and graphs are added later?  The information component of such reports may be selected carefully (i.e. with conscious bias) to attempt to give credibility to proposals arrived at by other methods. Worse still, and common, the information component may be largely decorative, arrived at out of a sense that this type of document "needs a few graphs".

Once you understand the concept of "infozak" you start to recognise it everywhere.

The art of misinformation using "statistics" is at least as old as the profession  of politics. Disraeli is famously attributed as saying "There are lies, damn lies and statistics". Ironically, this attribution itself may not be true, but the activities of modern day political campaigning produce no shortage of examples. Fortunately we are largely tuned in to expect manipulation of information in such arenas.

It is the accidental production of infozak that is the greater concern. Due to the increasing ease of production and dissemination, infozak is everywhere. Bill Gates must take his share of responsibility for the infozak pandemic. 

Many years ago, when my oldest daughter was four, she decided to entertain herself (without authorisation) using Micosoft Access which she had discovered on my computer. In a matter of minutes she had set up a CD collection database system. At the time, as far as I know,  she had received no training in database development and design. She was simply making largely arbitrary choices from the easy-to-use "wizard" in Mr.Gates's software. She produced something about which she had little understanding but which was very pleasing to her.

Microsoft Excel is equally easy to use. Many grown-ups can produce, in a matter of minutes, a raft of visually pleasing tables and graphs from any given selection of unprocessed data. It is possible to produce a time series bar chart of monthly totals in about 80 seconds. With one more click a trend line can be added. Ten more seconds to embed or paste the results into a Word document or Powerpoint presentation. From raw data to information wallpaper in less than two minutes. Beautiful!  

The trouble is that a cursory inspection may not easily distinguish infozak from true information. In fact infozak may look more like information than information does itself. Good information will be seeking to communicate. It will therefore make sparing use of graphical devices. The message may stand out but the medium should blend into the background. Infozak does the reverse. There may well be no clear message at all. The intention is to impress. Graphical devices are used to the full. It looks fantastic. It conveys almost no meaning.

What creates the pandemic is the highly contagious nature of infozak. Once normally rational people are exposed to even a small piece of beatiful infozak they suddenly develop irresistible cravings to start producing infozak themselves. It quickly spreads everywhere. There seems to be no cure.

References

Disraeli quotation

https://en.wikipedia.org/wiki/Lies,_damned_lies,_and_statistics

Excel: Using cell comments to provide "drill-down" details

A previous article (link) adapted a sample panel from the BBC website to look at options for creating arrows in Excel to indicate direction of change:

The article covered different ways of creating arrows in Excel and the need to define tolerances to create thresholds for changing arrows. The example Excel rendering introduced additional columns to hold both the previous values and the tolerances. These would be hidden in a real, deployed dashboard.

Some Design Considerations

In terms of dashboard design, the BBC-style panel could be simplified. There is a hierarchy within the information. From most the important downwards, something like:

• Markets  i.e. what is the whole panel about
• Each Index and whether up or down (the most important information)
• The latest value of each index (it could be argued that some people would find % change more important than the actual value) 
• The % change and/or previous value of each index (in conjunction with present value, these are two versions of the same thing, although % change conveys more information quickly)
• Full details of the activity of each index over a period of time  (a 'drill down' to the full details for each index can be provided in Excel - as well as on a website - as a hyperlink to a second page of details). 

Re-arranging the panel to reflect the hierarchy:

This simplified design makes the most important information readable at a glance and also relegates the calculated % change data from the panel. 

Cell Comments as a method for providing drill-down

One way to retain access to details from within the panel (without the need to jump to a different page via a hyperlink) is to use cell comments:

Comments can be added to cells manually but this would be extremely time consuming to set up and maintain, and prone to error. To be of real use, the comments need to be generated automatically.

The Worksheet SelectionChange event provides a method for triggering updates. The example above uses the  .AddComment method in VBA to create the comments box itself. The text within the comments box needs to be formatted to control the number of decimal places. Separate lines within the box are created using 'Chr(10)' in the text string.

Cells with a comment are designated in Excel by a small red triangle in the top right hand corner. Microsoft appears to determined to make this feature unalterable. It cannot be adjusted through options, property settings or by using VBA. Workarounds are discussed variously on Excel forums: all seem to rely on creating a differently coloured triangle over the top of the red one. The approach taken here, to avoid having a dashboard covered in unsightly little red triangles, is to wait until the comments are needed before creating them and then delete them again afterwards, straight away.

The VBA code used to generate the example above is as follows:

The details are collated from hidden columns:

• Column G: contains the percentage change
• Column H: contains the previous value
• Column K: contains a brief description of what the index is (for illustrative purposes)

The .visible property is set to true in order to open up the comments box.

Clicking on another cell within the target range in column E will trigger a three stage update:

• The last generated  comment will be deleted
• A new comment box will be constructed with the requisite information collated from the values for the same row in Columns G,H and K. 
• The new box will be opened up (made Visible)

If a cell outside of the target range in column E is clicked on, the last generated  comment will be deleted but no new comment box will be created. All comments will have been removed and there will be no red triangles left behind.

The approach has some limitations: the size and style of the comments box is hard to control (it is not possible to set 'autosize' to true on creation); selecting more than one cell can cause strange results.


Other options

An alternative to using cell comments is to create a text box. This works in a similar way to cell comments. It introduces additional technical challenges, most notably the need to determine the positioning of the box.



References

The VBA used here is a combination of ideas gleaned from various Excel user forums and some original experimentation. Because a lot of material recirculates in forums, it is difficult to trace original ideas reliably back to their creators to give due credit. The following are just a few of those referred to:

Shapes.AddTextbox Method (Excel). Excel 2013 Developer Reference. Microsoft. https://msdn.microsoft.com/en-us/library/office/ff838832.aspx

VBA Tips - Find Out If A Cell Is Within A Range. ExcelExperts.com.21 May, 2009. http://excelexperts.com/VBA-Tips-Find-Out-If-A-Cell-Is-Within-A-Range

Using the Intersect Method in Excel VBA. 

http://www.ozgrid.com/VBA/vba-intersect.htm

The Complete Guide to Ranges and Cells in Excel VBA by Paul Kelly.2nd January 2015. Excel Macro Mastery. 

http://excelmacromastery.com/Blog/?p=165

How to change comment indicator color in Excel?. ExtendOffice.

http://www.extendoffice.com/documents/excel/2253-excel-change-comment-indicator-color.html

Excel: Using arrows to indicate direction of change in performance

The use of arrows to represent relative change is a common device in performance dashboards and reports. 

Dashboard Example

The following example, from the Business Section of the BBC website (link), shows the latest positions of the four main market indexes:

This representation works well in that it shows at a glance the key information the reader is looking for i.e. whether the main market indexes are up or down

The red arrow next to the current values shows that, on this occasion,  all the main indexes are down. The use of strong, coloured graphics makes this the most prominent information on the panel. This helps achieve the principal objective of dashboard design - to make sure that the most important information stands out and can be read at a glance. 

Alongside the arrow, the actual amount of the drop is also given (in this case as a percentage) also emphasised in colour. Further information is available (on the BBC website) as a drill down accessed by clicking a link on each index name. So there are in effect several levels of information available depending upon how far the reader wants to go. 

Perhaps the most questionable element of the BBC's design is the use of the red coloured ">" symbols next to the names of the market indexes. This may be an example of where the desire to "look good" in a graphic design sense competes with the primary objective of conveying meaning.

Creating direction arrows in Excel

There are two main aspects to determine when using Excel:

• the graphic design features
• the calculation of what actually is up, down or the same

Looking at graphics first, there are various ways to simulate the design features in the BBC website example using Microsoft Excel: 

Method One

This approach uses Excel's Conditional Formatting Icon Sets (available from Excel 2007 onwards):

In the illustration above (created using Excel 2013) the 'previous values' of the top two indexes have been adjusted to provide contrasting symbols. Only three main columns are required for the panel itself. An additional column is needed somewhere (it could be a hidden column or referenced from a separate sheet)  to hold the previous values. 

All the work is done in the cells in column E: A simple formula

=(D3-H3)/H3 

calculates the percentage change. Two overlain sets of conditional formatting rules add the icons and the font colours. Icon sets provide the arrows automatically in the same cell as the calculated value. A second set of (more familiar) conditional formatting rules change the font colour.

Advantages of this method are its simplicity and that the features needed are all easily available within Excel core functionality. 

Disadvantages are that older versions of Excel will not be able to use it; that there is inflexibility over the positioning of the icons; the colour strength and definition of the icons are relatively weak; the sizing of the data and arrows is coupled; the shapes do not enlarge very well; changes to thresholds need to be made by editing the details in rule sets, which can be difficult to keep track of on a larger spreadsheet.

The method can be adapted to overcome some of the disadvantages. For example to "uncouple" the arrows from their values for formatting purposes: column E can be duplicated setting  "switch off display values" in one set of cells and removing icon set formatting from the other.

Method Two

This uses specially chosen fonts to create the arrows. In the illustration below, the arrows are generated from the rendering of the characters  p, q w into the Wingdings 3 font:

The arrows are generated from a nested IF statement

=IF((D3-I3>0),"p",IF((D3-I3)<0,"q","w"))

with the resultant character 'translated' by formatting these particular cells to Wingdings 3. This method requires an additional column to create the panel. It has some advantages over the icon set method: It allows much stronger and sharper arrows which scale better; it allows much more flexibility in positioning; it allows the sizing of the arrows and percentage change figures to be treated independently; it opens up wider choice of symbols used.

Disadvantages are that the special font used needs to be installed on the reader's computer for the method to work (Wingdings 3 is part of the standard Windows build so is a fairly safe choice);  the method relies on character to symbol mappings which do not read intuitively in formulas . Also, this particular rendering of the method does not yet include tolerances, so additional work is needed to incorporate these.

Beyond symbols from the BBC example, there are many other options using this method. The following panel highlights a few:

Other approaches

Excel Shapes provide a number of potentially useful options. For example

These can be added and edited manually or controlled using VBA code. Manual editing makes updating tedious and prone to mistakes. VBA provides a fascinating intellectual challenge from a programming perspective (see References below) but then introduces further issues in how the spreadsheets are distributed and used. The variation in functionality through successive versions of Excel introduces further constraints i.e. it cannot be certain that what works on the originating computer will run or look the same for all end users.

Graphic image files, sourced from outside of Excel,  can theoretically allow for an infinite variety of shapes to be used, for example: 

The disadvantages of graphic image files are similar to those for Excel Shapes. 

Use of either Excel shapes or graphic image files would involve a much greater overhead in setting up and maintaining the spreadsheets compared with the font or icon set methods outlined above. This can only be justified if the desired graphic symbols really cannot be obtained any other way. Before embarking on a VBA heavy spreadsheet, it would be wise to consider honestly whether the desire to impress is eclipsing the desire to communicate. Similar pitfalls can occur with other visual choices in dashboard design.


Calculating what actually is up, down or the same

A change, say,  from 17,569 to 17,571 is, in strict mathematical terms, an increase of 2. The question, in dashboard design terms, is whether this should be considered as an increase or as no significant change. The answer to this will depend upon the specific context. This can then be expressed in calculations through assigning a "tolerance" value. The following panel illustrates how various value changes, all of which have exactly the same arithmetic difference, are interpreted at variety of different tolerances: 

Incorporating tolerances into dashboards

Tolerances can be added to dashboards and reports in a variety of ways. It cannot be assumed that one tolerance value will apply sensibly to an entire dashboard. In fact,  this is unlikely to be the case on many occasions. It is best to allow for each measure on the dashboard to have its own tolerance. 

In the next example, the font method described above has been adapted to include tolerances. An additional column is used to hold the tolerance values:  

The 'nested IF' formula from Method Two needs to be adapted in column E:

=IF((D3>(I3+(I3*H3))),"p",IF(D3<(I3-(I3*H3)),"q","w"))

Additionally, the conditional formatting of column F needs to be altered to keep it in step with column E:

Depending on how it is intended to be used, there is a further option to include the tolerances on the dashboard itself so the user can adjust the values.


References

Excel Dashboards & Reports (2nd Edition) by John Walkenbach and Michael Alexander. May 2013, Wiley.

Mastering Excel: Conditional Formatting by Mark Moore  Kindle Edition. May 2014 (link)

Icon Sets. Excel Easy
http://www.excel-easy.com/examples/icon-sets.html

Programming Excel 2007 and Excel 2010 AutoShapes with VBA by Nicholas Hebb. Peltier Tech Blog.  12th January 2010.

http://peltiertech.com/programming-excel-2007-2010-autoshapes-with-vba/

Working with shapes in VBA by Andy Brown. Wise Owl Training. 25 January 2014. http://www.wiseowl.co.uk/blog/s394/shapes.htm