Multiple IF AND Statments with 2 Possible Outputs

Question

Jerry, to answer your first comment, no, the outcome should be a PASS because B5 has a value of -12790 and C5 has a value of -12790 making their absolute max difference 0. When I asked before, I changed C5 to 0 and left B5 at -12790 which made the absolute max difference 12790. This case should be a FAIL. I apologize for not explaining in more detail. It is a bit tricky to explain, but I really do appreciate your help with this. I think we are on the right track and I have been trying for a very long time to figure this formula out when I have some spare time. Let me try to explain what I am trying to accomplish here. Row 3 has 4 cells labeled as Lw_Send / Lw_Return / Up_Send / Up_Return. These 4 hands are part of one robot and each hand must be taught individually. Each hand has 4 movable axis or joints that can be adjusted. The 4 joints are X-axis (forward and reverse movement), Z-axis (vertical movement), Y-axis (horizontal or left and right movement) and Theta axis (rotational movement). The only axis’s I am concerned with in this problem are the Y and theta axis’s. These two axis have a direct effect on one another meaning if I shift the Y-axis one direction, it will shift my theta axis and vice versa. This is why we have rules when we are adjusting the Y-axis and theta axis. When I am done with teaching the Lw_Send hand (B5:B9), I start teaching the Lw_Return hand (C5:C9). Before I can complete my teaching of this hand, I must compare my theta and Y-axis teach values with my teach values of the 1st hand, the Lw-Send hand. For example, if B5 has a teach value of -12790 and C5 has a teach value of -12792, this means my absolute max difference between the two hand theta axis’s is 2. With the theta difference being 2, when I look at the salmon colored delta table, it says that my Y-axis teach difference between the 1st and 2nd hand must be an absolute max difference of 15-20.This means that if B5 is -75506 then cell C5 can be within the range of [-75491 to -75486] or [-75521 to -75526]. The ultimate goal is to of course have no deviation between the hands. This is almost never possible. Once those two hands are done, I must apply this same rule for the third and fourth hand. As I continue to teach each hand, I must compare my theta and horizontal teach values with that of all the previous hands. This is why it is very important to use the absolute max difference. One hand might be more positive and another hand might be more negative. Please let me know if this better explains what I am trying to accomplish. Again, I really do appreciate your help with this nightmare



Been beating my head against the wall trying to figure this one out. I'm trying to have multiple IF AND statements within a cell.

1.) the absolute max of B5:C5 must be less than or equal to 5 when the absolute max of B7:C7 is less than or equal to 2. Whether or not it meets the criteria will be displayed as PASS or FAIL. That's the first rule.

2.)The second rule in the same cell needs to be the absolute max of B5:C5 must be less than or equal to 4 when the absolute max of B7:C7 is between or equal to 3 through 8.

3.) The third rule needs to be the absolute max of B5:C5 must be less than or equal to 3 when the absolute max of B7:C7 is between or equal to 9 through 14.

4.) The fourth rule needs to be the absolute max of B5:C5 must be less than or equal to 2 when the absolute max of B7:C7 is between or equal to 15 through 20.

5.) The fifth rule needs to be the absolute max of B5:C5 must be less than or equal to 1 when the absolute max of B7:C7 is between or equal to 21 through 26.

6.) The sixth rule needs to be the absolute max of B5:C5 must be equal to 0 when the absolute max of B7:C7 is between or equal to 27 through 30.

Their is a snip of my spreadsheet below. If you look at the salmon colored table for delta Y and delta theta, you can see how the values in my rules apply to cells B5:C5 and B7:C7.

I would greatly appreciate any help :)

So far, here is what I've got for the first two rules:

=IF(ISBLANK(C5)," ",IF(OR(AND(MAX(ABS(B5:C5<=5))),(MAX(ABS(B7:C7<=2))),"PASS","FAIL",OR(MAX(ABS(B5:C5<=4))),(MAX(ABS(B7:C7<=8)))),"PASS","FAIL"))

Screenshot

Answer 1

Try this formula

=IF(ABS(B5-C5)<=6-MATCH(ABS(B7-C7),{0,3,9,15,21,27}),"Pass","Fail")

There may be some "edge cases" where that doesn't work - please test and let me know.

This formula uses the absolute difference of B7 and C7 in the MATCH function to find the value that needs to be met by the absolute difference of B5 and C5

Answer 2

Like I mentioned in my comment, I would use a lookup table:

If there is a 'match' from the table, then SUMPRODUCT will give a number above 0, thus will be 'Pass'.

The formula from the picture is as follows:

=IF(SUMPRODUCT((ABS(MAX(A11:B11))<=$B$3:$B$8)*(ABS(MAX(C11:D11))>=$C$3:$C$8)*(ABS(MAX(C11:D11))<=$D$3:$D$8))>0,"Pass","Fail")

Multiplication is equivalent to 'AND', so in the above, the 3 different conditions are being 'AND'-ed to each other. If all 3 passes on any row from the lookup table, then the formula returns 'Pass'.

---

EDIT: If you really meant 'max absolute' instead of 'absolute max', then you would need this formula instead (this one has to be an array entered formula entered using Ctrl+Shift+Enter):

=IF(SUMPRODUCT((MAX(ABS(A11:B11))<=$B$3:$B$8)*(MAX(ABS(C11:D11))>=$C$3:$C$8)*(MAX(ABS(C11:D11))<=$D$3:$D$8))>0,"Pass","Fail")

Answer 3

Something like this (ignoring the ISBLANK(C5) and concentrating on the different pairs of criteria)

=IF(OR(
          AND(MAX(ABS(B5:C5))>5, MAX(ABS(B7:C7))<=2),
          AND(MAX(ABS(B5:C5))>4, MAX(ABS(B7:C7))>=3, MAX(ABS(B7:C7))<=8)
      ),"FAIL","PASS")

Entered using Ctrl+Shift+Enter since it's an array formula.

Basically if any of the parts of the OR() evaluate to true then that triggers a FAIL result.

NOTE: when entering a complex formula you can use Ctrl+Enter in the formula bar to arrange and indent the formula across multiple lines: it makes it a lot easier to follow. Drag the bottom of the formula bar down so you can see the whole thing.