Combining 4 Conditions into 1 Formula!
 

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

Combining 4 Conditions into 1 Formula!
 

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

"Danny Boy" wrote:

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

Combining 4 Conditions into 1 Formula!
 

Only one variable need to be tested:

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

--
Gary''s Student - gsnu200834


"Danny Boy" wrote:

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

Combining 4 Conditions into 1 Formula!
 

Danny Boy wrote:
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.

Combining 4 Conditions into 1 Formula!
 

Nice

"Gary''s Student" wrote:

Only one variable need to be tested:

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

--
Gary''s Student - gsnu200834


"Danny Boy" wrote:

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

Combining 4 Conditions into 1 Formula!
 

Thanks
--
Gary''s Student - gsnu200834


"Mike H" wrote:

Nice

"Gary''s Student" wrote:

Only one variable need to be tested:

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

--
Gary''s Student - gsnu200834


"Danny Boy" wrote:

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

Combining 4 Conditions into 1 Formula!
 

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",""))))

"Mike H" wrote:

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

"Danny Boy" wrote:

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

Combining 4 Conditions into 1 Formula!
 

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","")))

"Danny Boy" wrote:

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