PS 2007 Formula Help

[Nik003]

I've been on tins for too long now, and I can't see it.
I have the following formula:

IIf([Baseline Start]=ProjDateValue('NA'),0,ProjDateDiff([Baseline
Finish],[Baseline Start])/ProjDateDiff(IIf(IsDate([Actual
Finish]+0)=0,[Finish],[Actual Finish]),IIf(IsDate([Actual
Start]+0)=0,[Start],[Actual Start]))*100-100)

If I put it Entoty = Project and Type = Text I get the expected values, but
If I put it Entity = Project and Type = Number, I don't get any values back.
I need to use Type = Number so I can setup the colored flags to display.

Thanks.

 

[Jim Aksel]

Your question appears to be "How can I be sure I return a numeric value from
this formula?" There are some conversions you may want to consider:

CDec(your expression) will convert your calcution to Decimal
CDbl(your expression) will convert it to double
CInt(your expression) will convert to integeter which is probably not useful
to you
CSng(your epxression) will convert to single precision

Sometimes when strings are determined there can be leading or trailing
spaces. I don't see that here, but you can use "trim" ...

CDec(trim(your expression)) (etc)

We do set different color flags based on text values, perhaps a conversion
to numeric is not truly required.

Some General Observations:

It appears you want to calculate some type of % compared to baseline and
actual durations or current durations It may be more straight forward to use
[Baseline Duration], [Actual Duration], and [Duration] instead of calcuating.
It looks like you are scoring on Baseline Duration and then comparing it to
either the [Actual Duration] or [Duration] if the task is not yet complete.

You are using [Baseline Duration] as your numerator and dividing it by some
measure of [actual duration] or [duration]. I question that logic - normally
the baseline is the standard of comparision and would appear on the bottom.

We do it like this:

[Actual Duration]/[Baseline Duration] where scores less than 1.00 represent
better than the baseline duration. Scores higher than 1.00 are running long.
You could modify your formula to choose [Actual Duration] or [Duration]
depending on the value of [Status] which would be either "Complete" or
something else.

Your initial logic of determining a baseline seems to be working properly.


--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project