Solved How To Use IF, AND, Statements in Excel

Microsoft Excel 2016 suites
February 13, 2019 at 14:14:57
Specs: Windows 10
I'm trying to write a formula that includes multiple conditions to return the correct value. In the example below (I've recreated it as best I could) I am tracking tenant's insurance documents. The formula will go in column B. I need it to return "Current" if the date in column C is tomorrow or later, "Expired" if column C is today or earlier, and "Missing" if column C is blank AND column A does not say "VACANT." If column A is VACANT, B and C both need to be blank.

So far I've come up with

=IF(C2<=TODAY(),"Expired", IF(C2>TODAY(),"Current", IF(C2="", "Missing")))

Unfortunately because of <=TODAY, I guess it reads that to mean blank as well, so the last condition for Missing is ignored. Every blank cell returns "Expired." And I can't figure out how to have it look at Column A to see if it says VACANT.

Is there a formula that can make this work?


              A                      B                      C
    1       Name	          Status 	      Expiration Date
    2      VACANT		
    3      Tenant 1               Current	        03/18/2019
    4      Tenant 2               Expired	        11/21/2017
    5      VACANT		
    6      Tenant 3               Current	        10/01/2019
    7      Tenant 4	          Missing	
    8      Tenant 5   	          Current	        06/01/2019

message edited by tgordon21


See More: How To Use IF, AND, Statements in Excel

Reply ↓  Report •

✔ Best Answer
February 13, 2019 at 19:12:09
This should work.

=IF(A2="VACANT","", IF(C2>TODAY(),"Current",IF(AND(C2<>"",C2<=TODAY()),"Expired","Missing")))

The key thing remember about the IF function is that it stops as soon as it finds a TRUE clause. Therefore, if A2 = "VACANT", return a blank cell and stop. If not, then you can move on and do your comparisons. If none of the comparisons are TRUE, then return "Missing".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
February 13, 2019 at 14:47:06
Hang about for pholks like DerbyDad03 and Mike McConaghy and hidee663... they will take you through it all... And there are a few others here too of similar guru status...

Reply ↓  Report •

#2
February 13, 2019 at 15:47:03
First, please click on the How-To link at the end of this post and read the instructions on how to post example data in this forum. Then edit your post so that the data is formatted so that we can read it easier. Please make sure that you use Column letters and Row numbers.

BTW...

This clause is checking for a space, not a blank cell:

IF(C1=" ", "Missing")

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
February 13, 2019 at 16:21:40
Thanks for the tip. I wasn't sure how to make it look like an actual table.

Regarding IF(C1=" ", "Missing"), thanks for catching that. I was playing around and put the space in there to see if it made a difference, but it still came back as Expired everywhere that was blank. I just forgot to take that space out before I copied it here.


Reply ↓  Report •

Related Solutions

#4
February 13, 2019 at 17:54:52
Update:

I've tried this:

=IF(C2<=TODAY(),"Expired", IF(C2>TODAY(),"Current", IF(AND(C2="",A2<>"VACANT"), "Missing")))

But I'm still getting "Expired" in every cell that is blank. Not sure what to try next.


Reply ↓  Report •

#5
February 13, 2019 at 19:12:09
✔ Best Answer
This should work.

=IF(A2="VACANT","", IF(C2>TODAY(),"Current",IF(AND(C2<>"",C2<=TODAY()),"Expired","Missing")))

The key thing remember about the IF function is that it stops as soon as it finds a TRUE clause. Therefore, if A2 = "VACANT", return a blank cell and stop. If not, then you can move on and do your comparisons. If none of the comparisons are TRUE, then return "Missing".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
February 14, 2019 at 08:16:51
This worked perfect! Thanks for your help!

Reply ↓  Report •

Ask Question