Fuel Gauge Redo

Earlier this week I had a reader comment on my Fuel Gauge dashboard graphic. Not familiar with what a fuel gauge chart is, click here see my earlier post.

Basically a Fuel Gauge graphic represents a percentage from 0 to 100. The concept is based on a pie chart. Here is an example of what one looks like.

My reader liked the concept but wanted to add a twist to it. He wanted the gauge to represent numbers from 70 to 100 and couldn't figure out how to convert it.

Well as a former coder I often say it’s only 1s and 0s. Anything is possible. So how do we convert the above fuel gauge to the one that works from 70 – 100?

Well first we need to understand the concept of the gauge.

Where my gauge would show 0, his will show 70, my 25 would be his 77.5, my 50 would be his 85, etc…

To convert the gauge I need to setup a matrix and it starts with a basic question. What is the difference between milestones on his gauge?

your #	77.50	My #	25
Your Starting #	70.00
	7.50	7.5/25 =	0.3000

I randomly choose one of his milestones (70, 77.5, 85, 92.5 or 100)

I then choose the next lower milestone and subtract the two.

In this case I chose 77.50 as the starting milestone and 70 and the previous milestone. The difference between these two numbers is 7.5. If I chose 85 and 77.5 I would again get 7.5 when I subtract them. So 7.5 is my first key for converting my gauge.

Next I take the first milestone of 77.50 and identify what that would be on my gauge (25). I then divide the 7.5 by 25 and get a conversion factor of 0.30 (7.5/25=0.30).

Now I can create my conversion matrix based on the 0.30 number. I create a column and run numbers from 0 to 100. I then start with the readers low score (70) adding 0.30 to each subsequent number. In the below example I show the first 11 numbers of my scale from 0 to 100. The readers scale increases 0.30 in each row.

Column P	Column Q
Your Scale	My Scale
70.00	0
70.30	1
70.60	2
70.90	3
71.20	4
71.50	5
71.80	6
72.10	7
72.40	8
72.70	9
73.00	10

Another way to get this number is to subtract the users high and low value and multiply that by 0.01.  (100-70)*0.01 = 0.30

Now that I have my matrix setup I just need to do a vlookup to convert the percentage entered by a user.

=VLOOKUP([Number Entered By User],P:Q,2,TRUE). This formula is shown in cell D3 of the converted Versions tab in the spreadsheet that you can download at the end of this post.

I need to use TRUE in my vlookup instead of false since with true, if an exact match is not found, the next largest value that is less than lookup_value is returned.

The vlookup result converts the user’s number into my gauge number from 0 to 100 and the needle points correctly.

Confused? Download the converted example and play with it.

Doughnut Chart / Spiral Chart

A couple of posts back I demonstrated how to create a box chart. Here is thepost.

Today I wanted to demonstrate how to display the same data as a spiral graph. What’s a spiral graph you ask? Basically it’s a doughnut chart. Let me show you… In the above example I compare 3 leaf blowers based on Satisfaction, Price and Durability. Each category and item is separate. Well for the spiral graph I want to compare each category separately. I want to compare all three blower types for Satisfaction in one graph, Price in a second and Durability in a third.

To start my spiral graph I go to my data tab. For the green leaf blower I have the following data. The first column of numbers is the results of the survey. The second column of numbers is 100 – the first column (100 – 53 = 47). I need both of these numbers (53 and 47) to create the first spiral of my graph.

Satisfaction	53	47
Price	41	59
Durability	62	38

So I start by highlighting Satisfaction, 53 and 47. Then I choose Insert > Other Charts > Doughnut Chart

This creates my first spiral in the doughnut chart.

Now I want to change the blue of the doughnut to match the green color of the Satisfaction bar chart. I select the blue by mouse clicking on it. Now you may find that when you click on the blue Excel selects both the Blue and Red colors. No big deal, just click on the blue a second time and try again.

From the format tab, change the shape fill to be green.

Repeat the process for the red portion of the doughnut but select no fill instead of a color.

When Done I have my first section of my chart.

Now I can clean up the chart by removing the legend to the right by clicking on it and hitting the delete key.

Next I want to add the Yellow Satisfaction to my chart. I right mouse click on my green spiral and choose Select Data.

This opens the Select Data Source dialog. Click the add button. For Series name select cell A20 and for series value enter =Data!$B$20:$C$20

This adds the second spiral to the chart. I repeat the process above to change the color blue to yellow and no fill on the red portion.

Finally again I repeat the Select Data and add the red Satisfaction data.

I modify the blue color to be red and no fill the other section.

I am now left with my spiral graph.

I add the chart title “Satisfaction” by selecting Chart Title centered Above Chart from the Layout tab.

Now I can add a black edge to each color in my chart if I wish by using the Shape outline option just below the shape fill shown above.

Next I cut my chart moving it from the data tab to my Dashboard tab and resize it.

I repeat the above process for Price and Durability. When done I have displayed my results two different ways, by bar chart and by doughnut chart (spiral graph).

To add some additional detail I add text boxes for numbers and a line and circle from the Insert tab > Shapes.

How can you use the above techniques to add some wow to your next dashboard?

If you want to download the example spreadsheet, click onthe following link.