Continuation to equivalent for formula - to Mr. Harlan Grove (and notonly...)

[ytayta555]

.....THIS IS A CONTINUATION FOR TOPIC ,,OLD PROBLEM without
SOLUTION ,, ....., see :
http://groups.google.ro/group/micro.../browse_thread/thread/25ce7b613b0a000b?hl=ro#

De la: Harlan Grove <[email protected]>
Data: Sun, 6 Jul 2008 16:12:21 -0700 (PDT)
Local: Lun 7 Iul 2008 02:12
Subiect: Re: OLD PROBLEM without SOLUTION

I, m so sorry , I cann't get the good result yet , but I'l try
and try , . . . I explain again : I need three functions in
formula to count normally (not like an countif <=1 function) , and
another function to show me if all of this three count functions
have the result <=1 ; ...
=AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D1;D3;D5)<=1;
COUNT(E1;E3;E­5)<=1;COUNT(F1;F3;F5)<=1)

...

Understood: one formula to determine whether ALL columns in some
range
(in this case B1:F5) had 0 or 1 numeric values in row 1, 3 and 5.


I'll try to use your locale's formula syntax.


The expression


ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2)


evaluates to an array of 1's and 0's with the same dimensions as
B1:F5
(so 5 rows and 5 columns), 1's when the corresponding cells in the
odd
numbered rows of B1:F3 (B1, B3, B5, C1, C3, C5, etc.) contain
numberic
values and 0's otherwise (so either corresponding cells in odd
numbered rows of B1:F5 don't contain numbers or for all cells in even
numbered rows).


For example, if B1:F5 contained


A #DIV/0! B #DIV/0!
5 6 TRUE 6 C
#DIV/0! 6 1 D E
6 F #DIV/0! G H
3 2 5


where B1, B5 and E5 are blank, D1, F1, B3 and D4 are error values, D2
is boolean value, and the other cells are either numeric or text
values, the formula


=ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2)


would return the array


0 0 0 0 0
0 0 0 0 0
0 1 1 0 0
0 0 0 0 0
0 1 1 0 1


so with 1's in odd numbered rows where the corresponding cells in
B1:F5 contained numeric values and 0's otherwise.


You want to check each column separately. The expression


TRANSPOSE(ROW(B1:F5))^0


returns a horizontal array of 1's where each row in the range B1:F5
has a corresponding column in the result.


The array formula


=MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2))


then returns the sum of each column of the array returned by the
expression ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2). With the sample data
from the previous example, the formula immediately above is
equivalent
to matrix multiplication of


1 1 1 1 1


as the left multiplicand by


0 0 0 0 0
0 0 0 0 0
0 1 1 0 0
0 0 0 0 0
0 1 1 0 1


as the right multiplicand, which returns the matrix product


0 2 2 0 1


This reflects that in the sample B1:F5 data, B1 is blank, B3 is an
error value and B5 is blank, so there are no numeric values in B1, B3
and B5, so the result array's first column entry is 0. C1 is text,
but
C3 and C5 both contain numbers, so 2 numbers in the odd numbered rows
of C1:C5, so the result array's second column entry is 2. And
similarly for the other columns.


Since columns C and D both have more than one numeric value in their
respective odd numbered rows, the array formula


=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;
ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2))<=1)


retruns FALSE. On the other hand, if B1:F5 contained


A 4 #DIV/0! B
C #DIV/0! D 1
#DIV/0! 3 TRUE
6 3 E 1
#DIV/0! FALSE F


with E1, B2, B3, E3, F4, D5 and E5 blank, D1, D2, C3 and B5 error
values, F3 and C5 boolean values, B1, F1, C2, E2, D4 and F5 text
values, and the remaining cells numeric values,


ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2)


would evaluate to


0 1 0 0 0
0 0 0 0 0
0 0 1 0 0
0 0 0 0 0
0 0 0 0 0


MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2))


would evaluate to


0 1 1 0 0


and so the array formula


=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;
ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2))<=1)


would evaluate to TRUE.


Next, B1:F5 just happens to begin in an odd-numbered row. Replacing
the literal range reference B1:F5 with the placeholder rng and
generalizing the MOD(..) term so that it always selects odd indexed
rows (e.g., 1st, 3rd, 5th, 7th, etc. rows of rng whether rng's
topmost
row is in an odd or even numbered row) gives


=AND(
MMULT(TRANSPOSE(ROW( rng ))^0;
ISNUMBER( rng )
*MOD(ROW( rng )-MIN(ROW( rng ))+1; 2 )
)<= 1
)


TRANSPOSE(ROW( rng ))^0 always returns a horizontal array of 1's
with
as many columns as rng has rows.


ISNUMBER( rng ) is an array of TRUE and FALSE boolean values of the
same size and shape as rng. When multiplied by the next term, Excel
converts the TRUE values to 1's and the FALSE values to 0's.


ROW( rng )-MIN(ROW( rng ))+1 is a vertical array of sequential
integers from 1 to ROWS(rng).


MOD(ROW( rng )-MIN(ROW( rng ))+1; 2 ) is a vertical array of 1's and
0's with 1's in odd indexed rows and 0's in even indexed rows.


The product of the ISNUMBER and MOD function calls is an array of the
same size and shape as rng with 1's correspond to cells in odd
indexed
rows of rng containing numeric values and 0's otherwise.


The MMULT function call returns a horizontal array of the sums of
each
of the separate columns of the array that's the product of the
ISNUMBER and MOD function calls.


MMULT(..)<=1 returns a horizontal array of TRUE's and FALSE's with
TRUE's for each entry in the MMULT(..) function call's result array
less than or equal to 1, so for those columns in rng with 1 or 0
numeric values in the odd indexed rows of rng.


The AND call returns TRUE if all columns of rng contain no more than
1
numeric value in the odd indexed rows.


You could change this to, for example, every fifth row (so rows
indexed 5, 10, 15, etc.) using the array formula


=AND(
MMULT(TRANSPOSE(ROW( rng ))^0;
ISNUMBER( rng )
*(MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) = 0 )
)<= 1
)


since the ROW( rng )-MIN(ROW( rng ))+1 expression is still a
vertical array of sequential integers from 1 to ROWS(rng), so the
expression MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) becomes a vertical
array cyclically repeating integers 1, 2, 3, 4, 0. The expression
(MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) = 0 ) is a vertical array of
TRUE's when the MOD(..) array result equals 0 and FALSE's otherwise.
When this is multiplied by the ISNUMBER function call's result, Excel
converts the TRUE's to 1's and the FALSE's to 0's. If rng were
B2:E19,
MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) = 0 would return


FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
TRUE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE


I've tested all these formulas. They *ALL* work as I've indicated. At
this point, if you can't get them to work, you'd be better off
seeking
someone who understands your own language but has better
comprehension
of English (and, better still, of Excel) who could help you with what
I've written.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I'TS AN AMASING AND GREAT ANSWER , Mr .Harlan Grove !
I have no words thinking to your time ! ...

I apologise , I haven't time today to work , but I please you again to
watch this topic
in next one or two few days , I'll write my results; if I'm not
impolite , and if it is possible ,
I'd like to have posibility to send you an workbook with some examples
of what this formula
must do [ I think this thing is very constructive for me and
highlighting for this problem ] ;
my e-mail is (e-mail address removed) ; I feel I'm very very near by
getting right formula ,
with the great way you gived me !
Thanks again ! I consider it's a rare answer ! ...

 

[ytayta555]

MY APOLOGISE , I use the original thread now ; this thread is closed