Using and,or functions together in Xcel
Using and,or functions together in Xcel
(OP)
Hello, I am trying to use the and, or functions together but the results I am getting from my formula are opposite of what I need. Basically if any combination of cells B4-B7 taken 3 at a time is less than 1.5*B3 its true otherwise false. In my case its true but my formula is saying false. Some guidance would be greatly appreciated!
See attached xcell sheet.
See attached xcell sheet.





RE: Using and,or functions together in Xcel
--Scott
http://wertel.eng.pro
RE: Using and,or functions together in Xcel
RE: Using and,or functions together in Xcel
Joel Berg
RE: Using and,or functions together in Xcel
AND(B4,B5,B6)<1.5*B3 is your problem. It is not acting as you expect. The first term, AND(B4,B5,B6), does not yield a number. It yields a TRUE or FALSE. Now, you're asking if TRUE or FALSE is less than a numerical value, in this case 1.5*B3, or 18.
You need to say AND(B4<1.5*B3,B5<1.5*B3,B6<1.5*B3). Or, say MAX(B4,B5,B6)<1.5*B3.
RE: Using and,or functions together in Xcel
If(sum(b4:b7)-max(b4:b7)>=1.5*b3,true,false)
RE: Using and,or functions together in Xcel
4,5,6; 4,5,7; 5,6,7 ...
if((b4+b5+b6)>1,5*b3,true,if((b4+b5+b7)>1,5*b3,true,if((b5+b6+b7)>1.5*b3,true,false))) ...
i'm not sure of the grammar but maybe ...
if((b4+b5+b6)or(b4+b5+b7)or(b5+b6+b7)>(1.5*b3),true,false)
next the OP will ask for x combinations of y data ...
RE: Using and,or functions together in Xcel
A star for elegance!
RE: Using and,or functions together in Xcel
RE: Using and,or functions together in Xcel
=AND(OR(RANK(B4,B4:B7)=1,B4<B3*1.5),OR(RANK(B5,B4:B7)=1,B5<B3*1.5),OR(RANK(B6,B4:B7)=1,B6<B3*1.5),OR(RANK(B7,B4:B7)=1,B7<B3*1.5))
For each cell in B4 to B7 it checks if the number is either the biggest in that range (rank = 1), or if it is less than B3*1.5.
If that is true for all four cells it returns true, otherwise it returns false.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Using and,or functions together in Xcel
=LARGE(B4:B7,2)<B3*1.5
That finds the second biggest value in the range B4:B7 and returns TRUE if it is less than B3*1.5, or FALSE if not.
I had forgotten about the LARGE() and SMALL() functions, and when I looked up the help on RANK() Excel felt no need to remind me.
Did Excel once give a list of related functions whith function help screens, or did I imagine that?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/