Combining 4 Conditions into 1 Formula!

[Danny Boy]

I am trying to establish a formula to produce the following flags in Column Q:

Level 1 Program Completed
Level 1 Program Not Completed
Level 2 Program Completed
Level 2 Program Not Completed

The following are the conditions I am trying to set up for the formula, but
I need some help bringing it all together. I'm good at conceptualizing what I
need, but often not so good at my "ands" and my "ors", and my parenthesis.
The four conditions below should be expressed with one formula:

=IF(I4="1",AND(O4="Yes","Level 1 Program Completed")
=If(I4="1", AND(O4="No","Level 1 Program Not Completed")

=IF(I4="2",AND(O4="Yes",AND(P4<>"","Level 2 Program Completed")
=If(I4="2",AND(O4="NO","Level 2 Program Not Completed")

Thank you very much for any help!
Best,

Dan

 

[Mike H]

Hi,

Try this

=IF(AND(I4=1,O4="Yes"),"Level 1 Program
Completed",IF(AND(I4=1,O4="No"),"Level 1 Program Not
Completed",IF(AND(I4=2,O4="Yes"),"Level 2 Program
Completed",IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed",""))))

Paste into the formula bar with the cell you want seleceted.

Mike

 

[Gary''s Student]

Only one variable need to be tested:

=IF(O4="yes","Level " & I4 & " Program Completed","Level " & I4 & " Program
Not Completed")

 

[Glenn]

I am trying to establish a formula to produce the following flags in Column Q:

Level 1 Program Completed
Level 1 Program Not Completed
Level 2 Program Completed
Level 2 Program Not Completed

The following are the conditions I am trying to set up for the formula, but
I need some help bringing it all together. I'm good at conceptualizing what I
need, but often not so good at my "ands" and my "ors", and my parenthesis.
The four conditions below should be expressed with one formula:

=IF(I4="1",AND(O4="Yes","Level 1 Program Completed")
=If(I4="1", AND(O4="No","Level 1 Program Not Completed")

=IF(I4="2",AND(O4="Yes",AND(P4<>"","Level 2 Program Completed")
=If(I4="2",AND(O4="NO","Level 2 Program Not Completed")

Thank you very much for any help!
Best,

Dan

Another option:

="Level "&I4&" Program "&IF(O4="No","Not ","")&"Completed"


If you would specify what you want in the cell if I4 is not 1 or 2 or if O4 is
not "Yes" or "No", this could be expanded to cover those issues.

 

[Mike H]

Nice

Only one variable need to be tested:

=IF(O4="yes","Level " & I4 & " Program Completed","Level " & I4 & " Program
Not Completed")

 

[Gary''s Student]

Thanks
--
Gary''s Student - gsnu200834

Nice

 

[Danny Boy]

That was great Mike and it works. The only other addition I forgot to account
for is the following condition:

If a Level 2 client completes their class (Column I="2", and Column
O="Yes"), but does not have a date posted in column P within 3 months AFTER
the date in which their class ended (Column M identifies class ending date),
than the "Level 2 Program Not Completed Flag" should also trigger.

I suspect that the addition I'll need to incorporate looks somethink like
this:

IF(TODAY()>EDATE(M4,3)(AND(I4=2,O4="Yes"),"Level 2 Program Not
Completed",""))))

The formula as it currently stands (without the above addition) looks like
this:

=IF(AND(I4=1,O4="Yes"),"Level 1 Program
Completed",IF(AND(I4=1,O4="No"),"Level 1 Program Not
Completed",IF(AND(I4=2,O4="Yes",P4<>""),"Level 2 Program
Completed",IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed",""))))

 

[Danny Boy]

Below is the incorporation of the date information I needed to add to the
Master Formula below, however when I add this information in, I can't get it
to work........

HELP!

Dan

=IF(TODAY()>EDATE(M4,3),AND(I4=2,O4="Yes"),"Level 2 Program Not Completed"

=IF(AND(I4=1,O5="Yes"),"Level 1 Program
Completed",IF(AND(I4=1,O4="No"),"Level 1 Program Not
Completed",IF(AND(I4=2,O4="Yes",P4<>""),"Level 2 Program
Completed",IF(TODAY()>EDATE(M4,3),AND(I4=2,O4="Yes"),"Level 2 Program Not
Completed",IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed","")))