ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Count of FIRST Digit (https://www.excelbanter.com/excel-programming/329865-count-first-digit.html)

Paul Black[_2_]
Count of FIRST Digit
 
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Peter T
Count of FIRST Digit
 
Hi Paul,

One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Bob Phillips[_6_]
Count of FIRST Digit
 
Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Peter T
Count of FIRST Digit
 
I've just seen Bob's neat Sumproduct formula. Try inserting following at the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Paul Black[_2_]
Count of FIRST Digit
 
Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Bob Phillips[_6_]
Count of FIRST Digit
 
Put this formula in CO5

=SUMPRODUCT(--(LEFT(B5:CM5,1)=TEXT(COLUMN(A1),"0")))

copy across to CW5, then copy down to CO505:CW505

in CO506, add

=SUM(CO5:CO505)

and copy across to CW506

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Peter T
Count of FIRST Digit
 
For a non-formula, vba approach:

Sub test2()
Dim rng As Range

Dim n As Long, nr As Long, nc As Long
Set rng = Range("b5:cm504")
With rng
ReDim nArr(1 To .Rows.Count, 1 To 9) As Long

For nr = 1 To .Rows.Count
For nc = 1 To .Columns.Count
n = Val(Left(.Cells(nr, nc), 1))
nArr(nr, n) = nArr(nr, n) + 1
Next
Next

End With

Set rng = rng(1).Offset(0, rng.Columns.Count + 1) _
.Resize(UBound(nArr), 9)
rng.Value = nArr

End Sub

Sub DumpRand()
Dim rng As Range
Set rng = Range("b5:cm504")
Application.Calculation = xlCalculationManual
With rng
..Cells(1, 1).Formula = "=Int(1000 * Rand() + 1)"
..Cells(1, 1).AutoFill .Columns(1)
..Columns(1).AutoFill rng
Application.Calculate
..Value = .Value
End With
Application.Calculation = xlCalculationAutomatic

End Sub

BTW - this is 500 rows x 90 columns per your example with B:CM

Regards,
Peter T


Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Peter T
Count of FIRST Digit
 
Hi Bob,

I think if autofill'ing it might need a bit of absolute for the columns.

=SUMPRODUCT(--(LEFT($B5:$CM5,1)=TEXT(COLUMN(A1),"0")))

Regards,
Peter T

Put this formula in CO5

=SUMPRODUCT(--(LEFT(B5:CM5,1)=TEXT(COLUMN(A1),"0")))

copy across to CW5, then copy down to CO505:CW505

in CO506, add

=SUM(CO5:CO505)

and copy across to CW506

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Bob Phillips[_6_]
Count of FIRST Digit
 
Good point :-)

"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

I think if autofill'ing it might need a bit of absolute for the columns.

=SUMPRODUCT(--(LEFT($B5:$CM5,1)=TEXT(COLUMN(A1),"0")))

Regards,
Peter T

Put this formula in CO5

=SUMPRODUCT(--(LEFT(B5:CM5,1)=TEXT(COLUMN(A1),"0")))

copy across to CW5, then copy down to CO505:CW505

in CO506, add

=SUM(CO5:CO505)

and copy across to CW506

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks for the Replies.

Bob,
Your Solution Works Great, Thanks.

Ashley,
I will have a Look at Using Pivot Tables, Thanks.

Peter,
Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total
Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then
Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following
at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Peter T
Count of FIRST Digit
 
I posted the wrong macro, this should be faster:

Sub test3()
Dim rng As Range
Dim vArr
Dim n As Long, nr As Long, nc As Long

Set rng = Range("b5:cm504")
vArr = rng.Value
ReDim nArr(1 To UBound(vArr), 1 To 9) As Long

For nr = 1 To UBound(vArr)
For nc = 1 To UBound(vArr, 2)
n = Val(Left(vArr(nr, nc), 1))
nArr(nr, n) = nArr(nr, n) + 1
Next
Next

Set rng = rng(1).Offset(0, rng.Columns.Count + 1) _
.Resize(UBound(nArr), 9)
rng.Value = nArr

End Sub

Regards,
Peter T

For a non-formula, vba approach:

Sub test2()
Dim rng As Range

Dim n As Long, nr As Long, nc As Long
Set rng = Range("b5:cm504")
With rng
ReDim nArr(1 To .Rows.Count, 1 To 9) As Long

