Well guys, here is one of my favorites graphs. Again, as I mentioned in the intro, I am more of a crazy outside of the box thinker with excel, than a true coder. My strength lies in manipulating excel to create interesting, neat solutions.
Drum roll please… the Speedometer.
I looked online forever for instructions on how to create a completely automated speedometer using an excel chart. Alas, I couldn’t find anything. So, I made it myself. Once you know how the graph works, its actually pretty simple, but creating it was someone difficult.
Here is a file with the graph:
What I have done is layered a “radar” graph on top of a pie chart. (Pause for thunderous applause). The pie chart represents the areas of the speedometer (in this case risk levels) and the radar graph creates the needle. Let’s take a look at the specifics on the spreadsheet.
Note 1:
This portion of the spreadsheet simply divides the pie chart in areas. The total areas add up to 100%. In this case I have divided up the top half of the pie into 4 zones corresponding to risk levels. Note, because we are only using the top half of the pie, the bottom unused portion takes up 50%. I have divided the remaining areas equally – 12.5% each. You could change the size of the pie depending on what you wanted. You could even have no specific areas. YOU ARE THE BOSS! I left extra spots on the spreadsheet if you wanted more than 4 areas.
Note 2:
Arrow % is simply where you want to arrow in your graph to be. In this sheet it is an input cell, however, in most cases this would likely be the result of a calculation. 50% represents the middle of the graph and a vertical line. Don’t believe me, try it!
Note 3 & 4:
Next, I had to create the needle. Originally, I started off with only a few possible needle points. I soon realized that for a speedometer to work, you need fairly precise measurement. If you scroll down column E you will see I have 1000 “possible needles”. Now, I suppose I need to get slightly technical to explain exactly how it works. As I just mentioned, column E has %’s listed from 50% to 100% and then following a big gap of 1000 cells (we don’t use the bottom of the graph, remember?), 0% to 50%. Column F is the actual needle data. These cells have a formula that says: look at our arrow %, if the number the same row in column E is equal to the arrow %, put a 1 in the cell. Otherwise put a zero. We round the input Arrow % cell to the 1st decimal place because this is the accuracy of our corresponding needles in column E. The result is that every row in column F will be 0, except for 1 cell that equals of arrow %. The radar graph ends up looking like a line because all the 0’s are on top of each other, except for one point. A line is created between the 0 value and the 1 value. A line is also created from the 1 value to the next 0, but these are again layered on top of each other. The result is a needle that moves up and down depending on the Arrow % entered.
The words on the graph page are simply “Word Art” layered on top of the chart.
Well, I tried to my best to explain the graph, but, there may be some follow up questions. Let me know what you think. Is it neat? Am I dumb? Is there an easier solution? I look forward to any feedback. Also, this is the first time I have tried to link to google docs. Please comment if the link doesn't work. Lastly, if you would prefer a step by step guide, I could work on something like that.
Cheers!
