Permutations of an array element <> to a value

[Guest]

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, A2>B1, A3>B1 or A1<B1, A2>B1, A3<B1

but it returns false if A1>B1

Eg. A1>B1, A2<B1, A3<B1

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

Any help appreciated

Bruce

 

[Dav]

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

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

Obviously 0 if none of the conditions is tru

 

[Robert_Steel]

Bruce
If the following is true
A1>B1, 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

 

[Bernard Liengme]

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