For nr = 1 To .Rows.Count
For nc = 1 To .Columns.Count
n = Val(Left(.Cells(nr, nc), 1))
nArr(nr, n) = nArr(nr, n) + 1
Next
Next

End With

Set rng = rng(1).Offset(0, rng.Columns.Count + 1) _
.Resize(UBound(nArr), 9)
rng.Value = nArr

End Sub

Sub DumpRand()
Dim rng As Range
Set rng = Range("b5:cm504")
Application.Calculation = xlCalculationManual
With rng
.Cells(1, 1).Formula = "=Int(1000 * Rand() + 1)"
.Cells(1, 1).AutoFill .Columns(1)
.Columns(1).AutoFill rng
Application.Calculate
.Value = .Value
End With
Application.Calculation = xlCalculationAutomatic

End Sub

BTW - this is 500 rows x 90 columns per your example with B:CM

Regards,
Peter T


Actually I have about 500 Rows of 100 Numbers, for Example B5:CM505.
What I would like is in Cell CO5 for Example is the Total Count of the
First Digit 1 for that Individual Row, then in Cell CP5 the Total Count
of the First Digit 2 for that Individual Row etc upto Cell CW5 and the
Total Count of the First Digit 9 for that Individual Row. Then Continue
Down All 500 Rows Putting the Counts for EACH Individual Row. It would
Also be Nice to Have a Grand Total for EACH First Digit Count at the
Bottom.

Thanks Again Everyone.
All the Best.
Paul



Count of FIRST Digit
From: Peter T

I've just seen Bob's neat Sumproduct formula. Try inserting following at
the
end of my Test macro

For i = 1 To 9
s = Chr(34) & CStr(i) & Chr(34)
Cells(i, 3).Formula = "=SUMPRODUCT(--(LEFT(A1:A100,1)=" & s & "))"
Next

Peter T


One way, if I understand the question -

Sub test()

For i = 1 To 100
Cells(i, 1) = Int(1000 * Rnd + 1)
Next

Dim nArr(1 To 9) As Long
Dim n As Long

For i = 1 To 100
n = Val(Left(Cells(i, 1), 1))
nArr(n) = nArr(n) + 1
Next

Range("b1:b9") = Application.Transpose(nArr)

End Sub

Regards,
Peter T

"Paul Black" wrote in message
...
Hi Everyone,

I have a Row of 100 Numbers that Range from 1 to 1000.
What I would like to do is to Count How Many Times the FIRST Digit
1,2,3,4,5,6,7,8 & 9 Appear in Each Row Please.
I know that I can Use Left(B10) for Example to get the FIRST Digit
But
How do you get it to do the Count Please.

Thanks in Advance.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***

Darren Hill[_3_]
Count of FIRST Digit
 
On Sun, 22 May 2005 11:39:20 +0100, Bob Phillips
wrote:

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?


I have a problem that this approach might help with.
I have a table with six-digit entries like:
100000
010000
020010
003001
010040
010201

I need a formula that can return the count of each possible digit.
So, if I checked for "1" it would tell me that all 6 above entries contain
a 1 (the last entry has 2 1's, but it's only 1 cell)
If I checked for "2", it would tell me that 2 of the above entries contain
a 2.

Is this possible, without resorting to splitting the information into six
separate columns?

Darren

Vasant Nanavati
Count of FIRST Digit
 
Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6sahyled89cl@omega...
On Sun, 22 May 2005 11:39:20 +0100, Bob Phillips
wrote:

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?


I have a problem that this approach might help with.
I have a table with six-digit entries like:
100000
010000
020010
003001
010040
010201

I need a formula that can return the count of each possible digit.
So, if I checked for "1" it would tell me that all 6 above entries contain
a 1 (the last entry has 2 1's, but it's only 1 cell)
If I checked for "2", it would tell me that 2 of the above entries contain
a 2.

Is this possible, without resorting to splitting the information into six
separate columns?

Darren

Darren Hill[_3_]
Count of FIRST Digit
 
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Bob Phillips[_6_]
Count of FIRST Digit
 
A bit simpler

=SUM(--(ISNUMBER(FIND(1,A1:A6))))

also as an array formula.

Continuing the SUMPRODUCT (non-array) theme

