Using and,or functions together in Xcel 1

[jetboat]

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.

 

[swertel]

I tried downloading your sample file and when opening I get an error stating that it is corrupt. Can you try again?

--Scott

http://wertel.eng.pro

 

[jetboat]

Here it is hope it works!

 

[jberg]

Try this. When using AND / OR, you need to give it a logical test, e.g. less than, greater, etc. for each item. Your formula was resulting in a "TRUE" value and comparing it to your 1.5*B3 which is a "FALSE" answer. There may be another way or more efficient way, but I believe it is working.

Joel Berg

 

[nutte]

Break it down to a simple term and see what you get. This will lead you to your error.

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.

 

[zelgar]

I think the correct formula to do want he wants is the following:

If(sum(b4:b7)-max(b4:b7)>=1.5*b3,true,false)

 

[rb1957]

not quite since he wants any three of B4 to B7 ...
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 ...

 

[cowski]

Actually, zelgar's formula is pretty slick. You add up all 4, then subtract out the largest value which will give you the worst case scenario (smallest 3 added together).

A star for elegance!

 

[nutte]

Except that's not what jetboat is trying to do. I believe jetboat is trying to make sure each of the smallest three is less than 1.5*B3, not the sum of the smallest three.

 

[IDS]

Assuming nutte is right (and that's how I read it too), this formula will do the job:

=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/

 

[IDS]

Here's a much neater formula to do the same thing:

=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/