customized functionProject 2000

[Stuart]

I am trying to create a custom duration column to calculate the variance of a
critical path, but all of my attempts yield an error. I've tried the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the project
rollup row should be the value that I want.

Help me, please

 

[JulieS]

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks for
the summary task or did you set the summary tasks to sum? (Customize field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie

 

[JackD]

Did you try?:

IIF([critical],[duration2]-[duration1],0)

The square brackets around field names are required.

 

[Stuart]

Thanks

That helps

Also, if I use

IIF([critical], (([duration2]-[duration1])/6)^2,0)

to get the Variance, I get wierd answers. It works fine without the ^2

I also tried

IIF([critical], (([duration2]-[duration1])/6)*(([duration2]-[duration1])/6),0)

That gives me the same wrong answer as ^2. There seems to be a problem in
the ^ math Anybody seen or heard about that?

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks for
the summary task or did you set the summary tasks to sum? (Customize field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie

I am trying to create a custom duration column to calculate the variance of
a
critical path, but all of my attempts yield an error. I've tried the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the project
rollup row should be the value that I want.

Help me, please

 

[JulieS]

Hi Stuart,

I do get accurate answers (mathematically) when I use:

IIf([Critical],(([Duration2]-[Duration1])/6)^2,0)

A couple of observations:
[duration2]-[duration1] in a *number* field would equal the number of
minutes between the two durations. So why divide by 6?
Dividing by 60 would give you the number of hours.
I guess I am a bit confused why you want to take a variance and then square
it?

If you are trying to calculate the number of days between the two durations
you will need to divide usually by 480 (60 minutes per hour times 6 hours
per day) if the formula is in a number field. If you are using a number of
hours per day less than 8, make sure both your calendar (Tools>Change
working time) reflects the different working hours as well as the hours per
day is set correctly in Tool>Options, Calendar tab. A miss-match will cause
unusual results.

Hope this helps. Let us know how you get along.

Julie

Thanks

That helps

Also, if I use

IIF([critical], (([duration2]-[duration1])/6)^2,0)

to get the Variance, I get wierd answers. It works fine without the ^2

I also tried

IIF([critical],
(([duration2]-[duration1])/6)*(([duration2]-[duration1])/6),0)

That gives me the same wrong answer as ^2. There seems to be a problem in
the ^ math Anybody seen or heard about that?

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks for
the summary task or did you set the summary tasks to sum? (Customize
field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie

I am trying to create a custom duration column to calculate the variance
of
a
critical path, but all of my attempts yield an error. I've tried the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the
project
rollup row should be the value that I want.

Help me, please

 

[JulieS]

OOPS...should be 60 minutes per hour times 8 hours per day in the second
section. ... sorry that's what I get for trying to do too many things at
once.

J

Hi Stuart,

I do get accurate answers (mathematically) when I use:

IIf([Critical],(([Duration2]-[Duration1])/6)^2,0)

A couple of observations:
[duration2]-[duration1] in a *number* field would equal the number of
minutes between the two durations. So why divide by 6?
Dividing by 60 would give you the number of hours.
I guess I am a bit confused why you want to take a variance and then
square it?

If you are trying to calculate the number of days between the two
durations you will need to divide usually by 480 (60 minutes per hour
times 6 hours per day) if the formula is in a number field. If you are
using a number of hours per day less than 8, make sure both your calendar
(Tools>Change working time) reflects the different working hours as well
as the hours per day is set correctly in Tool>Options, Calendar tab. A
miss-match will cause unusual results.

Hope this helps. Let us know how you get along.

Julie

Thanks

That helps

Also, if I use

IIF([critical], (([duration2]-[duration1])/6)^2,0)

to get the Variance, I get wierd answers. It works fine without the ^2

I also tried

IIF([critical],
(([duration2]-[duration1])/6)*(([duration2]-[duration1])/6),0)

That gives me the same wrong answer as ^2. There seems to be a problem
in
the ^ math Anybody seen or heard about that?

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks
for
the summary task or did you set the summary tasks to sum? (Customize
field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie
I am trying to create a custom duration column to calculate the
variance of
a
critical path, but all of my attempts yield an error. I've tried the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the
project
rollup row should be the value that I want.

Help me, please

 

[Stuart]

Thank you so-o-o much. I just discovered the constant 480 also. It's s good
thing I like puzzles. I recall pointing this out to MS several years ago (I
had forgotten about that until now) but it does not appear in the literature.
Do you have any nidea why they go to minutes?

Thanks again. You have been a great help.

Hi Stuart,

I do get accurate answers (mathematically) when I use:

IIf([Critical],(([Duration2]-[Duration1])/6)^2,0)

A couple of observations:
[duration2]-[duration1] in a *number* field would equal the number of
minutes between the two durations. So why divide by 6?
Dividing by 60 would give you the number of hours.
I guess I am a bit confused why you want to take a variance and then square
it?

If you are trying to calculate the number of days between the two durations
you will need to divide usually by 480 (60 minutes per hour times 6 hours
per day) if the formula is in a number field. If you are using a number of
hours per day less than 8, make sure both your calendar (Tools>Change
working time) reflects the different working hours as well as the hours per
day is set correctly in Tool>Options, Calendar tab. A miss-match will cause
unusual results.

Hope this helps. Let us know how you get along.

Julie

Thanks

That helps

Also, if I use

IIF([critical], (([duration2]-[duration1])/6)^2,0)

to get the Variance, I get wierd answers. It works fine without the ^2

I also tried

IIF([critical],
(([duration2]-[duration1])/6)*(([duration2]-[duration1])/6),0)

That gives me the same wrong answer as ^2. There seems to be a problem in
the ^ math Anybody seen or heard about that?

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks for
the summary task or did you set the summary tasks to sum? (Customize
field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie
I am trying to create a custom duration column to calculate the variance
of
a
critical path, but all of my attempts yield an error. I've tried the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the
project
rollup row should be the value that I want.

Help me, please

 

[Jan De Messemaeker]

Hi Stuart,

Indeed, sometimes people ask for seconds )

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
+32-495-300 620

Thank you so-o-o much. I just discovered the constant 480 also. It's s good
thing I like puzzles. I recall pointing this out to MS several years ago (I
had forgotten about that until now) but it does not appear in the literature.
Do you have any nidea why they go to minutes?

Thanks again. You have been a great help.

Hi Stuart,

I do get accurate answers (mathematically) when I use:

IIf([Critical],(([Duration2]-[Duration1])/6)^2,0)

A couple of observations:
[duration2]-[duration1] in a *number* field would equal the number of
minutes between the two durations. So why divide by 6?
Dividing by 60 would give you the number of hours.
I guess I am a bit confused why you want to take a variance and then square
it?

If you are trying to calculate the number of days between the two durations
you will need to divide usually by 480 (60 minutes per hour times 6 hours
per day) if the formula is in a number field. If you are using a number of
hours per day less than 8, make sure both your calendar (Tools>Change
working time) reflects the different working hours as well as the hours per
day is set correctly in Tool>Options, Calendar tab. A miss-match will cause
unusual results.

Hope this helps. Let us know how you get along.

Julie

Thanks

That helps

Also, if I use
IIF([critical], (([duration2]-[duration1])/6)^2,0)
to get the Variance, I get wierd answers. It works fine without the ^2

I also tried
IIF([critical],
(([duration2]-[duration1])/6)*(([duration2]-[duration1])/6),0)

That gives me the same wrong answer as ^2. There seems to be a problem in
the ^ math Anybody seen or heard about that?



:

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks for
the summary task or did you set the summary tasks to sum? (Customize
field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie
I am trying to create a custom duration column to calculate the variance
of
a
critical path, but all of my attempts yield an error. I've tried the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the
project
rollup row should be the value that I want.

Help me, please

 

[JulieS]

Hi Stuart,

You are most welcome and thanks for the feedback.

As to why everything in Project is calculated in minutes, I don't know the
particular reasoning behind this, just I do know many folks who do want the
level of detail for hours and minutes. As Jan said, some posters to this
newsgroup have wanted the level of seconds. A bit too detailed for me, but
some are trying to use Project for *very* detailed processes.

Do let us know if we can assist again in the future.

Julie

Thank you so-o-o much. I just discovered the constant 480 also. It's s
good
thing I like puzzles. I recall pointing this out to MS several years ago
(I
had forgotten about that until now) but it does not appear in the
literature.
Do you have any nidea why they go to minutes?

Thanks again. You have been a great help.

Hi Stuart,

I do get accurate answers (mathematically) when I use:

IIf([Critical],(([Duration2]-[Duration1])/6)^2,0)

A couple of observations:
[duration2]-[duration1] in a *number* field would equal the number of
minutes between the two durations. So why divide by 6?
Dividing by 60 would give you the number of hours.
I guess I am a bit confused why you want to take a variance and then
square
it?

If you are trying to calculate the number of days between the two
durations
you will need to divide usually by 480 (60 minutes per hour times 6 hours
per day) if the formula is in a number field. If you are using a number
of
hours per day less than 8, make sure both your calendar (Tools>Change
working time) reflects the different working hours as well as the hours
per
day is set correctly in Tool>Options, Calendar tab. A miss-match will
cause
unusual results.

Hope this helps. Let us know how you get along.

Julie

Thanks

That helps

Also, if I use
IIF([critical], (([duration2]-[duration1])/6)^2,0)
to get the Variance, I get wierd answers. It works fine without the ^2

I also tried
IIF([critical],
(([duration2]-[duration1])/6)*(([duration2]-[duration1])/6),0)

That gives me the same wrong answer as ^2. There seems to be a problem
in
the ^ math Anybody seen or heard about that?



:

Hi Stuart,

A couple of issues come to mind:

1) The formula should be:

IIF([critical], [duration2]-[duration1],0)

All field references should include the square brackets.

2) Have you populated data into the [Duration1] and [Duration2] tasks
for
the summary task or did you set the summary tasks to sum? (Customize
field
dialog box).

3) You mention the project rollup row - I assume you mean the project
summary task. Again, you will need to set the "Calculation for task
and
group summary rows" to sum.

Hope this helps -- let us know how you get along.

Julie
I am trying to create a custom duration column to calculate the
variance
of
a
critical path, but all of my attempts yield an error. I've tried
the
following, and many variations.
IIF([critical],duration2-duration1,0)

duration2 and duration 1 are populated fields, and a "sum" in the
project
rollup row should be the value that I want.

Help me, please