Enhance formula

[Steve Scott]

I currently have the following formula:-

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"))))),"NM")

Can I extend it further to perform the following checks as well:- (ie how
big can my formula be!!)


1) Amend the formula sp that it only works on tasks flagged as milestones.

2) Using another text field to act as reporting level (say for reporting
levels 1,2,3,4 and 5) and then apply the above RAG formula but with differing
"slippage days" depending upon what Level is in the text field.


Thanks in advance

Steve

 

[JackD]

There is a character limit to the length of the formula (I can't recall
exactly, but I think it is ~255 characters) but you can amend your formula
to include 1 and 2 below without any problem with the math. You already have
the milestone check in there, but you are using it to return a value of -1.

Your big problem is going to be fitting the formula in the maximum number of
characters. Start by changing things like "No baseline Set" to "No BL".

If you run out of room for your formula then you can always go to VBA where
the sky is the limit.

See my website for appropriate uses of custom formulas and also how to use
VBA:

http://masamiki.com/project

 

[John]

I currently have the following formula:-

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"))))),"NM")

Can I extend it further to perform the following checks as well:- (ie how
big can my formula be!!)


1) Amend the formula sp that it only works on tasks flagged as milestones.

2) Using another text field to act as reporting level (say for reporting
levels 1,2,3,4 and 5) and then apply the above RAG formula but with differing
"slippage days" depending upon what Level is in the text field.


Thanks in advance

Steve

Steve,
Formulas can be made as complex as you can stand but keep in mind the
limitations of using a formula in a custom field. The formula can only
work on data relating to an individual task, the more complex the
formula the more likely it will contain a logic error, and formulas only
work on tasks in the Project file where they reside. Formulas are also
quite limited in what they can do (i.e. available functions).

Whey trying to exercise multiple decisions that may involve data
elements from many tasks, (or resource or assignments), VBA is the way
to go. The same complex formula used in a custom field can be easily
broken into smaller elements making it easier to understand and
troubleshoot. A macro stored in your Project Global can be used to
operate on any Project file. If you do not have any experience with VBA,
we will be happy to help you either directly or through suggestions for
learning VBA yourself.

I know I didn't directly answer you question. When I see someone trying
to develop a complex formula my inclination is to suggest a better
alternative.

John
Project MVP