×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using and,or functions together in Xcel

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.
 

RE: Using and,or functions together in Xcel

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

RE: Using and,or functions together in Xcel

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

RE: Using and,or functions together in Xcel

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.

RE: Using and,or functions together in Xcel

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)

RE: Using and,or functions together in Xcel

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 ...

RE: Using and,or functions together in Xcel

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!

RE: Using and,or functions together in Xcel

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.

RE: Using and,or functions together in Xcel

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/
 

RE: Using and,or functions together in Xcel

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources