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
StateName - Change N1 formula to
=getip() - Change E2 formula to
=IF(A2=StateName,B2,IF(ABS(ROW()-1-StateIndex)<3,B2,0)) - 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”
xml.send
lMaxTime = Timer
Do Until xml.readyState = 4
DoEvents
If Timer – lMaxTime > (TimeValue(“00:00:05”) * 60 * 60 * 24) Then Exit Do
Loop
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)
End Function
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.
“Does anyone really look at the top or bottom five before he finds his own state?”
Good point. I sometimes forget these are real people using these charts. The GetIP function is a nice, if somewhat friable addition. An alternate approach would be to find the user’s credit card information on the computer, and get the state from the billing address.
[…] Dick Kusleika has made an adaptation to this chart that always displays the user’s home state. As he so astutely points out, “Does anyone really look at the top or bottom five before he finds his own state?” Read about it in Ego Charts. […]
Friable indeed, but fun. :) Send me your credit card number and I’ll work on the next part.
Friable or not it going in my code library – by which i mean in a few months I will be looking around DDoE to try and find it again! Excellent little snippet Dick, thanks.
p.s. I had to look up what friable meant, Stop begin so clever Jon, you have to dumb it down for me!
“p.s. I had to look up what friable meant”
Me too. And then I got hungry for some eggs.
Yes, friable describes eggs, but not in the way it sounds.
Jon,
Using “friable” in a comment ammounts to extraneous othogonality :-) :-). Or is that just the sound of my paradigm shifting?
Dick, how about we add a function to DDoE to vote on cute words added to comments?
OK, I had to look it up, too. Nice one Jon. You’d think an engineering degree and an MBA would have prepared me for life.