tally marks

[Theadish]

How hard would it be to count a range and then show tally marks in the 5 count style of 4 vertical lines and a diagonal going through them? I assume in vb I could do a count then output four 1's and add strikethrough font on count = 5, then keep doing that in adjacent cells until the count has been reached. I think I could handle that, but is there possibly an easier way? Thanks for the help!

 

[RalphA]

Perhaps using Roman numarals I, II, III, IIII, V would be an acceptable alternate? It would be much simpler than the strikthrough, and very readable. Or, perhaps you would prefer the symbol on the Shift+\ , giving |, ||, |||, ||||, and finishing with V.

 

[milesUK]

Theadish, I had a look at this the other day. I'm sure that you could do it with formulas alone. Combinantions of MOD & DIV equivalent (i.e INT(x/n) as DIV does not seem to exist in Excel 2000). Conditional formatting can then be used to apply a strikeout font attribute when there are 5 lines in the cell.

VBA would be a doddle i'm sure. I like the challenge of getting it to work with formulas. What would be the maximum number you would want to convert to this format.

 

[Peter_SSs]

Pretty much as Miles described. Formula and Conditional Format from B1 is copied across and down. The CF format in the display below does not show it but the Format is simply "strikethrough" on the Font tab.

Excel Workbook
	A	B	C	D	E	F	G
1	7	1111	11
2	1	1
3	23	1111	1111	1111	1111	111
4	5	1111
5	4	1111
6	17	1111	1111	1111	11
7
Tally Marks

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =COLUMNS($B1:B1)<=$A1/5	Abc

 

[RalphA]

Peter, you are astounding! Your solution is ingenius, elegant, and complete! Wow!

 

[milesUK]

Peter, Nice one. I was trying the COLUMNS($A1:A1) approach but I ended up with no success. Thought i'd sorted it driving home on Friday but can i remember what that was! Thank you for making me aware of CHOOSE; i was using REPT.

 

[Theadish]

Thanks, Peter, and everyone else for the responses. It was exactly what I needed.

 

[ashima_singh]

Pretty much as Miles described. Formula and Conditional Format from B1 is copied across and down. The CF format in the display below does not show it but the Format is simply "strikethrough" on the Font tab.

Tally Marks

	A	B	C	D	E	F	G
1	7	1111	11
2	1	1
3	23	1111	1111	1111	1111	111
4	5	1111
5	4	1111
6	17	1111	1111	1111	11
7

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(COLUMNS($B1:B1)<=$A1/5,1111,IF(COLUMNS($B1:B1)=ROUNDUP($A1/5,0),CHOOSE(MOD($A1,5),1,11,111,1111),"")) <tbody> </tbody>

<tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format B1 1. / Formula is =COLUMNS($B1:B1)<=$A1/5 Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Please tell me how to count the number of tally marks in column A.