Sunday, January 20, 2013

Excel Choropleth Maps for Coins

Project:
Have a map of the world that shows all the countries I collected coins from and have the shaded dark if I own a lot and shaded lightly if I only own a few coins, the top ten countries with the most coins owned should be obvious at first glance.

Plan: 
Create a heat map using my existing Excel file of world coins. Search the web for the best solution.

Results:

Map with U.S. and Canada included.
Map excluding U.S. and Canada.



Row Labels Sum of Value Sum of Coins

1 Great Britain $34.41 228

2 France $34.33 160

3 Spain $28.28 145

4 Trinidad & Tobago $8.83 76

5 Italy $31.07 70

6 Jamaica $12.02 60

7 Germany $33.57 51

8 Mexico $53.87 51

9 Israel $6.98 47

10 Greece $31.08 45


Grand Total $274.44 933

To make the map I downloaded Chloropeths maps featured on the Clearly and Simple website, a great site for data visualization. These maps have Microsoft friendly shapes that are associated with data. The shapes color is set by the user and using a formula will increase or decrease in intensity with the corresponding data.

I modified the RGB value associated with the shapes color to this:
=IF(D9>499,(udf_RGB(255,255,255)),udf_RGB(230-C9,255-C9,204-C9))
The reason is this allows certain data that is small to get colored white instead of lightly shaded. Also altered the original color from black to green and changed the background to blue to look like a real map.

The map is not perfect as many countries are missing and the shading is not "extreme" enough. More contrast can be controlled but small changes alters the map wildly. I can see modifying it further to make the top ten a different color or making it change with the value of coins.

Greenland is mislabeled as part of Canada I currently do not know how disassociate Greenland from Canada without erasing all of Canada. Luckily I have no Greenland coins which makes the second map more accurate.

Conclusion:
It works well and has great potential. Learn from Clearly and Simply because the site is a wealth of information. I plan to keep tweaking the map until it is simpler to use and modify then post up somewhere. Warning the maps are both protected and macro filled so you need to deal with that first.
Related Posts Plugin for WordPress, Blogger...