=SUMPRODUCT(--(ISNUMBER(FIND(1,A1:A6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6sahyled89cl@omega...
On Sun, 22 May 2005 11:39:20 +0100, Bob Phillips
wrote:

Hi Paul,

=SUMPRODUCT(--(LEFT(A15:A20,1)="1"))

This will count 1 and 11 as separate matching items. Is this correct?


I have a problem that this approach might help with.
I have a table with six-digit entries like:
100000
010000
020010
003001
010040
010201

I need a formula that can return the count of each possible digit.
So, if I checked for "1" it would tell me that all 6 above entries
contain
a 1 (the last entry has 2 1's, but it's only 1 cell)
If I checked for "2", it would tell me that 2 of the above entries
contain
a 2.

Is this possible, without resorting to splitting the information into
six
separate columns?

Darren

Darren Hill[_3_]
Count of FIRST Digit
 
Thanks, Bob, for those other approaches. I see they both work, too.
Can you see an answer to my additional question (repeated below):

If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell
shouldn't be included. On this version of the count, this cell should be
counted only when I search for "2".
Is there a way to modify the formula for this?

Finally, these formulas all use "--" - what does this signify?

Thanks again

Darren


On Sun, 22 May 2005 18:54:09 +0100, Bob Phillips
wrote:

A bit simpler

=SUM(--(ISNUMBER(FIND(1,A1:A6))))

also as an array formula.

Continuing the SUMPRODUCT (non-array) theme

=SUMPRODUCT(--(ISNUMBER(FIND(1,A1:A6))))

Vasant Nanavati
Count of FIRST Digit
 
The silence is deafening :-). This one is substantially trickier, I think. I
will play with it when I have a bit of time.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Vasant Nanavati
Count of FIRST Digit
 
Here you go ... clunky, but I think it works, as long as there are exactly 6
digits in each cell:

=SUM((--(ISNUMBER(FIND($B$1,A1:A6))))*(--MID(A1:A6,1,1)<=$B$1)*(--MID(A1:A6,
2,1)<=$B$1)*(--MID(A1:A6,3,1)<=$B$1)*(--MID(A1:A6,4,1)<=$B$1)*(--MID(A1:A6,5
,1)<=$B$1)*(--MID(A1:A6,6,1)<=$B$1))

entered as an array formula with <Ctrl <Shift <Enter. B1 contains the
target digit.

--

Vasant



"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
The silence is deafening :-). This one is substantially trickier, I think.
I
will play with it when I have a bit of time.

--

Vasant

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Darren Hill[_3_]
Count of FIRST Digit
 
Thanks, take your time - I'll only be checking the group every 5 minutes
or so...
Just kidding :)

Darren

On Sun, 22 May 2005 22:43:24 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

The silence is deafening :-). This one is substantially trickier, I
think. I
will play with it when I have a bit of time.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Peter T
Count of FIRST Digit
 
Hi Darren,

Try following to return the highest digit found in any of the six digits of
any cell in the range:

=MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))

Then combine with Bob's formula
=SUM(--(ISNUMBER(FIND(MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1 ))),A1:A6))))

or if the above Max formula is in B7
=SUM(--(ISNUMBER(FIND(MAX(VALUE(B7)),A1:A6))))

which should return a count of the cells that contain that highest digit.

All above formulas array entered. Also, all cells in the range must have six
digits. So a value like 1234 should be a string '001234. Not sure how to
cater for variable length numbers.

If I correctly understand what's required (?) this seems to work, but better
double check!

Regards,
Peter T

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Darren Hill[_3_]
Count of FIRST Digit
 
You're a genius! Yes, it works. I was able to check it manually against a
small range before applying to to the full many thousand cell range.
I'm in awe :) Thanks again.

Darren

On Sun, 22 May 2005 23:13:08 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

SUM((--(ISNUMBER(FIND($B$1,A1:A6))))*(--MID(A1:A6,1,1)<=$B$1)*(--MID(A1:A6,
2,1)<=$B$1)*(--MID(A1:A6,3,1)<=$B$1)*(--MID(A1:A6,4,1)<=$B$1)*(--MID(A1:A6,5
,1)<=$B$1)*(--MID(A1:A6,6,1)<=$B$1))

Darren Hill[_3_]
Count of FIRST Digit
 
Thanks for the response. Looks like Vasant Nanavati beat you to the
no-prize by mere minutes. Your method makes it easier for me to understand
what's going on.
I'm off to study this, until just before my head explodes :)
Thanks!

Darren
PS: you did understand what was needed - all my cells did have exactly 6
digits, formatted as text.

On Mon, 23 May 2005 00:06:50 +0100, Peter T <peter_t@discussions wrote:

