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.

Public Function PropAxis(rLabels As Range, rValues As Range) As String

Dim vaLabels As Variant
Dim vaValues As Variant
Dim i As Long, j As Long
Dim dSpan As Double
Dim dIncrement As Double
Dim lPosition As Long
Dim aReturn(1 To 20) As String

'Put ranges in an array
vaLabels = rLabels.Value
vaValues = rValues.Value

'Find the span of the values range
dSpan = vaValues(UBound(vaValues, 1), 1) - vaValues(LBound(vaValues, 1), 1)

'initialize the array with spaces
For i = LBound(aReturn) To UBound(aReturn)
aReturn(i) = Space(1)
Next i

'put the first and last labels in the array
aReturn(1) = vaLabels(1, 1)
aReturn(UBound(aReturn)) = vaLabels(UBound(vaLabels, 1), 1)

'Put the middle labels proportionally
For i = LBound(vaLabels) + 1 To UBound(vaLabels) - 1
dIncrement = (vaValues(i, 1) - vaValues(1, 1)) / dSpan
dIncrement = dIncrement * (UBound(aReturn) - 1)
lPosition = Round(dIncrement, 0)

'If they're too close, just move one over
If aReturn(lPosition) <> Space(1) And lPosition < UBound(aReturn) Then lPosition = lPosition + 1 End If aReturn(lPosition) = vaLabels(i, 1) Next i PropAxis = Join(aReturn, vbNullString) End Function

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”)

Leave a Reply

Your email address will not be published. Required fields are marked *