Tally chart cells exist in Excel!??

[Neo1]

Hello I was wondering if for example there exists a way in which yo
have a cell which u put a line like ║ to represent "1" so fo
example if you have 5 of those lines in a cell, i want the cell besid
to display the number "5".

Is this possible, if so can anyone explain me how to do it

Thanks for your time
From John Woo

 

[Leith Ross]

Hello Neo1,

Here is a User Defined Function (UDF) to tally bars in a cell. The bar
are the "pipe" character (ANSI code 124). Place this code in a VB
module in your workbook and call it just like anyother workshee
function.

For example say your bars are in cell A5, and you want the count in A6
Place the fomula below in A6...

=TallyBars(A5)

_________________________

Public Function TallyBars(ByRef Cell As Range) As Long
Application.Volatile

Dim I As Long
Dim N As Long

I = 1
Do While InStr(I, Cell, "|") > 0
I = I + 1
N = N + 1
Loop

TallyBars = N
End Function
_________________________

Sincerely,
Leith Ros

 

[Dave Peterson]

The character you used didn't come through in my newsreader, but if it's a
single character, couldn't you just use:

=len(a1)
where A1 contains a few of those funny characters.

If you want to go the opposite way, this looks pretty neat:

Type a number in A1 and use this formula
=REPT(REPT(CHAR(134),4)&" ",INT(A1/5))&REPT("|",MOD(A1,5))

 

[Neo1]

Yeah =len(a1) works very well indeed... its better than actuall
programming the worksheet i think right?... also how can i set fo
every 5th line it goes diagonally through the other four...like norma
tally charts do..?

Thanks for your time
From John Woo

 

[Dave Peterson]

Try this as a test:

Put some number in A1. I used 23
Put this in B1:
=REPT(REPT(CHAR(134),4)&" ",INT(A1/5))&REPT("|",MOD(A1,5))
Put this in C1:
=((LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(134),"")))/4)*5
+ LEN(B1)-LEN(SUBSTITUTE(B1,"|",""))
(all one cell)

When you change A1, you'll see B1 change and C1 should change to be the same as
A1.

Not quite diagonal, but pretty neat anyway.

 

[Neo1]

Can you do it so that when you change B1 by adding ticks it changes th
number in A1 and also C1?

Thanks for your time
From John Woo

 

[Dave Peterson]

Nope.

In my example, I made all my changes in A1.

But if you change B1, then C1 will be updated--but working with those kinds of
tick marks would become too much like work for me. (It wouldn't be long before
I messed it up.)

I'd either stick with a single character or do my entry in arabic numerals and
let excel make it look pretty.