Hi Darren,

Try following to return the highest digit found in any of the six digits
of
any cell in the range:

=MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))

Then combine with Bob's formula
=SUM(--(ISNUMBER(FIND(MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1 ))),A1:A6))))

or if the above Max formula is in B7
=SUM(--(ISNUMBER(FIND(MAX(VALUE(B7)),A1:A6))))

which should return a count of the cells that contain that highest digit.

All above formulas array entered. Also, all cells in the range must have
six
digits. So a value like 1234 should be a string '001234. Not sure how to
cater for variable length numbers.

If I correctly understand what's required (?) this seems to work, but
better
double check!

Regards,
Peter T

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/





--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Vasant Nanavati
Count of FIRST Digit
 
Hi Peter:

I don't think your formula works. I had tried that approach, but the 2
arrays seemed to confuse Excel. That's why I had to include 6 separate
conditions. But maybe I'm not applying it correctly ... as Tom says, I
always reserve the right to be wrong <g.

--

Vasant


"Peter T" <peter_t@discussions wrote in message
...
Hi Darren,

Try following to return the highest digit found in any of the six digits
of
any cell in the range:

=MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))

Then combine with Bob's formula
=SUM(--(ISNUMBER(FIND(MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1 ))),A1:A6))))

or if the above Max formula is in B7
=SUM(--(ISNUMBER(FIND(MAX(VALUE(B7)),A1:A6))))

which should return a count of the cells that contain that highest digit.

All above formulas array entered. Also, all cells in the range must have
six
digits. So a value like 1234 should be a string '001234. Not sure how to
cater for variable length numbers.

If I correctly understand what's required (?) this seems to work, but
better
double check!

Regards,
Peter T

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Vasant Nanavati
Count of FIRST Digit
 
"Darren Hill" wrote in message
news:op.sq69490red89cl@omega...
You're a genius!

Hardly; it's not a very elegant solution. But hey, as long as it does the
job ... ;-)

--

Vasant

Bob Phillips[_6_]
Count of FIRST Digit
 
Darren,

Correct me if I am wrong but this doesn't seem right to me. This counts the
number of rows that contain the largest of all digits in all rows. In your
example data that was 4, and it occurred in 1 row, so this returns 1. But I
thought you wanted to count how many rows had no digit greater than 1, how
many with no digit greater than 2, etc. This formula cannot count the 1's or
2's or 3's. Vasant's formula gave you this, but is inelegant (apologies
Vasant :-)).

Please confirm, because the problem that I thought you posed is far more
interesting, especially if the numbers are variable length.#

Thanks

Bob

"Darren Hill" wrote in message
news:op.sq7abqu4ed89cl@omega...
Thanks for the response. Looks like Vasant Nanavati beat you to the
no-prize by mere minutes. Your method makes it easier for me to understand
what's going on.
I'm off to study this, until just before my head explodes :)
Thanks!

Darren
PS: you did understand what was needed - all my cells did have exactly 6
digits, formatted as text.

On Mon, 23 May 2005 00:06:50 +0100, Peter T <peter_t@discussions wrote:

Hi Darren,

Try following to return the highest digit found in any of the six digits
of
any cell in the range:

=MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))

Then combine with Bob's formula
=SUM(--(ISNUMBER(FIND(MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1 ))),A1:A6))))

or if the above Max formula is in B7
=SUM(--(ISNUMBER(FIND(MAX(VALUE(B7)),A1:A6))))

which should return a count of the cells that contain that highest
digit.

All above formulas array entered. Also, all cells in the range must have
six
digits. So a value like 1234 should be a string '001234. Not sure how to
cater for variable length numbers.

If I correctly understand what's required (?) this seems to work, but
better
double check!

Regards,
Peter T

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So, when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted
only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/





--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Peter T
Count of FIRST Digit
 
Looks like Vasant Nanavati beat you to the
no-prize by mere minutes.

So I see :-). I was busy working out mine at the time, didn't see his when I
posted. Our methods or rather what they do are slightly different. His
counts cells that contain the target value, mine counts cells that include
the highest digit found in the range.

PS: you did understand what was needed - all my cells did have exactly 6
digits, formatted as text.

No not really, namely are you looking to count cells that contain a
particular digit, or only those that include the highest digit. However the
formula I posted should work with both text & numbers, providing length is
six digits.

Regards,
Peter T

