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.



5 comments:

  1. I tried playing with your chart and entered my numbers in the conversion table. My numbers go from 30-0 which is a negative scale - when I plugged my numbers into your scale, the needle didn't move at all. What am I doing wrong?

    ReplyDelete
  2. The issue is the scale conversion. You need to flip it. Use the following as a guide.

    Your Scale My Scale
    0.00 1
    0.30 2
    0.60 3
    0.90 4
    1.20 5
    1.50 6
    1.80 7
    2.10 8
    2.40 9
    2.70 10
    3.00 11

    ReplyDelete
  3. I modified this so you can just enter in your start number and end number, the scale will auto calculate.

    Can't figure out how to change the rainbow to step colors.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete