Error in formula for custom field

[PublicMike]

I am trying to create a formula in project that give schedule variance from
the baseline and then assigns a graphical icon depending on the value. I
created the formula exactly from something I read online, but now all I get
is a result #ERROR, with no explanation as to what is causing the error.

The formula I'm using is:

[Finish Variance]/[Baseline Variance]*100

Then I set up the indicators so that

is greater than 15.00 is red
is less than or equal to 0.00 is green
is less than or equal to 15.00 is yellow
is anything <all> is white

All indicators are white, but when I check the value it gives #ERROR. I'm
wondering if that "is anything" value is what is causing the problem, but the
website said this was necessary for when there is no baseline set on a task.

THanks for any assistance.

 

[HansH]

The [Baseline Variance] field does not exist, unless it is a custom
field that you have created.
My guess is that you only need the Finish Variance. This is the
difference between the baseline and the scheduled finish. From your
formula, I get the impression you want to transform a date difference
into a percentage, but I don't see how this could be done in a
meaningful way.
If you tell us what exactly you are trying to calculate in the formula,
we might be able to help you.

I hope this helps,
Hans

Projectopolis <http://msepm.hsquared.be>

 

[Jim Aksel]

No such field [Baseline Variance]
Where do you calculate this value?

It appears you are testing for some type of variance greater than 15% (red).
Might that be [Finish Variance] as a percent of [Baseline Duration]?
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

 

[PublicMike]

I pulled the Baseline Variance field directly out of a pick list in project.
It does indeed exist. To test out my theory of the "is anything" being the
problem, I replaced that line with "is equal to 4294967295" and now the
formula calculates. "4294967295" is apparently the code for #N/A, so if the
baseline hasn't been set, which would produce and error, it has a value to
associate with that and return an indicator.

What I was trying to accomplish is to set a graphical indicator that alerts
when a task is overdue, as determined by the baseline, by a certain
percentage.

I think I've got it now. Thanks for the input.

 

[Rob Schneider]

I also can't find the field [BaseLine Variance] in Project 2007.
Interesting.

I suspect it a custom field (probably one of the number fields).

In any event, PublicMike, why don't you use the field "Indicator" to
indicate if a task is overdue, i.e. late? Let Project do the work. Even
better than telling you when something is already over due, it will warn
you of the computed finish date is beyond the deadline. Better, I
think, to know before something is late that the current schedule is
planning for it to be late.



--rms

 

[PublicMike]

You guys are right. I went back and realized I'd typed the field wrong. The
field was Baseline Duration rather than Variance. Formula had the right
field, it was my typing/thinking that was screwy.

I'll give the indicator field a try and see what that does for me.

Appreciate the help.
--
The only thing constant in life is change; therefore, you can follow change
and allow it to control you, or lead change and control your destiny.

I also can't find the field [BaseLine Variance] in Project 2007.
Interesting.

I suspect it a custom field (probably one of the number fields).

In any event, PublicMike, why don't you use the field "Indicator" to
indicate if a task is overdue, i.e. late? Let Project do the work. Even
better than telling you when something is already over due, it will warn
you of the computed finish date is beyond the deadline. Better, I
think, to know before something is late that the current schedule is
planning for it to be late.



--rms

I pulled the Baseline Variance field directly out of a pick list in project.
It does indeed exist. To test out my theory of the "is anything" being the
problem, I replaced that line with "is equal to 4294967295" and now the
formula calculates. "4294967295" is apparently the code for #N/A, so if the
baseline hasn't been set, which would produce and error, it has a value to
associate with that and return an indicator.

What I was trying to accomplish is to set a graphical indicator that alerts
when a task is overdue, as determined by the baseline, by a certain
percentage.

I think I've got it now. Thanks for the input.

 

[Jim Aksel [MVP]]

Now it makes sense: [Finish Variance]/[Baseline Duration]. If you have
costed resources loaded to this schedule and a baseline, why not just use
Schedule Performance Index [SPI]? This is a comparison of what you have
accomplished divided by what you expected to accomplish within the given
time frame.

You guys are right. I went back and realized I'd typed the field wrong.
The
field was Baseline Duration rather than Variance. Formula had the right
field, it was my typing/thinking that was screwy.

I'll give the indicator field a try and see what that does for me.

Appreciate the help.
--
The only thing constant in life is change; therefore, you can follow
change
and allow it to control you, or lead change and control your destiny.

I also can't find the field [BaseLine Variance] in Project 2007.
Interesting.

I suspect it a custom field (probably one of the number fields).

In any event, PublicMike, why don't you use the field "Indicator" to
indicate if a task is overdue, i.e. late? Let Project do the work. Even
better than telling you when something is already over due, it will warn
you of the computed finish date is beyond the deadline. Better, I
think, to know before something is late that the current schedule is
planning for it to be late.



--rms

I pulled the Baseline Variance field directly out of a pick list in
project.
It does indeed exist. To test out my theory of the "is anything" being
the
problem, I replaced that line with "is equal to 4294967295" and now the
formula calculates. "4294967295" is apparently the code for #N/A, so
if the
baseline hasn't been set, which would produce and error, it has a value
to
associate with that and return an indicator.

What I was trying to accomplish is to set a graphical indicator that
alerts
when a task is overdue, as determined by the baseline, by a certain
percentage.

I think I've got it now. Thanks for the input.

 

[PublicMike]

Unfortuntately, we aren't applying cost to the work done, therefore
activities don't have a budgeted amount, hence the focus on dates only. This
is something to consider, however. We usually don't consider the labor cost
of performing the work. We have been discussing adding some $ amount on an
hourly basis to the resources, but their time is split between so many other
tasks and projects that often our estimates are based on x days to complete a
job that only requires 8 hours of work.

Thanks for the suggestion.
--
The only thing constant in life is change; therefore, you can follow change
and allow it to control you, or lead change and control your destiny.

Now it makes sense: [Finish Variance]/[Baseline Duration]. If you have
costed resources loaded to this schedule and a baseline, why not just use
Schedule Performance Index [SPI]? This is a comparison of what you have
accomplished divided by what you expected to accomplish within the given
time frame.

You guys are right. I went back and realized I'd typed the field wrong.
The
field was Baseline Duration rather than Variance. Formula had the right
field, it was my typing/thinking that was screwy.

I'll give the indicator field a try and see what that does for me.

Appreciate the help.
--
The only thing constant in life is change; therefore, you can follow
change
and allow it to control you, or lead change and control your destiny.

I also can't find the field [BaseLine Variance] in Project 2007.
Interesting.

I suspect it a custom field (probably one of the number fields).

In any event, PublicMike, why don't you use the field "Indicator" to
indicate if a task is overdue, i.e. late? Let Project do the work. Even
better than telling you when something is already over due, it will warn
you of the computed finish date is beyond the deadline. Better, I
think, to know before something is late that the current schedule is
planning for it to be late.



--rms




PublicMike wrote:
I pulled the Baseline Variance field directly out of a pick list in
project.
It does indeed exist. To test out my theory of the "is anything" being
the
problem, I replaced that line with "is equal to 4294967295" and now the
formula calculates. "4294967295" is apparently the code for #N/A, so
if the
baseline hasn't been set, which would produce and error, it has a value
to
associate with that and return an indicator.

What I was trying to accomplish is to set a graphical indicator that
alerts
when a task is overdue, as determined by the baseline, by a certain
percentage.

I think I've got it now. Thanks for the input.

 

[Jim Aksel]

PublicMike - Sure, I understand. Work and duration are not equal. What we
do sometimes is load the resources at $1/hr. That we we can using the
tracking features and it essentially relates to work. We're off your topic,
but it was worth the mention.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

Unfortuntately, we aren't applying cost to the work done, therefore
activities don't have a budgeted amount, hence the focus on dates only. This
is something to consider, however. We usually don't consider the labor cost
of performing the work. We have been discussing adding some $ amount on an
hourly basis to the resources, but their time is split between so many other
tasks and projects that often our estimates are based on x days to complete a
job that only requires 8 hours of work.

Thanks for the suggestion.
--
The only thing constant in life is change; therefore, you can follow change
and allow it to control you, or lead change and control your destiny.

Now it makes sense: [Finish Variance]/[Baseline Duration]. If you have
costed resources loaded to this schedule and a baseline, why not just use
Schedule Performance Index [SPI]? This is a comparison of what you have
accomplished divided by what you expected to accomplish within the given
time frame.

You guys are right. I went back and realized I'd typed the field wrong.
The
field was Baseline Duration rather than Variance. Formula had the right
field, it was my typing/thinking that was screwy.

I'll give the indicator field a try and see what that does for me.

Appreciate the help.
--
The only thing constant in life is change; therefore, you can follow
change
and allow it to control you, or lead change and control your destiny.


:

I also can't find the field [BaseLine Variance] in Project 2007.
Interesting.

I suspect it a custom field (probably one of the number fields).

In any event, PublicMike, why don't you use the field "Indicator" to
indicate if a task is overdue, i.e. late? Let Project do the work. Even
better than telling you when something is already over due, it will warn
you of the computed finish date is beyond the deadline. Better, I
think, to know before something is late that the current schedule is
planning for it to be late.



--rms




PublicMike wrote:
I pulled the Baseline Variance field directly out of a pick list in
project.
It does indeed exist. To test out my theory of the "is anything" being
the
problem, I replaced that line with "is equal to 4294967295" and now the
formula calculates. "4294967295" is apparently the code for #N/A, so
if the
baseline hasn't been set, which would produce and error, it has a value
to
associate with that and return an indicator.

What I was trying to accomplish is to set a graphical indicator that
alerts
when a task is overdue, as determined by the baseline, by a certain
percentage.

I think I've got it now. Thanks for the input.

 

[aeroplan]

Public mike,

I am having exactly the same issue as you regarding the progresstracking of
finish variance (and possibly start as an extra tracker) versus the baseline
durations.

Could you kindly send me a copy of the formula you used.

Many Thanks

You guys are right. I went back and realized I'd typed the field wrong. The
field was Baseline Duration rather than Variance. Formula had the right
field, it was my typing/thinking that was screwy.

I'll give the indicator field a try and see what that does for me.

Appreciate the help.
--
The only thing constant in life is change; therefore, you can follow change
and allow it to control you, or lead change and control your destiny.

I also can't find the field [BaseLine Variance] in Project 2007.
Interesting.

I suspect it a custom field (probably one of the number fields).

In any event, PublicMike, why don't you use the field "Indicator" to
indicate if a task is overdue, i.e. late? Let Project do the work. Even
better than telling you when something is already over due, it will warn
you of the computed finish date is beyond the deadline. Better, I
think, to know before something is late that the current schedule is
planning for it to be late.



--rms

I pulled the Baseline Variance field directly out of a pick list in project.
It does indeed exist. To test out my theory of the "is anything" being the
problem, I replaced that line with "is equal to 4294967295" and now the
formula calculates. "4294967295" is apparently the code for #N/A, so if the
baseline hasn't been set, which would produce and error, it has a value to
associate with that and return an indicator.

What I was trying to accomplish is to set a graphical indicator that alerts
when a task is overdue, as determined by the baseline, by a certain
percentage.

I think I've got it now. Thanks for the input.