Vasant Nanavati
Count of FIRST Digit
 
"Bob Phillips" wrote in message
...
Vasant's formula gave you this, but is inelegant (apologies Vasant :-)).

Too late for apologies, Bob; I'm already deeply offended <vbg. Actually, I
myself described my solution as "clunky" and "not very elegant."

Seriously, now I'm interested in finding out whose interpretation of the
issue was correct!

Regards,

Vasant

Peter T
Count of FIRST Digit
 
Vasent & Bob,

Didn't see either of your posts when I posted follow-up to Darren.

I don't think your formula works. I had tried that approach, but the 2
arrays seemed to confuse Excel. That's why I had to include 6 separate
conditions.

If Darren wants what you have provided then you right me wrong <g

Bob, you have interpreted the problem same way as Vasent, and looking again
I suspect you are both right. As for the varying length - that's a challenge
I will leave to you :-)

Regards,
Peter T

Signing off - back tomorrow

Darren Hill[_3_]
Count of FIRST Digit
 
Peter asked (below):
No not really, namely are you looking to count cells that contain a
particular digit, or only those that include the highest digit. However
the
formula I posted should work with both text & numbers, providing length
is
six digits.

I was trying to count all occurrences of the highest value in a cell. So,
Vasant's method was the correct one for my purposes. I thought your method
was the same one, broken down into nice, slightly less incomprehensible
steps. I was wrong (no surprise there!).
Thanks for trying, though. Every example of array formulas gets me
slightly closer to understanding how to use them myself (or so I keep
telling myself).

On Mon, 23 May 2005 01:01:18 +0100, Peter T <peter_t@discussions wrote:

Looks like Vasant Nanavati beat you to the
no-prize by mere minutes.

So I see :-). I was busy working out mine at the time, didn't see his
when I
posted. Our methods or rather what they do are slightly different. His
counts cells that contain the target value, mine counts cells that
include
the highest digit found in the range.

PS: you did understand what was needed - all my cells did have exactly 6
digits, formatted as text.

No not really, namely are you looking to count cells that contain a
particular digit, or only those that include the highest digit. However
the
formula I posted should work with both text & numbers, providing length
is
six digits.

Regards,
Peter T





--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Darren Hill[_3_]
Count of FIRST Digit
 
Bob, no doubt unable to sleep without finding the answer, asked:
Please confirm, because the problem that I thought you posed is far more
interesting, especially if the numbers are variable length.#

Yes, you're right. Vasant's approach was the correct one (so he keeps the
no-prize!). :)
The numbers are fixed length (6 digits) by the way.

Darren

On Mon, 23 May 2005 00:59:04 +0100, Bob Phillips
wrote:

Darren,

Correct me if I am wrong but this doesn't seem right to me. This counts
the
number of rows that contain the largest of all digits in all rows. In
your
example data that was 4, and it occurred in 1 row, so this returns 1.
But I
thought you wanted to count how many rows had no digit greater than 1,
how
many with no digit greater than 2, etc. This formula cannot count the
1's or
2's or 3's. Vasant's formula gave you this, but is inelegant (apologies
Vasant :-)).

Please confirm, because the problem that I thought you posed is far more
interesting, especially if the numbers are variable length.#

Thanks

Bob

"Darren Hill" wrote in message
news:op.sq7abqu4ed89cl@omega...
Thanks for the response. Looks like Vasant Nanavati beat you to the
no-prize by mere minutes. Your method makes it easier for me to
understand
what's going on.
I'm off to study this, until just before my head explodes :)
Thanks!

Darren
PS: you did understand what was needed - all my cells did have exactly 6
digits, formatted as text.

On Mon, 23 May 2005 00:06:50 +0100, Peter T <peter_t@discussions wrote:

Hi Darren,

Try following to return the highest digit found in any of the six
digits
of
any cell in the range:

=MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))

Then combine with Bob's formula
=SUM(--(ISNUMBER(FIND(MAX(VALUE(MID(A1:A6,{1,2,3,4,5,6},1 ))),A1:A6))))

or if the above Max formula is in B7
=SUM(--(ISNUMBER(FIND(MAX(VALUE(B7)),A1:A6))))

which should return a count of the cells that contain that highest
digit.

All above formulas array entered. Also, all cells in the range must
have
six
digits. So a value like 1234 should be a string '001234. Not sure how
to
cater for variable length numbers.

