# Misleading charts and data



## Jon von der Heyden (Feb 2, 2009)

Hello board

I am preparing a presentation on misleading charts and data (with the purpose that it be easily identified).  The charts subject is relatively easy, especially since Bill has so many of the 'little lies' documented here: <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CJONVON%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><style> <!--  /* Style Definitions */  p.MsoNormal, li.MsoNormal, div.MsoNormal 	{mso-style-parent:""; 	margin:0cm; 	margin-bottom:.0001pt; 	mso-pagination:widow-orphan; 	font-size:12.0pt; 	font-family:"Times New Roman"; 	mso-fareast-font-family:"Times New Roman"; 	mso-ansi-language:EN-GB;} a:link, span.MsoHyperlink 	{color:blue; 	text-decoration:underline; 	text-underline:single;} a:visited, span.MsoHyperlinkFollowed 	{colorurple; 	text-decoration:underline; 	text-underline:single;} @page Section1 	{size:612.0pt 792.0pt; 	margin:72.0pt 90.0pt 72.0pt 90.0pt; 	mso-header-margin:36.0pt; 	mso-footer-margin:36.0pt; 	mso-paper-source:0;} div.Section1 	{page:Section1;} --> </style><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable 	{mso-style-name:"Table Normal"; 	mso-tstyle-rowband-size:0; 	mso-tstyle-colband-size:0; 	mso-style-noshow:yes; 	mso-style-parent:""; 	mso-padding-alt:0cm 5.4pt 0cm 5.4pt; 	mso-para-margin:0cm; 	mso-para-margin-bottom:.0001pt; 	mso-pagination:widow-orphan; 	font-size:10.0pt; 	font-family:"Times New Roman"; 	mso-ansi-language:#0400; 	mso-fareast-language:#0400; 	mso-bidi-language:#0400;} </style> <![endif]-->http://www.mrexcel.com/tip142.shtml.  And there are all sorts of other examples on the web.

I am eager to gather as many examples of 'bad practise' as possible.  I have many chart examples but I am especially looking for more examples on the 'data' subject.  I am preparing a list of tips to make sure that data can be easily interpreted.

Example - insist that number formats show zero as hyphen and -tive numbers are formatted to red font and surrounded by brackets:
EG: #,##0,;[red](#,##0,);-

One notable advantage is that only TRUE zero's appear as "-".  Using the example above 499 will appear as "0" suggesting immediately that there is a balance possibly worth scrutinising.  I have frequently seen colleagues use rounding to 000's to conceal costs.

Do you have any other experiences that may be worth noting?

Thanks in advance.


----------



## Long Nose (Feb 2, 2009)

Charts have frustrated me in the past, particularly adding too much data and obscuring any gleaming gold nuggets.  Does anyone know, how much is too much?  Our company charts look like they need charts to explain themselves.

I kid you not, we have charts with 15 series of data on one chart PLUS a secondary Y axis on the right!  Am I just terribly small minded?  I can't get my head around what is going on there.


----------



## Andrew Fergus (Feb 2, 2009)

Hi Jon

In the late 1970's one of our former Prime Ministers, Robert Muldoon, would point to economic graphs with a rising trendline and claim all was well. Closer inspection of the graph (which would have the scales in the most miniscule of fonts) found the x-axis had been inverted. It looks like some people are still doing it : http://www.thestandard.org.nz/the-polls-and-petrol-prices/

Andrew


----------



## Gerald Higgins (Feb 2, 2009)

Hi Jon. Are you familiar with the work of Edward R Tufte ?

His books include many examples of both very good and very bad charting.


----------



## PaddyD (Feb 2, 2009)

classic bad data practise - percents of percents


----------



## Jon von der Heyden (Feb 3, 2009)

Thank you alll.



> In the late 1970's one of our former Prime Ministers, Robert Muldoon, would point to economic graphs with a rising trendline and claim all was well. Closer inspection of the graph (which would have the scales in the most miniscule of fonts) found the x-axis had been inverted. It looks like some people are still doing it : [URL="http://www.thestandard.org.nz/the-po...petrol-prices/"]http://www.thestandard.org.nz/the-po...petrol-prices/[/URL]


Thanks Andrew, would you believe I have even come across this.



> Are you familiar with the work of Edward R Tufte ?


Only through my recent research.  I like his LIE FACTOR ratio.   Thanks



> percents of percents


Paddy, what sort of scenario have you witnessed regarding this?  Sounds ominous!


----------



## cornflakegirl (Feb 3, 2009)

Andrew Fergus said:


> In the late 1970's one of our former Prime Ministers, Robert Muldoon, would point to economic graphs with a rising trendline and claim all was well. Closer inspection of the graph (which would have the scales in the most miniscule of fonts) found the x-axis had been inverted. It looks like some people are still doing it : http://www.thestandard.org.nz/the-polls-and-petrol-prices/



I don't think that graph is misleading though. He states clearly in the text that he's done it, and he's only done it to illustrate the correlation more clearly. (Not that I think it does show a correlation, but that's not the point.)

