| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining IF, ANd and SUM functions in a formula | Excel Worksheet Functions | |||
| Combining Text and formula | Excel Discussion (Misc queries) | |||
| Combining formula | Excel Discussion (Misc queries) | |||
| Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
| Combining conditions for data entry validation | Excel Worksheet Functions | |||
Linear Mode
