OLD PROBLEM without SOLUTION

[ytayta555]

A good day !

I have this function :


=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) ;

this function let me to know if every one of the 5 COUNT functions
have the result the value <=1;
(this is what I need !!, not to count everyone of the five count
functions the value only <=1!!!; it must work like Count functions,
not like a Countif function {Countif <=1 }) ;

The last result of this this function I want to show me if all 5
Count functions result is <=1 ;

This function work perfect for me , but I need very very strong a
perfect equivalent for this function (why I need this , it's a long
story ...) ,made with another functions .


I get some variants , but them doesn't work ;(but I think the solution
is somewhere here arround) :


=COUNT(INDEX(MATCH(ROW(B4:AH83),
{4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0)/
ISNUMBER(B4:AH83),0))
or


=SUMPRODUCT(ISNUMBER(MATCH(ROW(B4:AH83),
{4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0))*
ISNUMBER(B4:AH83))
or


=SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1))



Can you help me to get a function which do the same thing like this
function ?

Thanks very much for your time .

 

[Pete_UK]

You STILL have not explained why your first formula is not suitable
for you - you said it works perfectly for you.

Pete

 

[ytayta555]

You STILL have not explained why your first formula is not suitable
for you - you said it works perfectly for you.

Thanks , mr. Pete

I'ts a long long story ! ...
Imagine that you need to make / built milions of this kind of
functions ! milions of functions ...
imagine this thing ! the references in this functions will be in
combinatoric order ;
I use an VBA macro to built this kind of functions (I found the
resolve after many
months of search .....)
if I'll find another equivalent for this function I'm shure Ill can
built them more easy

 

[Harlan Grove]

I have this function  :

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

This is a formula involving many functions, not one function.

this function let me to know if every one of the 5 COUNT functions
have the result the value <=1; ...
The last result of this this function I want to show me if all  5
Count functions result is  <=1 ;

This function work perfect for me , but I need very very strong a
perfect equivalent for this function . . .

...

So you have a range like B1:F5 from which you want to count numbers in
each column separately for rows 1, 3 and 5 only. You could try array
formulas like

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1;0;1;0;1})<=1)

[NOTE: US standard syntax - comma (,) as argument separator, semicolon
( as ROW separator in array constants. Change as needed for your
locale's syntax.]

 

[ytayta555]

Thanks very much , I'll work and I'll comunicate later what result I
get ;
please very much to watch the topic in future , you gived me a wey
very
important for me .

 

[ytayta555]

I don't understand why function ISNUMBER must to be use , I think is a
mistake ;
I tried this formula :
=AND(COUNT(MATCH(ROWS(B1:F5);{1;3;5})>=1)) , but when you work to
formula excel show you
that the range in formula is B15 , not like in my first formula
B1;3;5 ; C1;3;5 ; D1;3;5; E1; .......etc .

I have in workbook in cell B1 value = 1 , C3 =1 , D5= 1000 (for
eg.) .When I add in cell D1 value = 5 , my
function become FALSE (what it is right) , and this formula remane
still TRUE (what it is wrong ).

I think the solution is somewhere arround , in this steps :

AND function first , then COUNT , then the combination of ROW and
MATCH functions ,
or another functions which give you the references identically for
this 5 Count functions ;
really , I don't understand what can do there ISNUMBER function , but
I don't know ;

Can anybody please to work for this function , please very much , I
tried in hundred weys , but
it still don't work .

Thank you very much for your time

 

[ytayta555]

Sorry , I forget to say I use excel 2003 ,
Romania .

 

[ytayta555]

ANY IDEEA HERE , PLEASE ? ! ...

 

[Gord Dibben]

Re-post your original question.

Most of us are not going to download old messages just to find yours.


Gord Dibben MS Excel MVP

 

[Harlan Grove]

Re-post your original question.

Most of us are not going to download old messages just to find yours.

....

Use Google Groups.

 

[Harlan Grove]

I don't understand why function ISNUMBER must to be use , I think
is a mistake ;

No. You're mistaken. The formula I proposed,

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1;0;1;0;1})<=1)

*sums* rather than counts. It sums 1's in the separate columns of rng
when cells in rng contain numbers, and 0's when cells in rng don't
contain numbers. There's no way to make this MMULT approach work
without using ISNUMBER.

I tried this formula :
=AND(COUNT(MATCH(ROWS(B1:F5);{1;3;5})>=1)) , but when you work to
formula excel show you that the range in formula is B15 , not
like in my first formula B1;3;5 ;  C1;3;5 ; D1;3;5; E1; .......etc.

...

I was clear that I was using US formula syntax, which means my formula
used commas (,) rather than semicolon ( as argument separator, and
semicolons ( rather than something else as row separators in array
constants. I don't recall what the corresponding character is for
European locales which use semicolons as argument separators, but you
could find out by typing BUT NOT ENTERING the formula =ROW(1:10) and
pressing [F9] - the character separating the numbers would be your
locale's array constant row separator.

The reason the formula above doesn't work for you is that in your
locale's formula syntax, the array constant {1;3;5} is the equivalent
of {1,3,5} in US locale formula syntax. I mean {1;3;5} in US locale
formula syntax, and IT'S UP TO **YOU** TO TRANSLATE THIS INTO YOUR OWN
LOCALE'S FORMULA SYNTAX.

If you can't figure out your own locale's formula syntax, change your
formula above to

=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))>=1))

 

