Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Vlookup array selection | Excel Worksheet Functions | |||
Question to Bob Phillips (or whoever...) | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |