Nested If/And Functions

I need to set up an IF statement that looks at cell F2 and determines if the number is greater than zero, and also looks at cell H2 to determine if there is a date in the cell. If there is a number higher than zero (F2) and there is a date (H2), the formula should respond with "Compliant" else "Deficient."

Here is what I have:

=IF(AND(F2>0,H2=ISDATE), "Compliant", "Deficient")

I get the #NAME? error...

Thanks for the help...

OK...the formula worked with the identifier (isdate) but the end goal didn't work due to some formatting with my spreadsheet.

I've added a column to indicates whether or not the training is Annual, Biennial, Triennial, or One-Time (A,B,T,OT). I need to change the formula to look at Column B to determine if there is a "One-Time" requirement and if there is any completion date.

=IF(AND(B2="OT",ISTEXT(I2)),"Compliant","Deficient") No problem here...

I need to expand that formula to say that if the text in Col B isn't "OT", then Excel needs to enter text based on number value in Column G...

If the text in column B2 is "OT" and there is text in column I2, then compliant; if the text in Column B2 is other than "OT" and the number in G2 is greater than 30, then "Compliant"; If the number in G2 is less than 0, Deficient; If the number is between 0 and 30, Caution.

=IF(AND(B2<>"OT",ISTEXT(I2)),"Compliant",IF(OR(G2>30),"Compliant",(G2<0),"Deficient","Caution")

Should this work?

By the way - you guys ROCK! I've been using the formulas you previously provided to do this report, some recent changes to the data source and requested changes from the recipients are what have caused these issues.


OK...I've linked a copy of the spreadsheet here (my information security settings won't let me upload, or I would have done it sooner) - the formula you provided worked, but it's not quite there.

In the attached spreadsheet, I am trying to get column H2 to tell me if someone is Compliant in their training, overdue (Deficient) or they need to pay attention (Caution).

The formula needs to:

• Look at Column B and determine if it is a one-time training (OT), and there is a completion date (Column I). If yes to both, "Compliant". If yes to "OT" and no Completion Date, then it needs to look at Column G and make a determination based on the number of days (>30 is compliant, between 30 and 0 is caution, 0 or less is deficient).

• If Column B is NOT OT, then it needs to look at Column G and return Compliant (>30), Caution (0-30), or Deficient (<=0)

Thanks!

Breaking this down, your main split in this function happens by what is in column B, so it's easiest to start the IF function there. Then we can drill down and get the other issues taken care of. So you start with IF(B2<>"OT",

Then we know that if it is in fact not equal to "OT", we need to look at Column G and determine the number, and return a value based on that. So we get IF(G2<=0,"Deficient",IF(G2<=30,"Caution","Compliant"))

If it is equal to "OT", then we are only checking to ensure that there is something in Column I, or returning the same formula as above. So we do IF(ISBLANK(I2),IF(G2<=0,"Deficient",IF(G2<=30,"Caution","Compliant")),"Compliant")

So if we put it all together, it comes to this:

Formula:

Please Login or Register  to view this content.

However, as it looks like you are inputting the days remaining manually, you may want to instead have a formula in there for that. In fact, it's a short enough formula that you could incorporate it into the above one if you would like. Although it depends on what date you plan on filling in, and basically how much automation you want in it.

Sorry if this was a little long winded. I wanted to show you how to break down a formula into manageable parts, and develop it from there. The old teach a man to fish adage and all. Please let us know if you still need assistance.

Not long winded at all, and - I realized after I posted this - I meant to let you know that I was trying to get this on my own, but I was using an "OR" statement.

Thank you for breaking it down - makes more sense to me...

Is there a section/post on the forum that discusses how to do background colors in one cell based on the value of another? My "Days Remaining" cells need to be color coded based on the entry in the "Status" column, but without removing the number of days remaining/overdue. I'm using Conditional Formatting based on Days Remaining, but I need the blank cells with "compliant" in the Status to be green and the built-in conditional formatting isn't cooperating.

The easiest way is to make sure your conditional formatting rules are applied in descending order. Also using the Stop if True condition helps give you better control. Conditional formatting to me is honestly a bit more confusing than formulas, so I would suggest doing your best to get in there and figure it out as best you can. When you are looking at the conditions, make sure that you use some logic to it. For instance, if you are going to do one condition based on if column B is Deficient and Column C is less than 0, Make sure the ones below that check higher conditions, such as Deficient and less than 30. You shouldn't leave any undefined space unless you want that to be a default color.

Again, I'm honestly a lot better with formula creation than conditional formatting, but I find it best to learn through practice. Set up 5 or 10 lines of data, and just changing up conditional formulas to see what happens is probably the best way to learn them. At least to get a rudimentary understanding of them.