[ytayta555]

WAW

Thank you very very much mr. Harlan Grove
Now I am working and I'll post my results in this night or
tomorrow .
Thank you so much , this formula is very very important for me .

 

[ytayta555]

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1;0;1;0;1})<=1)


What I must write instead of rng ? ( (ROW(rng) and ISNUMBER(rng) ?

 

[Harlan Grove]

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1;0;1;0;1})<=1)

What I must write instead of  rng ? ( (ROW(rng) and  ISNUMBER(rng)  ?

rng was a placeholder for a range address. Replace it with B1:F5 from
your original posting or some other range address. If you change the
number of rows, you'll need to change the array constant so that it
has the same number of rows.

 

[ytayta555]

I, m so sorry , I cann't get the good result yet , but I'l try
and try , because , if you believe me or not , this formula
is the problem of my life ;
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 ;

My function work , but , this is .....I really need a perfect
equivalent
for it ; to explain why , I must explain step by step how I built
milions of functions with references in combinatoric order with
the help of VBA ;
another thing , with this function :
=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) , one only workbook have 200 Mb , what is
enormous size for hundred and hundred of workbooks I have them
need ...

 

[Harlan Grove]

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.

 

[ytayta555]

Understood: one formula to determine whether ALL columns in some range

IT'S JUST AN AMASING AND GREAT ANSWER Mr . Harlan Grove !
I have no words thinking to your time ...

I apologise , I haven't time to work today , but I'll work and tell a
little later my results ;
please very much again to watch the topic in next few days ;
if it is possible , and I'm not impolite , I'd like and think very
constructive and
highlighting to can send you an workbook with my formula and example
of what formula must do ,
to an e-mail ; my personal e-mail is (e-mail address removed) ;
I'm shure - with the great way you gived me - , that I'm very very
near by the right function !!

Many thanks again , I think I'ts a rare answer ! ...

 

[ytayta555]

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]

Incredible news : I tested all formulas , and ALL work great .
I cann't still believe that I get what I needed so strong ,by so
along time .....

In all formulas , my rng will be B1:F90 , but my actually problem
which it appears in my front is
how to built the references in combinatoric order , because most of
my future functions will have references not in odd order , and 10
references(rows).

examples :
________________________________________________________________
1). If I get the perfect equivalent for this formula , I believe I'll
understand how to work for changes of references in combinatoric
order :

=AND(COUNT(b1;b3;b5;b6;b7;b10;b12;b15;b18;b38)<=1;COUNT(c1;c3;c5;c6;c7;c10;c12;c15;c18;c38)<=1;COUNT(d1;d3;d5;d6;d7;d10;d12;d15;d18;d38);COUNT(e1;e3;e5;e6;e7;e10;e12;e15;e18;e38);COUNT(f1;f3;f5;f6;f7;f10;f12;f15;f18;f38)<=1)

I understand I must work with *MOD(ROW( rng )-MIN(ROW( rng ))+1;
2 ) , with MIN function , but I don't know yet how
________________________________________________________________
2). In a perfect world , a perfect dream is to make to work this
formula :

=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))>=1))
(My rng is in all formulas B1:F90 , and the array is very easy to work
with it for make references in combinatoric order ) .

for example , we take b1:f5 values from your first example :
Col: | B C D E F
_______________________________________________________________
Row1| A #DIV/0! B #DIV/0!
Row2| 5 6 TRUE 6 C
Row3| #DIV/0! 6 1 D E
Row4| 6 F #DIV/0! G H
Row5| 3 2 5

When I delete Range(C33) , your formula :
{=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*MOD(ROW(B1:F5);
2))<=1)}
, which work perfect , became TRUE , what it is wright !
the formula above , still remane FALSE , it don't work !!
I tryed and so , but the same negative result :
{=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5})<=1)))}
________________________________________________________________
3). Question , only and only if it is posible :

I have placeholder rng for all f ormulas :B1:F90 ! Can work the
equivalent for this function :
=AND(COUNT(b1;b3;b5;b6;b7;b10;b12;b15;b18;b38)<=1;COUNT(c1;c3;c5;c6;c7;c10;c12;c15;c18;c38)<=1;COUNT(d1;d3;d5;d6;d7;d10;d12;d15;d18;d38);COUNT(e1;e3;e5;e6;e7;e10;e12;e15;e18;e38);COUNT(f1;f3;f5;f6;f7;f10;f12;f15;f18;f38)<=1)
with rng B1:F90 or only with range B1:F38 ?

only if it is posible this .....
________________________________________________________________
4). Another variants , I'm not shure which will work :

=AND(COUNT(INDEX(MATCH(ROW(B1:F5), {1;3;5},0)/ ISNUMBER(B1:F5),0)))
or
=SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1))
or
=SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1;3;5},0))* ISNUMBER(B1:F5))
________________________________________________________________

I have no words to apreciate your time and knowledge used for me

 

[ytayta555]

Mr. Harlan Grove (and not only) can you take a look for this my last
problem ?
1). and 2).

Many thanks for this incredible help