Incell Dot Plots in Microsoft Excel

seems im having a blonde moment

ive tried following this but couldnt get the standardization to work so used the formula =B3/MAX($B$3:$D$9) * 100

some of the results come out as 11.5 which translates to 12 when removing the decimal places and i noticed that these are highlighted green on the example.
Would it be worth using =INT(B3/MAX($B$3:$D$9) * 100) ?

Cheers

This is a neat idea. Strictly speaking, I think we should use characters of equal width.

Ah, thanks, and thanks for the formula tip too.
unfortunately i cant download the examples at work so next time ill wait till i get home and cross check there before posting

Chandoo, I’ve just landed yesterday in your page, and must say it’s awesome !!

question: I wanna take the incell a step further, and create a UDF called (surprise!!) incell, to which I’ll pass the range of numbers I want to represent.

a first approach is related to the length of the bars, as I’m not sure how to normalize any number as a representation in 10 bars (silly me…).
the second issue might be related to sign. I’d thought on adding spaces prior to positive bars, so when represented they should be “upper”, and the opposite to negatives, so they appear “lower”.

finally, this is what I’ve done so far (not including my previous considerations), starting from your example:
Function incell(rango)
Cadena = “”
For Each m In rango.Count
Cadena = Cadena & (application.WorksheetFunction.Rept(”|”, Round(m.Value / 3, 1))) & Chr(10)
Next m
rango.Select
With selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ActiveCell.Offset(5, 0).Value = Cadena
End Function

Any help will be much appreciated.

Thanks !

Here’s an update:

Function incell(rango)
Cadena = “”
Blanco = application.WorksheetFunction.Rept(” “,10)
For Each m In rango.Count
If m.value =>0 then
Cadena = blanco & application.WorksheetFunction.Rept(”|”,Round(INT(ABS(M.VALUE)/MAX(RANGO)*100)))) & Chr(10)
Else
Cadena application.WorksheetFunction.Rept(”|”,Round(INT(ABS(M.VALUE)/MAX(RANGO)*100)))) & Chr(10)
& blanco
Next m

rango.Select
With selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

incell = Cadena
End Function

share your wisdom and make it better, please !!

RGds,

Martín.

well, that’s quick… this one works fine up to the cell formatting

Function incell(rango)

Cadena = “”
n = rango.Count
For m = 1 To n
repet = Round(Int(Abs(rango(m).Value) / application.WorksheetFunction.Max(rango) * 10))
blanco = application.WorksheetFunction.Rept(” “, 11)
resto = application.WorksheetFunction.Rept(” “, 11 – repet)
If rango(m).Value >= 0 Then
Cadena = Cadena & blanco & application.WorksheetFunction.Rept(”|”, repet) & Chr(10)
Else
Cadena = Cadena & resto & application.WorksheetFunction.Rept(”|”, repet) & Chr(10)
End If

Next m

incell = Cadena
ActiveCell.Select
With selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Function

Now, I can format the incells manually after the formula, but the trick would be to do it when using the formula, if possible…