Counting the digits in a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I am working on is a spreadsheet used for calculating how many labels are needed for RJ-45 patch panels. Each port on the pach panel has a lable comprised of single digit labels Example port # 1 is 001. so that would be two 0 labels and one 1 lable, etc.
I currently am using the autofill feature so that I type in 001 then under it 002 and drag to 500 or whatever number of ports I need. It then tells me on the right how many labels fro 0 to 9 I need to purchase.

What I would like to accomplish is to set it up so that I can put in a start number in one cell like 001 and an end number in another cell say 600, and achieve the same results.

Thank you in advance for anyone who can help.
 
Can't follow question. Are you trying to sum 1+2+3......N?
The answer is N(N+1)/2 as proved by Gauss when he was 7 years old

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address
MaxPower said:
What I am working on is a spreadsheet used for calculating how many labels
Click to expand...
are needed for RJ-45 patch panels. Each port on the pach panel has a lable
comprised of single digit labels Example port # 1 is 001. so that would be
two 0 labels and one 1 lable, etc.
I currently am using the autofill feature so that I type in 001 then under
Click to expand...
it 002 and drag to 500 or whatever number of ports I need. It then tells me
on the right how many labels fro 0 to 9 I need to purchase.
What I would like to accomplish is to set it up so that I can put in a
Click to expand...
start number in one cell like 001 and an end number in another cell say 600,
and achieve the same results.
 
Max,

Here is a way with putting the start at end numbers in the formula

=SUMPRODUCT(--(MID(TEXT(ROW(1:600),"000"),{1,2,3},1)=A1))

in A1 put the label to be tested for, 0, 1, etc. as text, for instance '0,
'1, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

MaxPower said:
What I am working on is a spreadsheet used for calculating how many labels
Click to expand...
are needed for RJ-45 patch panels. Each port on the pach panel has a lable
comprised of single digit labels Example port # 1 is 001. so that would be
two 0 labels and one 1 lable, etc.
I currently am using the autofill feature so that I type in 001 then under
Click to expand...
it 002 and drag to 500 or whatever number of ports I need. It then tells me
on the right how many labels fro 0 to 9 I need to purchase.
What I would like to accomplish is to set it up so that I can put in a
Click to expand...
start number in one cell like 001 and an end number in another cell say 600,
and achieve the same results.
 
Hi Max
Could you post one of the formulas you are using to count the digits. This can probably be set up to start with a table from 001 to 999, then two MATCH functions can be used to select the start and end positions in the table

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- MaxPower wrote: ----

What I am working on is a spreadsheet used for calculating how many labels are needed for RJ-45 patch panels. Each port on the pach panel has a lable comprised of single digit labels Example port # 1 is 001. so that would be two 0 labels and one 1 lable, etc.
I currently am using the autofill feature so that I type in 001 then under it 002 and drag to 500 or whatever number of ports I need. It then tells me on the right how many labels fro 0 to 9 I need to purchase.

What I would like to accomplish is to set it up so that I can put in a start number in one cell like 001 and an end number in another cell say 600, and achieve the same results.

Thank you in advance for anyone who can help.
 
Very nice formula Bob

I played around with it a little to allow the start and finish numbers to be entered in seperate cells

=SUMPRODUCT(--(MID(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"000"),{1,2,3},1)=A5)

The start number goes in B1, the end number goes in B2 and the number being counted goes in A5

Good Luck Max

Regards
Mark Graesse
(e-mail address removed)
Boston M


----- Bob Phillips wrote: ----

Max

Here is a way with putting the start at end numbers in the formul

=SUMPRODUCT(--(MID(TEXT(ROW(1:600),"000"),{1,2,3},1)=A1)

in A1 put the label to be tested for, 0, 1, etc. as text, for instance '0
'1, etc

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

MaxPower said:
What I am working on is a spreadsheet used for calculating how many label
Click to expand...
are needed for RJ-45 patch panels. Each port on the pach panel has a labl
comprised of single digit labels Example port # 1 is 001. so that would b
two 0 labels and one 1 lable, etc
I currently am using the autofill feature so that I type in 001 then unde
Click to expand...
it 002 and drag to 500 or whatever number of ports I need. It then tells m
on the right how many labels fro 0 to 9 I need to purchasestart number in one cell like 001 and an end number in another cell say 600
and achieve the same results
 
Sure

This formula is in each of the cells accross for 0,1,2,3,4,5,6,7,8,9 . =LEN(B11)-LEN(SUBSTITUTE(SUBSTITUTE(B11,"0",""),"0",""))

Then on the right I have the "quantity listed verticaly and the following formula in each cell. =SUM(C10:C2508

I have it counting the digits fine, but I just want to set it up to use a "start of range "cell and End of range "cell, instead of the whole fill range process. The ports are numbered using a 3 digit number like 001, 002, and can range from only a few ports per patch panel up to hundreds of ports. Then I may need to do 50 or 100 panels. So, tou see my need to find a better way.
 
Doh! Of course, I should have thought of that.

Bob

Mark Graesser said:
Almost forgot, instead of entering the digit to be counted as text, you
Click to expand...
could add a TEXT function to the counted argument:
=SUMPRODUCT(--(MID(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"000"),{1,2,3},1)=TEXT(
A5,"0")))

Regards,
Mark Graesser
(e-mail address removed)
Boston MA

----- Mark Graesser wrote: -----

Very nice formula Bob!

I played around with it a little to allow the start and finish
Click to expand...
numbers to be entered in seperate cells.
 
Thanks Mark. Funny how you went that way, I started with that and thought it
better to embed it in the formula. Life's rich tapestry I guess. These
posters are so lucky, not only solutions, but variety as well <G>

Bob
 
Thanks guys, appreciate the comments.

Mark (MaxPower version), it seems that you struck lucky with Mark helping
you through. The Internet is good isn't it?

Bob
 
Back
Top