Jorge Camoes discussed skyscraper bar charts. He writes:
Listen, unless you work for a statistics office, you should never create a chart like this. I know, it’s irresistible to check how well my state ranks, but identifying each and every data point in a virtually limitless bar chart makes no sense in most cases.
Does anyone really look at the top or bottom five before he finds his own state?
Jon Peltier took the idea a step further and created an accordion chart. Sweet. I’m going to take another step (well, more like a quarter step) and make sure that my state is always showing.
If you download Jon’s chart, you can modify it with these steps:
- Add GetIP Function (see below)
- Name cell N1
- Change N1 formula to
- Change E2 formula to
- Fill down column E
The GetIP function is below. You have to set a reference to Microsoft XML (VBE – Tools – References).
Dim xml As MSXML2.XMLHTTP
Dim sHtml As String
Dim lCityStart As Long
Dim lCityEnd As Long
Dim lComma As Long
Dim lMaxTime As Long
Const sFIND As String = “You appear to be from “
Set xml = New MSXML2.XMLHTTP
xml.Open “GET”, “http://private.dnsstuff.com/info/geolocation.htm”
lMaxTime = Timer
Do Until xml.readyState = 4
If Timer – lMaxTime > (TimeValue(“00:00:05”) * 60 * 60 * 24) Then Exit Do
sHtml = xml.responseText
lCityStart = InStr(1, sHtml, sFIND) + Len(sFIND)
lCityEnd = InStr(lCityStart, sHtml, “”)
lComma = InStr(lCityStart, sHtml, “,”)
GetIP = Mid$(sHtml, lComma + 2, lCityEnd – lComma – 3)
Severely not tested. If you don’t live in the US or if you have a comma in your city name, it will blow up. I couldn’t find a decent way to get the state from the ip address, so I resorted to scraping. The site hostip.info looked promising, but it returned “Unknown City” for my IP.