Count of FIRST Digit

[Darren Hill]

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

 

[Darren Hill]

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.

 

[Vasant Nanavati]

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 Nanavati]

You're a genius!

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

 

[Bob Phillips]

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

 

[Peter T]

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]

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]

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]

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).

 

[Darren Hill]

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

 

[Darren Hill]

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]

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.

 

[Darren Hill]

Gee, thanks (I think!).

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

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]

Hi Peter,

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]

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]

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

 

[Vasant Nanavati]

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]

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/2005/04/20/formula-challenge/#comments -
see Bernie's response. Doesn't apply here though AFAICS

 

[Vasant Nanavati]

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


http://www.dicks-blog.com/archives/2005/04/20/formula-challenge/#comments -

 

[Peter T]

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:A6)))+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