Excel - Calculate Number of Digits 5

[NigeW]

Hi

I work in a screen printing business and we print numbered bibs for sports events.

Currently I manually create a spreadsheet for the number ranges required and then apply the following functions to calculate how many individual digits are required to be printed:

left(), mid(), right(), countif()

Is was wanting to setup a spreadsheet for my boss to use where he could simply enter the number in column A and then the calculation would automagically happen.

Any ideas will be gratefully received.

Thanks

Nigel Wilson
Christchurch Web Design

 

[SkipVought]

Hi,

Use the LEN function, returns the length of a string.

Skip,

_[red][/red]

 

[NigeW]

Hi Skip

Thanks for your feedback.

I didn't explain myself properly earlier.

As an example :

Bib number range of 100 - 170 and a number range of 200 - 2500.

From the above ranges I need to know how many 0's, how many 1's, how many 2's, etc.

The len() function will only return the length of the number.

Cheers



Nigel Wilson
Christchurch Web Design

 

[mintjulep]

The origianlly posed question was more interesting.

One would think that there would be a very elegant solution that made use of MOD(n,9), although I wasn't able to come up with it.

The revised functional criteria probably lends itself to some brute force VBA.

 

[SkipVought]

I can do it with a VBA function, but I have been trying to write an array function, to no avail.

The Word goes in A1
0 thru 9 in A2:A11
Enter function into B2:B11
[tt]
B2: =CountChr($A$1,A2)
[/tt]

Function CountChr(sSource As String, sChr As String) As Integer
    Dim i As Integer
    For i = 1 To Len(sSource)
        If UCase(Mid(sSource, i, 1)) = sChr Then CountChr = CountChr + 1
    Next
End Function

Skip,

_[red][/red]

 

[mintjulep]

Public Function DigitCounter(Low, High As Integer)
Dim CountArray As Variant
CountArray = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

For Count1 = Low To High Step 1

    For count2 = 1 To Len(Count1) Step 1
        Digit = Mid(Count1, count2, 1)
        CountArray(CInt(Digit)) = CountArray(CInt(Digit)) + 1
    Next count2
Next Count1

DigitCounter = CountArray

End Function

Enerted as an array function.

It seems to work.

 

[XrayboX]

use 2 sheets, and 1 named range digits=Sheet2!$B:$E
Sheet1
A1 = Range Start (200)
A2 = range end (2500)
A1= A2-A1+1
B1 = 1
C1 = 2
.......
K1 = 0

B2 =COUNTIF(digits,B1)
C2 =COUNTIF(digits,C1)
.......
K1=COUNTIF(digits,K1)

Sheet2
A1 = Sheet1!A1
B1 =IF(ISERROR(MID(A1,LEN(A1)-3,1)),"",MID(A1,LEN(A1)-3,1)+0)

C1 =IF(ISERROR(MID(A1,LEN(A1)-2,1)),"",MID(A1,LEN(A1)-2,1)+0)

D1 =IF(ISERROR(MID(A1,LEN(A1)-1,1)),"",MID(A1,LEN(A1)-1,1)+0)

E1 =IF(ISERROR(MID(A1,LEN(A1),1)),"",MID(A1,LEN(A1),1)+0)


A2 =IF(ROW()<=Sheet1!$A$3,A1+1,"")

copy B1 thru E1 and paste down greater than range end(Sheet1A2) will ever be
Do the same with A2

As you change the start and end range numbers on sheet1
You should see counts for each number

When Linux is Free and Open, who needs Windows or Gates?

 

[mharroff]

mintjuleps answer is close but only gives you the value for the "0" character.

If you modify his line

DigitCounter = CountArray

to

DigitCounter = "0 - " & CountArray(0) & "    " & _
               "1 - " & CountArray(1) & "    " & _
               "2 - " & CountArray(2) & "    " & _
               "3 - " & CountArray(3) & "    " & _
               "4 - " & CountArray(4) & "    " & _
               "5 - " & CountArray(5) & "    " & _
               "6 - " & CountArray(6) & "    " & _
               "7 - " & CountArray(7) & "    " & _
               "8 - " & CountArray(8) & "    " & _
               "9 - " & CountArray(9) & "    "

it will give you a string out put that has each digit and the number of times it occurs.

