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

Here is the chart version using a Line chart with standard data labels, although it’s not really anymore lightweight.

https://skydrive.live.com/redir?resid=EAD861C14BCC56B7!528

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

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