Calculated Field Expected % Complete Error

[Rob]

I have project 2007 pro-not using server.

I have used the formula from John below to get an expected/scheduled %
complete calculation but get #ERROR in Text1 Field for Milestones.

Any Ideas?

Thanks for your help.

*****

It will just take 3 custom fields instead of one. Here's the
basic outline of the process.
1. Create three custom fields (e.g. Text1, Duration1, and Duration2)
2. At the task level use the following formulas:

Text1=IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0
,0,format([Duration1]/[Duration2],"#.00%")))

Duration1=IIf(projdatediff([Baseline Start],[Date1])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline
Start],[Date1])<=0,0,projdatediff([Baseline Start],[Date1])))

Duration2=[Baseline Duration]

3. At the summary level use "rollup - sum" for Duration1 & Duration2
4. At the summary level "use formula" for Text1


John
Project MVP

*****

 

[Dave]

The formula contains a lot of division by [Duration2] which is likely to
be 0 for a milestone.

It looks like you need to embed the whole formula in something which
describes behaviour for the case where that happens:

Text1 = IIf( [Duration2]=0 , <describe behaviour for milestones> ,
<embed John's formula from below> )

I haven't checked these details but the division by 0 is the starting
point for me.

 

[Jim Aksel]

How are you defining [Date1]? It should be [Status Date] or [Current Date]
depending on what meets your needs.

Duration1 compares the penetration of Date1 into the baseline duration
period and will return a value of 0 if it is prior to the baseline start
date. The greatest value returned is the baseline duration.

John's formula compares to baselines. Sometimes it is useful to use the
current forecast. It should be easy for you to knock that off.

I don't see any obvious typo's or logic errors in the formula posted.
However, that is John's formula not yours, correct? What exactly did you
type? I suspect one of two things (1) Mismatched parethesis, or, (2) Date1
is undefined in your schedule.

I have a slightly different approach and a rant about this topic on my blog,
see link below.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

 

[Rob]

Yea, that works.

I basically had to combine a formula that was simple but did not work well
at the summary level with the one John made. I think I could trim some out
but for now, its working exactly as needed.

Here is the solution for Text1:

IIf([Duration2]=0, IIf([Current Date]<[Start],0,IIf([Current
Date]>[Finish],100,((ProjDateDiff([Start],[Current Date],[Project
Calendar])/[Duration]))*100)),(IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0,0,format([Duration1]/[Duration2],"#%")))))

The formula contains a lot of division by [Duration2] which is likely to
be 0 for a milestone.

It looks like you need to embed the whole formula in something which
describes behaviour for the case where that happens:

Text1 = IIf( [Duration2]=0 , <describe behaviour for milestones> ,
<embed John's formula from below> )

I haven't checked these details but the division by 0 is the starting
point for me.

I have project 2007 pro-not using server.

I have used the formula from John below to get an expected/scheduled %
complete calculation but get #ERROR in Text1 Field for Milestones.

Any Ideas?

Thanks for your help.

*****

It will just take 3 custom fields instead of one. Here's the
basic outline of the process.
1. Create three custom fields (e.g. Text1, Duration1, and Duration2)
2. At the task level use the following formulas:

Text1=IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0
,0,format([Duration1]/[Duration2],"#.00%")))

Duration1=IIf(projdatediff([Baseline Start],[Date1])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline
Start],[Date1])<=0,0,projdatediff([Baseline Start],[Date1])))

Duration2=[Baseline Duration]

3. At the summary level use "rollup - sum" for Duration1 & Duration2
4. At the summary level "use formula" for Text1


John
Project MVP

*****

 

[Rob]

Jim,

You are right, John made an ammendment to his Duration1 formula. Pasted
from my Duration1 formula containing his corrections.

IIf(projdatediff([Baseline Start],[Current Date])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline Start],[Current
Date])<=0,0,projdatediff([Baseline Start],[Current Date])))

Good catch.

Your current forcast idea has me curious. Care to expound?

Everything is working perfect now. I was just getting errors from the
"/Duration2" result and needed a workaround for tasks that have no duration.

How are you defining [Date1]? It should be [Status Date] or [Current Date]
depending on what meets your needs.

Duration1 compares the penetration of Date1 into the baseline duration
period and will return a value of 0 if it is prior to the baseline start
date. The greatest value returned is the baseline duration.

