IF AND OR Together

R

Robby19

Board Regular
Joined
Mar 19, 2018
Messages
227
I am trying to have a cell state Compliant or Non-Compliant based off other cells.

If TA Error = FALSE and Days to EAS - TRS Completion is greater than or equal to (>=) 180 show Compliant OR if Days to EAS is >= 180 show Compliant.

This is what I have so far, but it doesn't seem to work.
=IF(AND(OR([@[Days To EAS]=]=>=180),[@[TA Error]]=FALSE,[@[Days to EAS - TRS Completion]=]=>=180),"Compliant","Non-Compliant")

The TRS Compliance, where this formula is located, is sometimes displaying Non-Compliant when it should say Compliant, and vice versa.

[TABLE="width: 500"]
<tbody>[TR]
[TD]TA
[/TD]
[TD]TA COMP DATE
[/TD]
[TD]DAYS TO EAS
[/TD]
[TD]DAYS TO EAS - TRS COMPLETION
[/TD]
[TD]TA ERROR
[/TD]
[TD]TRS COMPLIANCE
[/TD]
[/TR]
[TR]
[TD]TA
[/TD]
[TD]18-APR-18
[/TD]
[TD]59
[/TD]
[TD]59
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#N/A
[/TD]
[TD][/TD]
[TD]73
[/TD]
[TD]43281
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#N/A
[/TD]
[TD][/TD]
[TD]237
[/TD]
[TD]43445
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi

How about =IF(OR(AND([@[TA ERROR]]=FALSE,[@[DAYS TO EAS - TRS COMPLETION]]>=180),[@[DAYS TO EAS]]>=180),"Compliant","Non-Compliant")

HTH
DK
 
Upvote 0
So I ran into another problem. The TA COMP Date is an Index Match formula. When I had tested this out originally, I had just "hand jammed" the date into that cell. However, now that it is pulling the date via Index Match from another tab in the spreadsheet, the formula doesn't work.

For example, if the Index Match under the TA COMP DATE is true and pulls the date, the TRS Compliance works. But if the INDEX Match does not pull a date, and instead shows an error, the TRS Compliance is blank.
 
Upvote 0
Hi

Not sure I understand the problem - the formula should return "Compliant" or "Non-Compliant" - have you modified it from what I provided previously?

DK
 
Upvote 0
Robby19 said:
So I ran into another problem. The TA COMP Date is an Index Match formula. When I had tested this out originally, I had just "hand jammed" the date into that cell. However, now that it is pulling the date via Index Match from another tab in the spreadsheet, the formula doesn't work.

For example, if the Index Match under the TA COMP DATE is true and pulls the date, the TRS Compliance works. But if the INDEX Match does not pull a date, and instead shows an error, the TRS Compliance is blank.
Click to expand...

Hi Robby19,

We need more information.

What you have in each column of the table? Value or formula? If formula, what formula?

Markmzz
 
Upvote 0
Robby19 said:
[TABLE="width: 500"]
<tbody>[TR]
[TD]TA
[/TD]
[TD]TA COMP DATE
[/TD]
[TD]DAYS TO EAS
[/TD]
[TD]DAYS TO EAS - TRS COMPLETION
[/TD]
[TD]TA ERROR
[/TD]
[TD]TRS COMPLIANCE
[/TD]
[/TR]
[TR]
[TD]TA
[/TD]
[TD]18-APR-18
[/TD]
[TD]59
[/TD]
[TD]59
[/TD]
[TD]FALSE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#N/A
[/TD]
[TD][/TD]
[TD]73
[/TD]
[TD]43281
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#N/A
[/TD]
[TD][/TD]
[TD]237
[/TD]
[TD]43445
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

TA - =INDEX(TRS_TA[TRAIN EVENT CODE],MATCH([@EDIPI],TRS_TA[EDIPI],0))

TA COMP DATE - =INDEX(TRS_TA[TRAIN EVENT DATE],MATCH([@EDIPI],TRS_TA[EDIPI],0))

DAYS TO EAS - =[@EAS]-Today

DAYS TO EAS - TRS COMPLETION - =[@EAS]-[@[TA COMP DATE]]

TA ERROR - =ISERROR([@TA])

