Permutations of an array element < to a value
 

I want to test whether any of my array elements are less than a value and
have entered the following formula. If they are the result returns TRUE.

This is what I am working with so far;

{=A1:A3<B1}

Basically this is intended to return true if any of A1, A2 or A3 is less
than B1.

This works for combinations where A1 is less than B1

Eg. A1<B1, A2B1, A3B1 or A1<B1, A2B1, A3<B1

but it returns false if A1B1

Eg. A1B1, A2<B1, A3<B1

I want if to return true for any permutation of A* < B1

Any help appreciated

Bruce

Permutations of an array element < to a value
 

I think the array (3 values) returns the result of the first value eg
whatever is in the first cell, if you edit the array and select all of
it and press F9 you will see a combination of True and false for each
of the 3 conditions

=MAX((A1:A3<$B$1)*1) entered as an array will return a 1 if any of the
conditions is true, which is what u want, but I have not given any
thought as to how to turn this into a true.

Obviously 0 if none of the conditions is true


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=506716

Permutations of an array element < to a value
 

Bruce
If the following is true
A1B1, A2<B1, A3<B1
The formula
{=A1:A3<B1} will return
{FALSE;TRUE;FALSE}
but as indicated will display the first element

If you wish to know if one or more TRUE conditions are returned
{=OR(A1:A3<B1)} will do

the other option is to first return the smallest value from the range and
then do one comparison.
This removes the need for an Array Formula
eg. =Min(A1:A3)<B1

Dav's formula worked by retuning the max of {0;1;0} which is in a way
mimicking the built in OR function

hth RES

Permutations of an array element < to a value
 

=SUMPRODUCT(--(A1:A3<B1))0 returns TRUE or FALSE if on value in range A1:A3
is less than B1.


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bruce" wrote in message
...
I want to test whether any of my array elements are less than a value and
have entered the following formula. If they are the result returns TRUE.

This is what I am working with so far;

{=A1:A3<B1}

Basically this is intended to return true if any of A1, A2 or A3 is less
than B1.

This works for combinations where A1 is less than B1

Eg. A1<B1, A2B1, A3B1 or A1<B1, A2B1, A3<B1

but it returns false if A1B1

Eg. A1B1, A2<B1, A3<B1

I want if to return true for any permutation of A* < B1

Any help appreciated

Bruce