I've been thinking about misleading data (rather than graphs). I think most of the stuff that I see (and produce!) is to do with poor labelling, not updating text when the data changes, assumptions not clearly noted etc. Probably not the sort of stuff you want for a presentation!


----------



## Long Nose (Feb 3, 2009)

Last year my boss looked at me as if I had two heads because I'd never used a second Y axis.  Anyone here ever used one?


----------



## cornflakegirl (Feb 3, 2009)

Long Nose: Yes. Any time that I want to graph a margin % against a £ value over time, for example.


----------



## Long Nose (Feb 3, 2009)

Well, I stand corrected.  I've not run across this in past reports.


----------



## Jon von der Heyden (Feb 3, 2009)

> Last year my boss looked at me as if I had two heads because I'd never used a second Y axis. Anyone here ever used one?


Yes, I definitely think they have a purpose. Emma has just described one. Another example is to demonstrate sales v. sales volume, obviously depending on what is being sold. These can for instance describe increased sales through increased sales volumes, or perhaps increased sales due to increased product pricing.



> I've been thinking about misleading data (rather than graphs). I think most of the stuff that I see (and produce!) is to do with poor labelling, not updating text when the data changes, assumptions not clearly noted etc. Probably not the sort of stuff you want for a presentation!


It's a tricky subjecct to present. Well I think so. A few of points I'm throwing in:

1. Pivot Data is more reliable. I will go on to demonstrate how efficient pivot tables can be. Most of my colleagues and our management are not aware of things such as calculated items/fields, show pages, date grouping etc.
2. Where formula are used, and where they contain names, a list of all names and their corresponding formulae should always be presented, with a brief description toward the purpose of each. I will demonstrate how VBA can be quickly used to ptoduce the list.
3. Summaries and raw data should be contained in independent sheets. This may make it easier to validate and also minimises opportunities to (accidentally) corrupt the data.


----------



## Expiry (Feb 3, 2009)

In my experience, it's not so much that the individual graphs are misleading or wrong, more that there are just too many graphs. 

When it comes to KPIs, many people forget that the K is for KEY. The number of pencils we get through could arguably be a Performance Indicator, but would you measure that? No, because it's not Key. 

Sometimes there's a graph for every element of what we do and at best, that just dilutes the message, at worse it deflects the attention and energy from the areas that need it into somewhere that isn't KEY at all, but where the graph is telling the story that some people want.


----------



## Long Nose (Feb 3, 2009)

Thanks Jon,

Apparently I have two heads.  Our second Y axis are in $ as is the first Y access.  It's confusing to me.  If I could post a picture, I think you'd see what I mean.

Is there any way I can post a JPEG?


----------



## Jon von der Heyden (Feb 3, 2009)

Sounds ominous but that may still be ok just as long as there is still a correlation between the two.  What is each axis meant to represent?  Presume there are labels?

You can post an image using http://photobucket.com/


----------



## Greg Truby (Feb 3, 2009)

Long Nose said:


> Is there any way I can post a JPEG?


 
http://www.mrexcel.com/forum/showthread.php?t=204962


----------



## Long Nose (Feb 3, 2009)

I can't open the link on our work server, I'll try tonight at home.

Thanks!


----------



## Greg Truby (Feb 3, 2009)

Long Nose said:


> I can't open the link on our work server, I'll try tonight at home.
> 
> Thanks!


 
A lot of workplaces are now blocking photo-hosting sites, which is a bit of a bummer. I'm curious, if you follow my link, can you see the example images I used in that other thread? Or do those also get blocked?


----------



## Long Nose (Feb 3, 2009)

If I understand your question, then the Mr. Excel thread link shows no photos and the link to the photobucket is blocked.


----------



## Greg Truby (Feb 3, 2009)

