Health check with 3 criteria not working

Cheryl Pan · 05/11/20

Hi All, it's probably something REALLY basic for you all, but I cannot for the life of me get my Health check status to convert from Red"No"/Yellow"Hold"/Green"Yes". I have written my formula as:

=IF(ISDATE([LOA Issued]@row), "No", IF(AND([LOA Received]@row = 1) + (ISDATE([LOA Issued]@row)), "Hold", IF(AND([DRAs]@row = 1) + ([LOA Received]@row = 1) + (ISDATE([LOA Issued]@row)), "Yes")))

I've tried with and without the +. I only get that the first of my 3 criteria has been met. Please help! Thanks!!! :)

L_123 · 05/11/20

you need to reverse your if statement as the true statement for the "No" part is true for other criteria, and you are filtering them out prior to meeting them. There are also some syntax issues here, but I think I get the general Idea.

IF(AND([DRAs]@row = 1, [LOA Received]@row = 1, ISDATE([LOA Issued]@row), "Yes",IF(AND([LOA Received]@row = 1, ISDATE([LOA Issued]@row), "Hold",IF(ISDATE([LOA Issued]@row), "No"

you can simplify this down a bit, but I tried to keep it close to your original formula so you can understand what the syntax should be.

Cheryl Pan · 05/11/20

Thanks! But unfortunately I received the following error '#UNPARSEABLE'. What I am hoping to do is get a red/"No" indicator if a date is entered in the "LOA Issued" column... a yellow/"Hold" indicator if both a date is present and if it has been "LOA received"... and a green/"Yes" indicator if all three criteria have been met. Since the last 2 columns are checkbox indicators, I assume I need the "=1". Thanks in advance!!

L_123 · 05/12/20

Ok I misunderstood the goal. With your comment explaining exactly what you want to do, I can simplify this down quite a bit.

Keep in mind that an if statement returns a true and a false, not a true and a continue. That is if you have checked the criteria with the if statement, you don't need to check it further down the line. An example of this would be

=If(A1 <= 0, "The cell is negative or zero", if(A1< 10, "The cell is between 0 and 10","The cell is greater than or equal to 10"))

So the common indicator that is in all 3 of your checks is ISDATE([LOA Issued]@row)

=if(isdate([LOA Issued]@row),

The second indicator is [LOA Recieved}@row

=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,

and the last indicator is [DRAs]@row

=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,if([DRAs]@row = 1

Then we can start stacking our results.

=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,if([DRAs]@row = 1,"Yes","Hold"),"No"),"")

L_123 · 05/11/20