John's formula compares to baselines. Sometimes it is useful to use the
current forecast. It should be easy for you to knock that off.

I don't see any obvious typo's or logic errors in the formula posted.
However, that is John's formula not yours, correct? What exactly did you
type? I suspect one of two things (1) Mismatched parethesis, or, (2) Date1
is undefined in your schedule.

I have a slightly different approach and a rant about this topic on my blog,
see link below.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

I have project 2007 pro-not using server.

I have used the formula from John below to get an expected/scheduled %
complete calculation but get #ERROR in Text1 Field for Milestones.

Any Ideas?

Thanks for your help.

*****

It will just take 3 custom fields instead of one. Here's the
basic outline of the process.
1. Create three custom fields (e.g. Text1, Duration1, and Duration2)
2. At the task level use the following formulas:

Text1=IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0
,0,format([Duration1]/[Duration2],"#.00%")))

Duration1=IIf(projdatediff([Baseline Start],[Date1])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline
Start],[Date1])<=0,0,projdatediff([Baseline Start],[Date1])))

Duration2=[Baseline Duration]

3. At the summary level use "rollup - sum" for Duration1 & Duration2
4. At the summary level "use formula" for Text1


John
Project MVP

*****

 

[Jim Aksel]

I'd love to expound volumes. I think the best start I can give you is to
refer to my blog (link below). On the left side, click "MS Project Tips" and
it is the first item on the new page.

This was first brought to my attention from another blogger (sorry, I forget
his name). The issue is getting the "Should be" roll-ups to be correct. It
depends on how you are weighting the tasks. Project wants us to weight by
duration, work, or budget.

I might have a PPT or a white paper on this. It may take me awhile, becuase
the "Revenue Hours Generating Customers" are keeping me very busy. You can
dig my email off the blog.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

Jim,

You are right, John made an ammendment to his Duration1 formula. Pasted
from my Duration1 formula containing his corrections.

IIf(projdatediff([Baseline Start],[Current Date])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline Start],[Current
Date])<=0,0,projdatediff([Baseline Start],[Current Date])))

Good catch.

Your current forcast idea has me curious. Care to expound?

Everything is working perfect now. I was just getting errors from the
"/Duration2" result and needed a workaround for tasks that have no duration.

How are you defining [Date1]? It should be [Status Date] or [Current Date]
depending on what meets your needs.

Duration1 compares the penetration of Date1 into the baseline duration
period and will return a value of 0 if it is prior to the baseline start
date. The greatest value returned is the baseline duration.

John's formula compares to baselines. Sometimes it is useful to use the
current forecast. It should be easy for you to knock that off.

I don't see any obvious typo's or logic errors in the formula posted.
However, that is John's formula not yours, correct? What exactly did you
type? I suspect one of two things (1) Mismatched parethesis, or, (2) Date1
is undefined in your schedule.

I have a slightly different approach and a rant about this topic on my blog,
see link below.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

I have project 2007 pro-not using server.

I have used the formula from John below to get an expected/scheduled %
complete calculation but get #ERROR in Text1 Field for Milestones.

Any Ideas?

Thanks for your help.

*****

It will just take 3 custom fields instead of one. Here's the
basic outline of the process.
1. Create three custom fields (e.g. Text1, Duration1, and Duration2)
2. At the task level use the following formulas:

Text1=IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0
,0,format([Duration1]/[Duration2],"#.00%")))

Duration1=IIf(projdatediff([Baseline Start],[Date1])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline
Start],[Date1])<=0,0,projdatediff([Baseline Start],[Date1])))

Duration2=[Baseline Duration]

3. At the summary level use "rollup - sum" for Duration1 & Duration2
4. At the summary level "use formula" for Text1


John
Project MVP

*****

 

[gdallas]

[FONT=&quot]Hi Jim, [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have followed your white paper, and I have been unable to get anything other than “0” for my expected % column. I’m sure I have set it up as per your instructions. [/FONT]
[FONT=&quot]I swapped out [Status Date] with [Current Date] to see if that would fix it for me, but it hasn’t. Also, will your method take account of non working time such as holidays, as a previous method that I found on the internet appeared to work right until holidays screwed it up, [/FONT]
[FONT=&quot]Are you able to see where I have gone wrong please with the attached sheet?[/FONT]