Formula for stoplight

[Hawk]

Hello... I need some help with a formula in project. I found a stoplight
formula and it works fine, but I need to tweak it a little bit to match my
boss's needs. Problem is, when i try to understand the logic/syntax and
adjust it, i get a user error. i must be missing a character or something
because it just won't work, and after i get the error, it places the cursor
at the end of the formula instead of highlighting the problem. Here's what
i want it to show me: ---if tasks or milestones are (baselined to start) in
the future, show 1. if tasks or milestones are 100%, show 2. if tasks are on
track to finish according to the baselined dates, show 2. if tasks are
baselined to finish three days from now and the % complete is more than 60%,
show 2. if tasks are baselined to finish three days from now and the %
complete is more than 50 but less than 59%, show 3. if tasks are baselined
to finish one day from now and the % complete is more 40 but less than 49%,
show 4. if tasks are 5 days late (using baselined finish date) and the %
complete is less than 60%, show 5. if tasks are 10 days late (using
baselined finish date) and the % complete is less than 60%, show 6.

this is what i came up with, but like i said, there's an error somewhere (or
lots of somewheres...!)

If the task is 100% or the ratio of how much should be done is on time, GREEN
IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Actual
Start])/([Baseline Finish]-[Baseline Start])))),2,IIf[Baseline
Finish]>Now()-3 And [% Complete]<80,3,IIf[Baseline Finish]>Now()-4 And [%
Complete]<70,4,IIf[Baseline Finish]>Now()-5 And [%
Complete]<60,5,IIf[Baseline Finish]>Now()-10 And [%
Complete]<60,6,IIf[Baseline Start] Or [Start]>Now(),1

Any help would be very much appreciated since the help on project's formulas
didn't assist me much....

 

[Jonathan Sofer]

Looks like you are missing some closing parenthesis at the end of your
formula which explains why the cursor moves to the end of your formula after
throwing the error.

Actuall, all of your IIf statements execpt the first one need to have a
start open paranthesis. The syntax is IIF(conditional statement,true,false)

Jonathan

 

[Hawk]

Thanks for the comments ! I'm new at all of this, so 1- is there a way to
know when you use () or [] in the formula? I'm sure there are rules, i just
can't find them. 2- i've never used flags...will that still give me the
stoplight visual? the stoplight part has to stay, so i'm guessing the filter
option is out...

thanks again--

You have lots of missing opening and closing brackets on your IIFs.
Miles of nested IIFs are always going to be error prone and very hard to
check that you are getting the expected result.
This looks a lot more complicated than necessary
Try putting the various IIFs into separate spare Flag fields so you can make
sure they all work by themselves.
You could also try a set of Filters to find the Tasks that meet the
criteria.
Of course, the Filters don't put the graphic indicator in or colour in the
cells, but they will find the Tasks.
Use separate one-liner Filters rather than one with a whole mess of
complicated Boolean.

Trevor RabeyTrevor Rabey 0407213955 61 8 92727485 PERFECT PROJECT PLANNING
www.perfectproject.com.au

Hello... I need some help with a formula in project. I found a stoplight
formula and it works fine, but I need to tweak it a little bit to match my
boss's needs. Problem is, when i try to understand the logic/syntax and
adjust it, i get a user error. i must be missing a character or something
because it just won't work, and after i get the error, it places the
cursor
at the end of the formula instead of highlighting the problem. Here's
what
i want it to show me: ---if tasks or milestones are (baselined to start)
in
the future, show 1. if tasks or milestones are 100%, show 2. if tasks are
on
track to finish according to the baselined dates, show 2. if tasks are
baselined to finish three days from now and the % complete is more than
60%,
show 2. if tasks are baselined to finish three days from now and the %
complete is more than 50 but less than 59%, show 3. if tasks are
baselined
to finish one day from now and the % complete is more 40 but less than
49%,
show 4. if tasks are 5 days late (using baselined finish date) and the %
complete is less than 60%, show 5. if tasks are 10 days late (using
baselined finish date) and the % complete is less than 60%, show 6.

