IF AND OR Together

[Robby19]

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.

TA	TA COMP DATE	DAYS TO EAS	DAYS TO EAS - TRS COMPLETION	TA ERROR	TRS COMPLIANCE
TA	18-APR-18	59	59	FALSE
#N/A		73	43281	TRUE
#N/A		237	43445	TRUE

<tbody>
</tbody>

 

[dk]

Hi

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

HTH
DK

 

[Robby19]

Works great. Thank you!

 

[Robby19]

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.

 

[dk]

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

 

[markmzz]

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.

Hi Robby19,

We need more information.

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

Markmzz

 

[Robby19]

TA	TA COMP DATE	DAYS TO EAS	DAYS TO EAS - TRS COMPLETION	TA ERROR	TRS COMPLIANCE
TA	18-APR-18	59	59	FALSE
#N/A		73	43281	TRUE
#N/A		237	43445	TRUE

<tbody>
</tbody>

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

 

[Robby19]

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

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.

 

[markmzz]

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

Hi!

Try this in F2

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

A

B

C

D

E

F

G

H

I

J

K

L

M

1

TA

TA COMP DATE

DAYS TO EAS

DAYS TO EAS - TRS COMPLETION

TA ERROR

TRS COMPLIANCE

EAS

EDIPI

TRAIN EVENT CODE

TRAIN EVENT DATE

EDIPI

2

TA

18/04/2018

59

63

FALSO

Non-Compliant

20/06/2018

1

TA

18/04/2018

1

3

#N/D

#N/D

73

#N/D

VERDADEIRO

Non-Compliant

04/07/2018

4

TR

19/04/2018

2

4

#N/D

#N/D

237

#N/D

VERDADEIRO

Compliant

15/12/2018

5

TW

20/04/2018

3

5

***

******

***********

********

*****************

************

**************

***********

*******

**

************

***********

******

**

<tbody>
</tbody>

Markmzz

 

[Robby19]

This seems to work perfectly. Thank you!