Take a look at the innovation heat map published by McKinsey (link to article)
I call this the “un-innovative heat map on innovation” for obvious reasons.
First let me fill you on what the chart is trying to show. Folks at McK got curious to know how innovation around the world is and partnered with World Economic Forum to create this heat map. The map shows innovation clusters (sized based on the number of patents won) on 2 dimensions – Diversity (how many companies & patent sectors are in the cluster) and Momentum (growth rate of patents). If you are wondering what a cluster is, it is a city.
Confused?
Well, Mckinsey folks are always keen to plot anything including your cat on two dimensions. So they created a gazillion bubbles and plotted them on 2 axes and conveniently sliced the area in to 9 parts and named them like hot springs, shrinking pools, molten lava (well, not really, but the first two are true, I swear!!!)
One of the primary shortfalls of this heat map is, it takes innovation clusters (cities) that already have geographical co-ordinates and plots them in a way that is unreadable.
How could they have improved this heat map?
Instead of plotting the bubbles on 2 dimensions like diversity and momentum, they should have used simple google maps API or Many eyes world map visualization and colored the bubbles based on whether the cluster is in a hot spring or a stinking ooops, shrinking pool. That would have improved the effectiveness of this heat map so much more. Hey, it also helps you locate your state or city easily.
Your comments on this heat map – Hot or not?
Previously on infographic inspiration: Bubble Chart Fail, Bubble Chart Success, Kiss and Impress, More visualization inspiration
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.