Y

#### ytayta555

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

ytayta555 said: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;E5)<=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 ! ...