2 IF Statements not mutually exclusive

[Guest]

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

 

[JE McGimpsey]

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.

 

[Guest]

Thank you, JE!!!!!!! It worked.

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.

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