If I correctly understand what's required (?) this seems to work, but
better
double check!

Regards,
Peter T

"Darren Hill" wrote in message
news:op.sq6t3jcved89cl@omega...
Thanks, that's great.
One extra featu
If I have data like:

100200

I need to be able to check only the highest digit in a cell. So,
when I
perform this search, and count the "1"s, the above cell shouldn't be
included. On this version of the count, this cell should be counted
only
when I search for "2".
Is there a way to modify the formula for this?



On Sun, 22 May 2005 18:13:47 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

Something like:

=SUM(--((NOT(ISERROR(FIND(1,A1:A6))))))

entered as an array formula with <Ctrl <Shift <Enter.

No doubt there are many other and better ways.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/





--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/





--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Darren Hill[_3_]
Count of FIRST Digit
 
On Mon, 23 May 2005 00:50:24 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

"Darren Hill" wrote in message
news:op.sq69490red89cl@omega...
You're a genius!

Hardly; it's not a very elegant solution. But hey, as long as it does the
job ... ;-)

Okay, if it makes you happier, you're not a genius! :)
I don't mind it's clumsy inelegance (heh), it does a damn fine job.

Darren

Vasant Nanavati
Count of FIRST Digit
 
"Darren Hill" wrote in message
news:op.sq7e6ehned89cl@omega...
Vasant's method ... broken down into nice, slightly **less
incomprehensible**
steps.

Gee, thanks (I think!).

Every example of array formulas gets me
slightly closer to understanding how to use them myself (or so I keep
telling myself).

It took me years to figure out array formulas ... they used to give me a
splitting headache. It was only thanks to Myrna Larson and Bill Manville
(back in the old CompuServe days) that I finally started understanding them.

--

Vasant

Darren Hill[_3_]
Count of FIRST Digit
 
On Mon, 23 May 2005 02:28:58 +0100, Vasant Nanavati <vasantn *AT* aol
*DOT* <com wrote:

"Darren Hill" wrote in message
news:op.sq7e6ehned89cl@omega...
Vasant's method ... broken down into nice, slightly **less
incomprehensible**
steps.

Gee, thanks (I think!).

:)
I was commenting on the difficulty of understanding array formulas not
your, um, oh so elegant code. Honest.

Every example of array formulas gets me
slightly closer to understanding how to use them myself (or so I keep
telling myself).

It took me years to figure out array formulas ... they used to give me a
splitting headache. It was only thanks to Myrna Larson and Bill Manville
(back in the old CompuServe days) that I finally started understanding
them.

I'm still very much in the splitting headache stage. I do remember once
thinking, "this is just so tough, I'll spend some time working out how to
use Sumproduct instead." <insert the laughter of one who has been driven
beyond the edge of madness here, trailing off into pitiful whimpering

Darren

Bob Phillips[_6_]
Count of FIRST Digit
 
Hi Peter,

"Peter T" <peter_t@discussions wrote in message
...
Bob, you have interpreted the problem same way as Vasent, and looking
again
I suspect you are both right. As for the varying length - that's a
challenge
I will leave to you :-)

I think the varying length is the easy bit, just pad it with trailing
zeroes. The bit I can't get around is to array each cell to get the max
digit, then array down the rows. I have had at least 10 brilliant ideas so
far, all rubbish :-)

Time for the next 10 ....

Regards

Bob

Bob Phillips[_6_]
Count of FIRST Digit
 

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
"Bob Phillips" wrote in message
...
Vasant's formula gave you this, but is inelegant (apologies Vasant :-)).

Too late for apologies, Bob; I'm already deeply offended <vbg. Actually,
I
myself described my solution as "clunky" and "not very elegant."

Strange, you name doesn't suggest Italian ancestry :-)

Seriously, now I'm interested in finding out whose interpretation of the
issue was correct!

Yeah, me too. I think our interpretation is a darn sight harder, but a more
interesting, problem.

Peter T
Count of FIRST Digit
 
Morning Bob,

Yeah this is kinda frustrating. Obviously I know now my offering was not
correct in terms of what was required, I was never confident it was. However
it does do as stated.

Turning to Vasent's correct but big formula (can't say clunky!), I'm sure it
must be possible to reduce this.

Rather than using F9, in a row of six cells I've array entered

=--(ISNUMBER(FIND($B$1,A1:A6)))

and in an adjacent block of 6x6 cells array entered

=--((VALUE(MID(A1:A6,{1,2,3,4,5,6},1)))<=$B$1)

