Sparkline Gauge

I have a list of labels and a list of values. I also have a value from somewhere in the range of my values list.

I want to show where the value falls in the list of values with a red mark. The labels need to be proportional to their values. An XY chart seemed like the obvious answer, but would require the XY Chart Label add-in or some better charting skills on my part. Also, there may be a few of these on a sheet, so I wanted to keep it more light-weight. Sparklines move nicely with cells, so I tried that route.

First, I needed to get the labels spread proportionally across a cell. I made the cell 20 characters wide by typing '00000000000000000000, setting the font to Courier New, and resizing the column. Cell C6 shows the positions. To get the proper spread of labels, I used a UDF, shown below.

The values still have to be reasonably spread out or this will overwrite some of them. But it worked for my purposes. The formula =PropAxis(F3:F8,G3:G8) is in C3.

Next I set up the range I2:AB3 to feed the sparkline. Row 2 represents the 20 characters in the cell. Row three should be all zeros except one, which will be a 1. Then a win/loss sparkline will show the one ‘win’ as a mark.

In H2, I calculate which character gets the win. Here’s how that formula progressed:

=ROUND((G2-G3)/(G8-G3)*20,0)

That figures where the value is in the list of values as a percentage and multiplies by the number of characters in the cell (20). I wanted to protect against a value that was not in the range, so I modified it to this:

=MIN(MAX(ROUND((G2-G3)/(G8-G3)*20,0),1),20)

Now it will never be less than 1 or greater than 20. Because of rounding, the mark may be one character off of an exact match. That’s probably not a problem – it’s obviously not supposed to be hyper-accurate since I’m only using 20 characters – but it just doesn’t look right. So I handle exact matches as special cases.

=IF(ISNA(MATCH(G2,G3:G8,FALSE)),MIN(MAX(ROUND((G2-G3)/(G8-G3)*20,0),1),20),FIND(INDEX(F3:F8,MATCH(G2,G3:G8,FALSE),1),C3))

If there isn’t an exact match, find the percentage and get close. If it is an exact match, find the character’s position in the string and put the mark there. Now in Row 3 of my sparkline data range, I use this number to find the ‘win’.

=IF($H$2=I2,1,0)

I thought this whole process would be easier with sparklines. But it’s not really any more light-weight than just a chart. Anybody else want to take a crack at it?

You can download SparklineProportion.zip

3 thoughts on “Sparkline Gauge

  1. I was more thinking about:

    in cell C5 : =F_snb(F3:G8,G2)

    Function F_snb(c01 As Range, c02 As Range)
    sn = Split(Space(20))
    sq = c01.Value

    For j = 1 To UBound(sq)
    sn(int(20 * (sq(j, 2) / (sq(UBound(sq), 2) – sq(1, 2))))) = sq(j, 1)
    Next
    sn(int(20 * c02.Value / (sq(UBound(sq), 2) – sq(1, 2)))) = “*”

    F_snb = Join(sn)
    End Function

  2. If you want to avoid coding altogether:

    Assume the following table is in range B2:C8

    Find: 60
    a 0
    b 40
    c 50
    d 70
    e 90
    f 100

    Put the following in B10:C12

    min =MIN(C3:C8)
    max =MAX(C3:C8)
    spread =C11-C10

    Put 20 spaces in D14

    Put the following in B15 to D20
    (NB B15 and D15 can be copied down)
    =ROUND((C3-$C$10)/$C$12*19,0) a =REPLACE(D14,B15+1,1,C15)
    =ROUND((C4-$C$10)/$C$12*19,0) b =REPLACE(D15,B16+1,1,C16)
    =ROUND((C5-$C$10)/$C$12*19,0) c =REPLACE(D16,B17+1,1,C17)
    =ROUND((C6-$C$10)/$C$12*19,0) d =REPLACE(D17,B18+1,1,C18)
    =ROUND((C7-$C$10)/$C$12*19,0) e =REPLACE(D18,B19+1,1,C19)
    =ROUND((C8-$C$10)/$C$12*19,0) f =REPLACE(D19,B20+1,1,C20)

    In B21:D21 put the following and the result is in D21
    =ROUND((C2-$C$10)/$C$12*19,0) 60 =REPLACE(D20,B21+1,1,”X”)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.