Not really "photographs" per se, but rather an image of a dialog box and a clickable thumbnail image of a chart should be visible underneath where I have *How to Post Thumbnails and Pictures*.


----------



## Long Nose (Feb 3, 2009)

This is going to sound strange, but the first time I opened the link there were no thumbs, but on the second time there were thumbs and they opened the image.

Thanks!


----------



## Richard Schollar (Feb 3, 2009)

%s are always a good target - you only get sensible data when a consistent baseline is used. Consider:
*
Politician*: GDP fell last year by 20% but this year we have seen a rise of 25% so we are better off than ever before!

Year__GDP__GDP movement
Year0_100________-
Year1_80_______-20%
Year2_100______+25%

Net improvement of year2 compared to year0:  0%


----------



## Colin Legg (Feb 3, 2009)

RichardSchollar said:


> %s are always a good target - you only get sensible data when a consistent baseline is used. Consider:
> 
> *Politician*: GDP fell last year by 20% but this year we have seen a rise of 25% so we are better off than ever before!
> 
> ...


It's that darn volatility drag again!


----------



## PaddyD (Feb 3, 2009)

"%s are always a good target - you only get sensible data when a consistent baseline is use"

Exactly the point re percents of percents. Consider the following:


```
Widget   Y1         Y2       % growth
A           5           50          900%
B         10,000    10,001     0.01%
C          10           10           0%
D          100        200          100%
```
 

..the comment 'on average our sales grew 250% last year' is in some sense numerically correct but, from a business perspective, meaningless at best.


----------



## Long Nose (Feb 3, 2009)

I find this confusing, although I can explain it.

<a href="http://s579.photobucket.com/albums/ss234/detwd0hb/?action=view&current=Chart.jpg" target="_blank"><img src="http://i579.photobucket.com/albums/ss234/detwd0hb/Chart.jpg" border="0" alt="Example"></a>

<a href="http://s579.photobucket.com/albums/ss234/detwd0hb/?action=view&current=ChartII.jpg" target="_blank"><img src="http://i579.photobucket.com/albums/ss234/detwd0hb/ChartII.jpg" border="0" alt="Example II"></a>


----------



## SydneyGeek (Feb 3, 2009)

Ah... the "Unwrapped Toffee" chart. Always easy to interpret!

I agree about pivot tables. Quick summaries, easy to filter data, not so easy to lie about it. 

I regularly include a checksum row at the bottom of important summary sheets, so you can see if anyone has hidden rows of unwanted numbers. 

Charley Kidd has some useful hints on business charting at http://www.exceluser.com
He's also a fan of Tufte.

Denis


----------



## Jon von der Heyden (Feb 4, 2009)

Long Nose
That is a horrible chart.  I wouln't event know where to begin.

Denis,
Thank you for the pointer to exceluser.com.  And thanks for the checksum tip, I think that's quite an important tip.

Paddy and Richard
Thank you for the good examples.  I see what you mean.


----------



## gardnertoo (Feb 6, 2009)

The king of the "Misleading Chart" kingdom must be the radar plot.  I have used this chart for exactly one good application: graphing temperatures reported by sensors which are physically in a circular arrangement.  







In this example, you are looking upstream at the thermocouples which monitor exhaust temperatures from a combustion turbine.  Sensor #4 saw the most dramatic cooldown resulting from a flame-out in one combustor.  The bold red series at timestamp 7:03:50 marks the time the unit was shut down by it's protective systems.  This display format gives the maintenance and repair crew a good idea where to go look on the physical machine.


----------



## SydneyGeek (Feb 6, 2009)

You're missing the point Gardnertoo, that's actually informative! 

Something else that I hate, and never use, is pie charts. They often tell you nothing and can easily get overcrowded. 

Denis


----------



## hatman (Feb 8, 2009)

1) Second Y-Axis: very effective in many engineering applications when plotting two curves in drastically different units or scales on the same chart. Here's a typical example of one of mine: Time along the X-Axis, Pump RPM's from 0 to 5000 on the primary Y-Axis, and Pump Delta-Pressure from 0 to 10 psid on the secondary Y-Axis. 

2) My tip for making data easy to interpret is to chart it clearly. Seriously: raw numbers are almost impossible for anyone who is not personally collecting or massaging the data directly to be able to interpret. (okay, so that sentence was even more obfuscating than many of the unclear chart examples... sorry) What I mean to say is that the person who works directly with the data sets may be able to see meaning in the raw numbers, but someone like an upper level manager who is trying to get an exectutive summary is only going to infer meaning if it's somehow presented graphically. That being said, I do have a few pieces of advice for making it easier to see trends when looking at raw numbers. The first is to specify the number of decimal points and/or or sig figs discreetly and uniformly for all cells in the column. The second is to eliminate as many insignificant figures as possible. Third, when eliminating insignificant figures, choose wisely whather you allow Excel to do it's default rounding, or implementing some other method, like Int(), Ceiling() or Floor()... all of these mathods have pros and cons, but depending upon your data you can often clarify or confuse by choosing one over the other (and sometimes, it just doesn;t matter).

3) 87.3% of all statistics are made up on the spot.

4) "There are three kinds of lies: lies, damned lies, and statistics." 

5) Know when to use a log or a log-log scale... you can really skew your data by plotting it on a linear axis when it belongs on a log-scale, and vice versa.


----------



## Jon von der Heyden (Feb 9, 2009)

> 3) 87.3% of all statistics are made up on the spot.



I'm definitely using this! 
Seriously, thank you for your contribution.



> Something else that I hate, and never use, is pie charts.


Cannot agree more!  Thanks Denis


----------



## Expiry (Feb 9, 2009)

Why would you ALWAYS refuse to use a pie chart? If you're wanting to show a simple split of resources or something, surely a pie chart is an easy way to show that?

If 10% of my time is spent on admin, 30% on meetings and the rest on sales, for example, a pie chart would work and wouldn't mislead at all.


----------



## Jon von der Heyden (Feb 9, 2009)

> Why would you ALWAYS refuse to use a pie chart? If you're wanting to show a simple split of resources or something, surely a pie chart is an easy way to show that?
> 
> If 10% of my time is spent on admin, 30% on meetings and the rest on sales, for example, a pie chart would work and wouldn't mislead at all.



I don't think that Denis meant that they are misleading (although they can be - see link below), rather just that they are pointless.

Personally I prefer a vertical scale 0% - 100%.  Which means I would probably use a stacked column.

In fact rather, for me anything _suitably_ represented on a pie is just as easy to represent on a simple table.  Anything more than 4 - 5 slices becomes difficult to interpret (imo).  I especially hate 3D pie charts: http://www.mrexcel.com/tip142.shtml


----------



## Expiry (Feb 9, 2009)

I agree that 3D pie charts are rubbish charts.

In fact, someone should do a 3D pie chart to show what percentage of chart types are ok and what percentage are rubbish.


----------



## SydneyGeek (Feb 9, 2009)

Expiry said:


> Why would you ALWAYS refuse to use a pie chart? If you're wanting to show a simple split of resources or something, surely a pie chart is an easy way to show that?
> 
> If 10% of my time is spent on admin, 30% on meetings and the rest on sales, for example, a pie chart would work and wouldn't mislead at all.


 
As Jon said, it's not that they are always misleading. They just can't represent large datasets well. Most of the charting I do requires trend information with many data points and I have seen people try to use pies for that. Straight percentages, 3 or 4 points, no problem. but IMO, it's one of the most badly used chart types so I just avoid them on principle.

Denis


----------



## hatman (Feb 9, 2009)

SydneyGeek said:


> As Jon said, it's not that they are always misleading. They just can't represent large datasets well. Most of the charting I do requires trend information with many data points and I have seen people try to use pies for that. Straight percentages, 3 or 4 points, no problem. but IMO, it's one of the most badly used chart types so I just avoid them on principle.
> 
> Denis


 
Another chart type that I have seen abused is ye olde bar chart.  I've seen people try to plot several years worth of monthly data for several datasets (picture 5 or 6 colored bar sets), and then orient their x-axis vertically so the labels don't overlap, thus making the actual plot area very short and wide, so the trend (if there is one) is even more difficult to see.  I don't know why people are so afraid of x-y scatter plots in these cases...


----------



## SydneyGeek (Feb 9, 2009)

hatman said:


> Another chart type that I have seen abused is ye olde bar chart. I've seen people try to plot several years worth of monthly data for several datasets (picture 5 or 6 colored bar sets), and then orient their x-axis vertically so the labels don't overlap, thus making the actual plot area very short and wide, so the trend (if there is one) is even more difficult to see. I don't know why people are so afraid of x-y scatter plots in these cases...


Yep. And can anyone tell me why X-Y Scatter is the _only_ Excel chart type with a numerical X-axis?

Denis


----------

