# IF, AND, OR whats?

#### Bruce McCausland

##### New Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### dave3009

##### Well-known Member
=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

##### MrExcel MVP
=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

##### New Member
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

You're welcome.

#### barry houdini

##### MrExcel MVP
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",""))

Replies
2
Views
145
Replies
5
Views
572
Replies
5
Views
273
Replies
0
Views
249
Replies
0
Views
490

1,191,386
Messages
5,986,315
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back