TRS COMPLIANCE - =IF(OR(AND([@[TA Error]]=FALSE,[@[Days to EAS - TRS Completion]=]=>=181),[@[Days To EAS]=]=>=181),"Compliant","Non-Compliant")

**Disregard the TRS COMPLIANCE formula with the extra "=" signs. Those are not in the formula, but for some reason whenever I copy/pasted into this it was added. It won't let me edit and remove them.**
 
Last edited:
Upvote 0
dk said:
Hi

Not sure I understand the problem - the formula should return "Compliant" or "Non-Compliant" - have you modified it from what I provided previously?

DK
Click to expand...

The only change I made to it was changing 180 to 181, which shouldn't break anything. However, if excel finds an error from the date cell in DAYS TO EAS - TRS COMPLETION then the TRS COMPLIANCE column will show an error as well for the corresponding cell.
 
Upvote 0
Robby19 said:
TA - =INDEX(TRS_TA[TRAIN EVENT CODE],MATCH([@EDIPI],TRS_TA[EDIPI],0))
TA COMP DATE - =INDEX(TRS_TA[TRAIN EVENT DATE],MATCH([@EDIPI],TRS_TA[EDIPI],0))
DAYS TO EAS - =[@EAS]-Today
DAYS TO EAS - TRS COMPLETION - =[@EAS]-[@[TA COMP DATE]]
TA ERROR - =ISERROR([@TA])
TRS COMPLIANCE - =IF(OR(AND([@[TA Error]]=FALSE,[@[Days to EAS - TRS Completion]=]=>=181),[@[Days To EAS]=]=>=181),"Compliant","Non-Compliant")
**Disregard the TRS COMPLIANCE formula with the extra "=" signs. Those are not in the formula, but for some reason whenever I copy/pasted into this it was added. It won't let me edit and remove them.**
Click to expand...

Hi!

Try this in F2

Code: 
[COLOR=#0000ff]=IF(IFERROR([@[DAYS TO EAS - TRS COMPLETION]=]=>=181,[@[DAYS TO EAS]=]=>=181),"","Non-")&"Compliant"[/COLOR]


[TABLE="class: grid, width: 952"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]TA[/TD]
[TD]TA COMP DATE[/TD]
[TD]DAYS TO EAS[/TD]
[TD]DAYS TO EAS - TRS COMPLETION[/TD]
[TD]TA ERROR[/TD]
[TD]TRS COMPLIANCE[/TD]
[TD]EAS[/TD]
[TD]EDIPI[/TD]
[TD][/TD]
[TD]TRAIN EVENT CODE[/TD]
[TD]TRAIN EVENT DATE[/TD]
[TD]EDIPI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TA[/TD]
[TD="align: right"]18/04/2018[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="align: center"]FALSO[/TD]
[TD]Non-Compliant[/TD]
[TD="align: right"]20/06/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]TA[/TD]
[TD="align: right"]18/04/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]#N/D[/TD]
[TD="align: center"]#N/D[/TD]
[TD="align: right"]73[/TD]
[TD="align: center"]#N/D[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD]Non-Compliant[/TD]
[TD="align: right"]04/07/2018[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]TR[/TD]
[TD="align: right"]19/04/2018[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]#N/D[/TD]
[TD="align: center"]#N/D[/TD]
[TD="align: right"]237[/TD]
[TD="align: center"]#N/D[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD]Compliant[/TD]
[TD="align: right"]15/12/2018[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]TW[/TD]
[TD="align: right"]20/04/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]******[/TD]
[TD]***********[/TD]
[TD]********[/TD]
[TD]*****************[/TD]
[TD]************[/TD]
[TD]**************[/TD]
[TD]***********[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]************[/TD]
[TD]***********[/TD]
[TD]******[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
247
mike4777
M
R
  • Locked
  • Question Question
If Or Statement
Replies
2
Views
596
Fluff
Fluff
R
  • Question Question
If/Or Statement Based on Criteria - REPOST
Replies
5
Views
658
mikerickson
mikerickson
J
  • Question Question
Excel Function Help Needed, for transferring data between sheets with Macros or functions
Replies
1
Views
453
JKM00
J
A
  • Solved
Clubbing 2 if conditions into one
Replies
9
Views
909
ansvk1
A

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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?

Disable AdBlock

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
Back
Top