Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bruce
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dav
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Vlookup array selection Scott269 Excel Worksheet Functions 2 January 30th 06 06:29 PM
Question to Bob Phillips (or whoever...) vezerid Excel Worksheet Functions 5 December 11th 05 12:44 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"