this is what i came up with, but like i said, there's an error somewhere
(or
lots of somewheres...!)

If the task is 100% or the ratio of how much should be done is on time,
GREEN
IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Actual
Start])/([Baseline Finish]-[Baseline Start])))),2,IIf[Baseline
Finish]>Now()-3 And [% Complete]<80,3,IIf[Baseline Finish]>Now()-4 And [%
Complete]<70,4,IIf[Baseline Finish]>Now()-5 And [%
Complete]<60,5,IIf[Baseline Finish]>Now()-10 And [%
Complete]<60,6,IIf[Baseline Start] Or [Start]>Now(),1

Any help would be very much appreciated since the help on project's
formulas
didn't assist me much....

 

[Jonathan Sofer]

Use ( ) when you are creating conditional IIF statements. Like I said, the
syntax for IIF statements is IIF(conditional statement,true value,false
value)

Use [] to specify field values like [Baseline Work] or [Finish Date] etc.

Flags can be associated with graphical indicators just fine but you only get
a Yes or No value to work with. If you need more than just the Yes No
values, then you can't use the flag type.

Jonathan

Thanks for the comments ! I'm new at all of this, so 1- is there a way to
know when you use () or [] in the formula? I'm sure there are rules, i
just
can't find them. 2- i've never used flags...will that still give me the
stoplight visual? the stoplight part has to stay, so i'm guessing the
filter
option is out...

thanks again--

You have lots of missing opening and closing brackets on your IIFs.
Miles of nested IIFs are always going to be error prone and very hard to
check that you are getting the expected result.
This looks a lot more complicated than necessary
Try putting the various IIFs into separate spare Flag fields so you can
make
sure they all work by themselves.
You could also try a set of Filters to find the Tasks that meet the
criteria.
Of course, the Filters don't put the graphic indicator in or colour in
the
cells, but they will find the Tasks.
Use separate one-liner Filters rather than one with a whole mess of
complicated Boolean.

Trevor RabeyTrevor Rabey 0407213955 61 8 92727485 PERFECT PROJECT
PLANNING
www.perfectproject.com.au

Hello... I need some help with a formula in project. I found a
stoplight
formula and it works fine, but I need to tweak it a little bit to match
my
boss's needs. Problem is, when i try to understand the logic/syntax and
adjust it, i get a user error. i must be missing a character or
something
because it just won't work, and after i get the error, it places the
cursor
at the end of the formula instead of highlighting the problem. Here's
what
i want it to show me: ---if tasks or milestones are (baselined to
start)
in
the future, show 1. if tasks or milestones are 100%, show 2. if tasks
are
on
track to finish according to the baselined dates, show 2. if tasks are
baselined to finish three days from now and the % complete is more than
60%,
show 2. if tasks are baselined to finish three days from now and the %
complete is more than 50 but less than 59%, show 3. if tasks are
baselined
to finish one day from now and the % complete is more 40 but less than
49%,
show 4. if tasks are 5 days late (using baselined finish date) and the
%
complete is less than 60%, show 5. if tasks are 10 days late (using
baselined finish date) and the % complete is less than 60%, show 6.

this is what i came up with, but like i said, there's an error
somewhere
(or
lots of somewheres...!)

If the task is 100% or the ratio of how much should be done is on time,
GREEN
IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Actual
Start])/([Baseline Finish]-[Baseline Start])))),2,IIf[Baseline
Finish]>Now()-3 And [% Complete]<80,3,IIf[Baseline Finish]>Now()-4 And
[%
Complete]<70,4,IIf[Baseline Finish]>Now()-5 And [%
Complete]<60,5,IIf[Baseline Finish]>Now()-10 And [%
Complete]<60,6,IIf[Baseline Start] Or [Start]>Now(),1

Any help would be very much appreciated since the help on project's
formulas
didn't assist me much....