RAG formula

[Steve Scott]

I have a great formula set up on a custom field but am having trouble with
making it better.

Existing formula is:-

IIf([% Complete]=100,"Complete",IIf([Baseline
Finish]=ProjDateValue("NA"),"No baseline
Set!",IIf([Finish]<Date(),"Reforecast",IIf([Number1]>5,"R",IIf([Number1]>0,"A","G")))))

Modified formula is:-

IIf([Milestone]=Yes,(IIf([% Complete]=100,"Complete",IIf([Baseline
Finish]=ProjDateValue("NA"),"No baseline
Set!",IIf([Finish]<Date(),"Reforecast",IIf([Number1]>5,"R",IIf([Number1]>0,"A","G")))))),"")

The purpose of the modified formula is to only apply the RAG calculation if
the task is a milestone, however it is not working properly. It is still
allowing tasks to be RAG calculated.

Any ideas? Also how many IIf statements can I nest into a formula as I have
a few more to add?

 

[Roy]

Hello Steve,
Your formula works well for me. Do you really use an english version of
Project ?
If no, replace the Yes value by -1 which works on any language version :

IIf([Milestone]=-1;(IIf([% Complete]=100;"Complete";IIf([Baseline
Finish]=ProjDateValue("NA");"No baseline
Set!";IIf([Finish]<Date();"Reforecast";IIf([Number1]>5;"R";IIf([Number1]>0;"
A";"G"))))));"")

Gérard Ducouret

I have a great formula set up on a custom field but am having trouble with
making it better.

Existing formula is:-

IIf([% Complete]=100,"Complete",IIf([Baseline
Finish]=ProjDateValue("NA"),"No baseline

Modified formula is:-

IIf([Milestone]=Yes,(IIf([% Complete]=100,"Complete",IIf([Baseline
Finish]=ProjDateValue("NA"),"No baseline