Division by "0"

[BobC]

We used the following expression to prevent division by zero. This
seems sort of sluggish.

switch([Baseline Finish]=projdatevalue("NA"),"Not Baselined",([Finish
Variance]+0.01)/([Baseline Duration]+0.01)<=0,"On Time or
Ahead",([Finish Variance]+0.01)/([Baseline Duration]+0.01)<=0.1,"Less
than 10% Behind",True,"More than 10% Behind")

Is there a better way?
Bob

 

[Dave]

I think you've missed out some nested switches in the quoted formula
below but I think I see what is going on.

I think that there is a better way. In any case, adding 0.01 to both
numerator and denominator doesn't preserve the ration although I suspect
that the error introduced is small.

I think I would add an extra trap to cater for the case where the
baseline duration is 0 because that can only occur for tasks which are
milestones in the baseline plan and consequently factors of 10% behind
have no meaning for tasks of duration 0. Also, for milestone tasks then
the quotient could be huge.

How about:

switch( no baseline, not baselined , switch( switch milestone , display
finish variance , switch( lateness test , less than 10% behind , more
than 10% behind ) ) )

This would then show how far behind in work terms you were on actual
tasks but would also show you the effect on your milestones.

 

[BobC]

Thanks Dave,
I am going to look at this at work ... and respond again this evening.
Bob

I think you've missed out some nested switches in the quoted formula
below but I think I see what is going on.

I think that there is a better way. In any case, adding 0.01 to both
numerator and denominator doesn't preserve the ration although I suspect
that the error introduced is small.

I think I would add an extra trap to cater for the case where the
baseline duration is 0 because that can only occur for tasks which are
milestones in the baseline plan and consequently factors of 10% behind
have no meaning for tasks of duration 0. Also, for milestone tasks then
the quotient could be huge.

How about:

switch( no baseline, not baselined , switch( switch milestone , display
finish variance , switch( lateness test , less than 10% behind , more
than 10% behind ) ) )

This would then show how far behind in work terms you were on actual
tasks but would also show you the effect on your milestones.

We used the following expression to prevent division by zero. This
seems sort of sluggish.

switch([Baseline Finish]=projdatevalue("NA"),"Not Baselined",([Finish
Variance]+0.01)/([Baseline Duration]+0.01)<=0,"On Time or
Ahead",([Finish Variance]+0.01)/([Baseline Duration]+0.01)<=0.1,"Less
than 10% Behind",True,"More than 10% Behind")

Is there a better way?
Bob

 

[Jim Aksel]

You may want to consider using the "Status" column as well. The Sttatus
column operates off the Status Date (Project/PRoject Information...) and the
%Complete column for the current forecast (not baseline) dates.

The Status column will tell you "Late", "On Schedule", "Complete" or "Future
Task" compared to the current forecast.

The reason I bring this up is because oftern times the basline is obsolete.
With your formula if a predecessor is running late, then all its successors
will be running late as well causing a "Behind Schedule" conditin for you.
ALthough that is quite true, it is usually unecessary to "penalize" a
successor task because a late predecessor precludes the baseline start date.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[BobC]

I'm having trouble understanding why the nested switch statements??

I think you've missed out some nested switches in the quoted formula
below but I think I see what is going on.

I think that there is a better way. In any case, adding 0.01 to both
numerator and denominator doesn't preserve the ration although I suspect
that the error introduced is small.

I think I would add an extra trap to cater for the case where the
baseline duration is 0 because that can only occur for tasks which are
milestones in the baseline plan and consequently factors of 10% behind
have no meaning for tasks of duration 0. Also, for milestone tasks then
the quotient could be huge.

How about:

switch( no baseline, not baselined , switch( switch milestone , display
finish variance , switch( lateness test , less than 10% behind , more
than 10% behind ) ) )

This would then show how far behind in work terms you were on actual
tasks but would also show you the effect on your milestones.

We used the following expression to prevent division by zero. This
seems sort of sluggish.

switch([Baseline Finish]=projdatevalue("NA"),"Not Baselined",([Finish
Variance]+0.01)/([Baseline Duration]+0.01)<=0,"On Time or
Ahead",([Finish Variance]+0.01)/([Baseline Duration]+0.01)<=0.1,"Less
than 10% Behind",True,"More than 10% Behind")

Is there a better way?
Bob