Using IF, AND, OR

Working with a database where cells use numbers and letters. Trying to query from cells using IF, AND,OR without complete success.

[TABLE="width: 500, align: center"]

[tr]

[td][/td]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]

[/tr]

[tr]

[TD="align: center"]1[/TD]
[TD="align: center"]PT Admit Status[/TD]
[TD="align: center"]Start Location[/TD]

[td]

DC/Transfer

[/td]

[td]

OPS

[/td]

[td]

OPS-IP

[/td]

[td]

IP

[/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]2[/TD]
[TD="align: center"]OPS[/TD]
[TD="align: center"]OPS[/TD]

[td]

PACU Home

[/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]3[/TD]
[TD="align: center"]IP[/TD]
[TD="align: center"]M435[/TD]

[td]

M545

[/td]

[td][/td]

[td][/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]4[/TD]
[TD="align: center"]OPS[/TD]
[TD="align: center"]OPS[/TD]

[td]

OPS

[/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]5[/TD]
[TD="align: center"]OPS[/TD]
[TD="align: center"]E423[/TD]

[td]

E423

[/td]

[td][/td]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]6[/TD]
[TD="align: center"]IP[/TD]
[TD="align: center"]M633[/TD]

[td]

M633

[/td]

[td][/td]

[td][/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]7[/TD]
[TD="align: center"]IP[/TD]
[TD="align: center"]A510[/TD]

[td]

M531

[/td]

[td][/td]

[td][/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]8[/TD]
[TD="align: center"]OPS[/TD]
[TD="align: center"]OPS[/TD]

[td]

PACU Home

[/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[TD="align: center"]9[/TD]
[TD="align: center"]OPS[/TD]
[TD="align: center"]OPS[/TD]

[td]

M537

[/td]

[td][/td]

[TD="align: center"]1[/TD]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

Column A represents patient admission status, Outpatient (OPS) or Inpatient (IP)
Column B represents patient location prior to procedure date. OPS if coming from home, ex M435 from hospital bed.
Column C represents patient discharge location after procedure.
Column D represents patient coming from home for the procedure and discharged home same day of the procedure (OPS).
Column E represents patient coming from home for the procedure and admitted to hospital bed after the procedure (OPS-IP).
Column F represents patient coming from a hospital bed and returning to hospital bed after the procedure (IP).

Trying to autofill columns D,E, and F using IF formula.
To autofill column D, patient will meet one of two criteria:
1. Admit status is OPS and discharged to OPS.
2. Admit status is OPS and discharged to PACU Home.

=IF(OR(A2="OPS",C2="OPS",C2="PACU Home"),1,"") *This formula works for me.

To autofill column E, patient will meet one criteria:
1. Admit status is OPS and admitted to hospital bed.

=IF(OR(A2="OPS",C2="OPS",C2="PACU Home"),"",1) *This formula does not work for me.

To autofill column F, patient will meet one criteria:
1. Admit status is IP and returns to hospital bed.

=IF(OR(A2="OPS",A2=""),"",1) *This formula works for me.

I don't understand why the formula for column E does not work. Do I add "AND" or is there an easier formula to accomplish the task?

Thank you in advance for your assistance.

Nestor

Re: Using IF, AND, OR

Quote from skywriter;767727

This is what I get based on your explanation.

I'll just spell out what each formula is doing in plain English to make sure we're on the same page.

D2 formula.
A2 must be OPS - and either C2 is OPS or C2 is PACU Home and you will get a result of 1 in D2, if all this criteria is met.

Code

=IF(AND(A2="OPS",OR(C2="OPS",C2="PACU Home")),1,"")

E2 formula.
A2 must be OPS and C2 must not be PACU Home and C2 must also not be OPS, you will get a result of 1 if all this criteria is met.

Code

=IF(AND(A2="OPS",C2<>"PACU Home",C2<>"OPS"),1,"")

F2 formula
A2 must be IP and C2 must not be PACU Home and C2 must also not be OPS, you will get a result of 1 if all this criteria is met.

Code

=IF(AND(A2="IP",C2<>"PACU Home",C2<>"OPS"),1,"")

If I got any of this wrong, just tell me in the same terms what you want to happen.

Display More

Thanks you for the help. F2 formula did not work because I did not take into account the blank cells.

Nestor