If all 7 cells in the row are 1, then count. I can see the whole picture
right in front of me - but I can't see the solution )-:

Regards,
Peter T


"Bob Phillips" wrote in message
...
Hi Peter,

"Peter T" <peter_t@discussions wrote in message
...
Bob, you have interpreted the problem same way as Vasent, and looking
again
I suspect you are both right. As for the varying length - that's a
challenge
I will leave to you :-)

I think the varying length is the easy bit, just pad it with trailing
zeroes. The bit I can't get around is to array each cell to get the max
digit, then array down the rows. I have had at least 10 brilliant ideas so
far, all rubbish :-)

Time for the next 10 ....

Regards

Bob

Vasant Nanavati
Count of FIRST Digit
 
"Bob Phillips" wrote in message
...
The bit I can't get around is to array each cell to get the max
digit, then array down the rows. I have had at least 10 brilliant ideas so
far, all rubbish :-)

Been there; done that on other problems in the past, so I didn't waste much
time on that approach this time around. I don't think Excel can handle a
horizontal array and a vertical array in the same array formula in a
sequential manner.

Besides, I'm sure Harlan can come up with a solution using MMULT or some
such, but it's way beyond my limited capabilities!

Regards,

Vasant

Bob Phillips[_7_]
Count of FIRST Digit
 
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
"Bob Phillips" wrote in message
...

Been there; done that on other problems in the past, so I didn't waste
much
time on that approach this time around. I don't think Excel can handle a
horizontal array and a vertical array in the same array formula in a
sequential manner.

http://www.dicks-blog.com/archives/2...enge/#comments -
see Bernie's response. Doesn't apply here though AFAICS

Vasant Nanavati
Count of FIRST Digit
 
Hi Bob:

Yes, I've seen Bernie's solution and it is brilliant, but I think that
formula contains only vertical arrays (I've forgotten all my vector math, so
I probably don't know what I'm talking about).

In our particular problem, we have a vertical array (the rows) and a
horizontal array (the digits). Coincidentally, in the example, each of the
arrays had 6 elements, but of course that doesn't hold when you increase the
number of rows. That's why I applied brute force to the fixed-size
horizontal array.

But I'm sure there's a better solution out there, and I'd love to be
educated!

Regards,

Vasant


"Bob Phillips" wrote in message
...
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
"Bob Phillips" wrote in message
...

Been there; done that on other problems in the past, so I didn't waste
much
time on that approach this time around. I don't think Excel can handle a
horizontal array and a vertical array in the same array formula in a
sequential manner.


http://www.dicks-blog.com/archives/2...enge/#comments -
see Bernie's response. Doesn't apply here though AFAICS

Peter T
Count of FIRST Digit
 
In our particular problem, we have a vertical array (the rows) and a
horizontal array (the digits). Coincidentally, in the example, each of the
arrays had 6 elements, but of course that doesn't hold when you increase
the
number of rows.

I'm sure your right, wish I had heeded your advice first time you said
similar!

Clutching at straws and thinking aloud - it's possible in vba to extract a
row or column from a 2d array and assign to a 1d array using App Index. Do
you see any way to do similar here.

Eg, the following array entered into a column of cells, 6 in the example,
looks right, assuming I've correctly understood the problem this time
around!

=--(SUM(INDEX(--(VALUE(MID(A1:A6,{1,2,3,4,5,6},1))<=$B$1),ROW(A1:A 6)))+ISNUM
BER(FIND($B$1,A1:A6))=7)

Appears to produce a 1 in each row that should be counted. This is along the
lines of what I mentioned in my previous to Bob, namely if 6 + 1 true's in a
row then count. But I can't figure how to "count" this into a single
formula.

Regards,
Peter T

Vasant Nanavati
Count of FIRST Digit
 
Hi Peter:

"Peter T" <peter_t@discussions wrote in message
...
Appears to produce a 1 in each row that should be counted. This is along
the
lines of what I mentioned in my previous to Bob, namely if 6 + 1 true's in
a
row then count. But I can't figure how to "count" this into a single
formula.

Exactly. And therein lies the problem. As I said, *perhaps* this can be done
with matrix multiplication (MMULT) but I don't have a large enough supply of
aspirin to try and explore that option! I think I'll leave it as an exercise
for Harlan <g.

Regards,

Vasant


All times are GMT +1. The time now is 07:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com