IF, AND, OR whats?

[Bruce McCausland]

I'm getting wrapped around the axel trying to test multiple conditions in a little time-card spreadsheet. I'm certain someone on this board can make short shrift of the problem and I would welcome the help.

Column AT is used for messages to draw attention to exceptional situations. I'm testing for two possible exceptional situations that may arise independantly or together. The combinations are:

• If the value in any of cells F7, J7, N7, or R7 > 6.0 the message in AT7 is "NB" (in bold red).
  If the value in cell AR7 > 8.5 the message in AT7 is "OT"
  If both are true ( F7 or J7 or N7 or R7 > 6.0 and AR7 > 8.5 the message in AT7 is "NB ... OT"

I can write the OR for the first condition but I don't know how to AND it with the second condition to handle when both conditions are true.

 

[dave3009]

=IF(OR(F7>6, J7>6, N7>6, R7>6), "NB", if(AR7>8.5, "OT", IF(AND(OR(F7>6, J7>6, N7>6, R7>6), AR7>8.5), "NB ... OT")))

Ought to do the trick, then just select Bold and Red as your format

KR

Dave

 

[mikerickson]

=IF(OR(F7>6,J7>6,N7>6,R7>6,AR7>8.5),IF(OR(F7>6,J7>6,N7>6,R7>6),"NB" &IF(AR7>8.5," ... OT",""),"OT"),"")

That formula should get you the value. Bolding and coloring cannot be set from a spreadsheet formula. Either set the cell permenantly Bold Red or use conditional formatting.



P.S. for the OT condition, do you mean > or >=

 

[Bruce McCausland]

Dave - I tried your solution but it didn't work because (I think) Excel stops evaluating when it hits a TRUE condition. It would not find when BOTH conditions were TRUE (No Break and OverTime). I was about to rearrange the statement to test both conditions for TRUE first when Mike's solution arrived.

I'm not sure what Mike's solution is doing exactly, (as I said earlier, I get wrapped around the axel trying to decipher these things ... but I'm learning), but I tested it with all conditions and it works flawlessly.

Thanks, Mike ... and thank you both for your rapid responses!

B

 

[mikerickson]

You're welcome.

 

[barry houdini]

A little shorter, I think....

=IF(OR(F7>6,J7>6,N7>6,R7>6),IF(AR7>8.5,"NB ... OT","NB"),IF(AR7>8.5,"OT",""))