IE. Low = 101 High = 109
Output 0 - 9 1 - 10 2 - 1 3 - 1 4 - 1 5 - 1 6 - 1 7 - 1 8 - 1 9 - 1

 

[AnotherHiggins]

If I understand the request correctly, this should work:

[COLOR=blue white]=SUM(LEN(A1:A1000) - LEN(SUBSTITUTE(A1:A1000, B1, "")))[/color]

Enter as an array formula (use [Ctrl]+[Shift]+[Enter] instead of just enter).

In the above example, you would enter whatever number you wanted counted into cell B1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

John,

I knew that you'ld come thru with a spreadsheet solution. I just don't have that technique at all mastered! ==> *

Skip,

_[red][/red]

 

[mintjulep]

mharroff,

Enter it as an array formula spaning 10 cells. It works fine.

 

[AnotherHiggins]

Thanks, Skip!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mharroff]

You are right. I have not worked with array functions in the past.

 

[macropod]

Hi Nigel,

Here's another one, which only requires you to have the first & last numbers - in A1 & A2, respectively - and the test digit in B1:
=SUM(LEN(ROW(INDIRECT($A1&":"&$A2)))-LEN(SUBSTITUTE(ROW(INDIRECT($A1&":"&$A2)),B1,)))

Enter as an array formula.

If you have your digits 0-9 in B1:K1, copying the formula across will give the quantities required for all 10 digits.

Cheers

[MS MVP - Word]

 

[NigeW]

Hey

Blown away with the responses . . .

Thanks to everyone for contributing.

I managed to get the solution detailed by anotherhiggins working so thank you mate for your input.

The "Ctrl" - "Shift" - "Enter" option for formula's is new to me - quite frustrating to manage as if you want to edit the formula it loses the setting.

Thanks again to everyone for their input.


Nigel Wilson
Christchurch Web Design

 

[AnotherHiggins]

Yeah - if dealing with an Array Formula you have to use [Ctrl]+[Shift]+[Enter] after you edit each time. But, as has been mentioned, you can use 10 different array formulas so you shouldn't have to edit any of them.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[XrayboX]

hi macropod,

I know Nigel went with anotherhiggins solution, but yours looked interesting because you only need A1 and A2 fill in.

But because I primarily use Openoffice ( I work in a mix office,OO & MS), I couldn't get it to work.
After explaining indirect to my brother, I realised why it wasn't working in Calc but was in Excel.
In Excel, depending on how its used, Indirect doesn't require a text string.
After Adding the text "A" and "" it works in both Calc and Excel

Your code

=SUM(LEN(ROW(INDIRECT($A1&":"&$A2)))-LEN(SUBSTITUTE(ROW(INDIRECT($A1&":"&$A2)),B1,)))

Modified Code

=SUM(LEN(ROW(INDIRECT([COLOR=red]"A"&[/color]$A1&":[COLOR=red]A"[/color]&$A2)))-LEN(SUBSTITUTE(ROW(INDIRECT([COLOR=red]"A"[/color]&$A1&":[COLOR=red]A[/color]"&$A2)),B1,[COLOR=red]""[/color])))

So for posting an Great formula Here's a Star

When Linux is Free and Open, who needs Windows or Gates?

 

[xlhelp]

I know I am way late.... but, just to keep History in the forum; the following will work too. It's very close to John's (and slightly longer)

=SUMPRODUCT(--(LEN($A$1:$A$1000)-LEN(SUBSTITUTE($A$1:$A$1000,B1,""))))



Member- AAAA Association Against Acronym Abusers

 

[NigeW]

Hi xlhelp

Give you a star too - the solution I was using was reliant on pressing Ctrl Shift Enter when typing the formula and so was a bit of a nightmare to copy / paste.

Your solution works a treat and from a mangement perspective is somewhat easier.

Thanks again.

Nigel Wilson
Christchurch Web Design

 

[XrayboX]

Hi Nigel

Did You look close at macropod's solution?
It only needs the range start and end numbers, you don't have to fill down the row.(plus it looks like it calculates faster )

And instead of copy/paste, after you "ctrl-shift-enter" the first formula, grab the little square in the corner of its cell and "drag" it across to copy.


When Linux is Free and Open, who needs Windows or Gates?