2 IF Statements not mutually exclusive
I am stuck on a particular problem. Because some users here are not familiar
with MS Project, we need to use Excel for reporting. So I am creating a template for a simple (?) report for management to be created as an Excel chart, and embedded in PowerPoint (think of it as a modified Gantt chart. The Gantt chart add-ins that are out there do not create the kind of graphic management is looking for.) I am building the formulas to put into the chart now, and have a problem with two columns returning some of the same results in some fields. The basic data for the formulas is: comparison of START and FINISH, to create three boxes or bubbles: Completed Tasks, Tasks in Process, and Next Step Tasks. The English language version of the logic for the three formulas for these three boxes is, I think: [finish] equals [date earlier than today], [start] equals [date earlier than today], AND [finish] equals [date later than today], and [start] equals [date later than today]. I have created three IF statements, but the problem is, for the second two statements, I get an entry in the cell for both, which is not what I want. I can see why that happens, if I look at my logic, but I cannot figure out how to rephrase my formulas to prevent this. (See example below for what the output is and yellow highlights for problem areas.) Initiating & PlanningComplete Monitoring, Control & ExecutionIn Process Next Step:Monitoring, Control & Execution Can anyone help? Obviously I need mutually exclusive selections for the last two statements, but I am doing something wrong. Here are the IF statements written: E4 is FINISH D4 is START E36 is a cell set up to hold the formula =TODAY () =IF (E4<$E$36, B4 & €śComplete€ť, 0) =IF (D4<$E$36&E4$E$36, B4 & €śIn Process€ť, 0) =IF (E4$E$36, €śNext Step€ť& B4, 0) Bev |
2 IF Statements not mutually exclusive
One way:
=IF(E4<$E$36, B4 & "Complete", 0) =IF(AND(D4<$E$36,E4$E$36), B4 & "In Process", 0) =IF(D4$E$36, "Next Step" & B4, 0) Note that & is the concatenation operator, not a logical (e.g., AND) operator. In article , Bev wrote: I am stuck on a particular problem. Because some users here are not familiar with MS Project, we need to use Excel for reporting. So I am creating a template for a simple (?) report for management to be created as an Excel chart, and embedded in PowerPoint (think of it as a modified Gantt chart. The Gantt chart add-ins that are out there do not create the kind of graphic management is looking for.) I am building the formulas to put into the chart now, and have a problem with two columns returning some of the same results in some fields. The basic data for the formulas is: comparison of START and FINISH, to create three boxes or bubbles: Completed Tasks, Tasks in Process, and Next Step Tasks. The English language version of the logic for the three formulas for these three boxes is, I think: [finish] equals [date earlier than today], [start] equals [date earlier than today], AND [finish] equals [date later than today], and [start] equals [date later than today]. I have created three IF statements, but the problem is, for the second two statements, I get an entry in the cell for both, which is not what I want. I can see why that happens, if I look at my logic, but I cannot figure out how to rephrase my formulas to prevent this. (See example below for what the output is and yellow highlights for problem areas.) Initiating & PlanningComplete Monitoring, Control & ExecutionIn Process Next Step:Monitoring, Control & Execution Can anyone help? Obviously I need mutually exclusive selections for the last two statements, but I am doing something wrong. Here are the IF statements written: E4 is FINISH D4 is START E36 is a cell set up to hold the formula =TODAY () =IF (E4<$E$36, B4 & €śComplete€ť, 0) =IF (D4<$E$36&E4$E$36, B4 & €śIn Process€ť, 0) =IF (E4$E$36, €śNext Step€ť& B4, 0) Bev |
2 IF Statements not mutually exclusive
Thank you, JE!!!!!!! It worked.
"JE McGimpsey" wrote: One way: =IF(E4<$E$36, B4 & "Complete", 0) =IF(AND(D4<$E$36,E4$E$36), B4 & "In Process", 0) =IF(D4$E$36, "Next Step" & B4, 0) Note that & is the concatenation operator, not a logical (e.g., AND) operator. In article , Bev wrote: I am stuck on a particular problem. Because some users here are not familiar with MS Project, we need to use Excel for reporting. So I am creating a template for a simple (?) report for management to be created as an Excel chart, and embedded in PowerPoint (think of it as a modified Gantt chart. The Gantt chart add-ins that are out there do not create the kind of graphic management is looking for.) I am building the formulas to put into the chart now, and have a problem with two columns returning some of the same results in some fields. The basic data for the formulas is: comparison of START and FINISH, to create three boxes or bubbles: Completed Tasks, Tasks in Process, and Next Step Tasks. The English language version of the logic for the three formulas for these three boxes is, I think: [finish] equals [date earlier than today], [start] equals [date earlier than today], AND [finish] equals [date later than today], and [start] equals [date later than today]. I have created three IF statements, but the problem is, for the second two statements, I get an entry in the cell for both, which is not what I want. I can see why that happens, if I look at my logic, but I cannot figure out how to rephrase my formulas to prevent this. (See example below for what the output is and yellow highlights for problem areas.) Initiating & PlanningComplete Monitoring, Control & ExecutionIn Process Next Step:Monitoring, Control & Execution Can anyone help? Obviously I need mutually exclusive selections for the last two statements, but I am doing something wrong. Here are the IF statements written: E4 is FINISH D4 is START E36 is a cell set up to hold the formula =TODAY () =IF (E4<$E$36, B4 & €œComplete€, 0) =IF (D4<$E$36&E4$E$36, B4 & €œIn Process€, 0) =IF (E4$E$36, €œNext Step€& B4, 0) Bev |
All times are GMT +1. The time now is 12:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com