Help with Custom Field Calculation

[Cindy]

I seem to be having a (blonde, senior, stupid - take your pick)
moment...but...I can't figure out how to handle this formula.

We are in need of 3 different number fields with the following
criteria:
1. Task weight - PM sets this at the lowest level task - each summary
task at outline level 3 must equal 100
2. Milestone Weight - this is to be set at outline level 3 only
3. WP Weight - At outline level 2 this should be the sum of all
outline level 3 entries below it (note: there could be multiple
outline level 2 tasks in each schedule).

My problem is number 3 I currently have the following formula for the
field IIf([Outline Level]=3,[Number2],0) - this is setting the field
correctly on outline level 3...but...how to I add up the values to the
parent level 2. I tried this (don't laugh..) IIf([Outline Level]=3,
[Number2],IIf( [Outline Level] =2, sum[Milestone Weight] , 0 ) ) but
of course it didn't work.

Any ideas would be very much appreciated!!

....Cindy

 

[Jack Dahlgren]

Cindy,

Summary task fields can either use a formula or aggregate the values from
tasks below them. They can not do both. It looks to me that you DO need to
do both, so using an iif statement in combination with summing may work. It
will require the use of more fields though.

For example you would need task weight for individual tasks. The summary of
task weights at outline level 3 could be the sum of the subtasks. You would
also have a task weight field for summary tasks at level 3 only (with a
formula to set it to 100 depending on outline level, then you could have
ANOTHER field which sums those two fields. This could be quite messy and
confusing to people though. I'm only going on a couple minutes of thought
here, but I THINK there is probably a way to make it work, however, it is
possible that it is impossible.

I think in this situation you are better off using a little bit of VBA to
generate these numbers and plug them into the right fields. The user would
just have to click a button to run the macro.

I think that is what I'd do.
Here is some very raw code. It can be optimized, but I'm following the
requirements step by step. Note that there is no activity involved in step
2. It is not clear where the milestone weights are derived from:

Sub Cindy()
Dim ts As Tasks
Dim cts As Tasks
Dim t As Task
Dim ct As Task
Dim wp As Long

Set ts = ActiveProject.Tasks
'first set level3 tasks to 100
For Each t In ts
If Not t Is Nothing Then
If t.OutlineLevel = 3 Then
t.Number1 = 100
End If
End If
Next t

'then rollup the value the milestones
'from level 3 (assuming the value is in Number2 field)
For Each t In ts
If Not t Is Nothing Then
wp = 0
If t.OutlineLevel = 2 Then
Set cts = t.OutlineChildren
For Each ct In cts
If ct.OutlineLevel = 3 Then
wp = wp + ct.Number2
End If
Next ct
t.Number2 = wp
End If
Next t

End Sub

I'm not sure you have defined the relationship clearly enough to make this
useful but post back if you have any questions.

-Jack Dahlgren

I seem to be having a (blonde, senior, stupid - take your pick)
moment...but...I can't figure out how to handle this formula.

We are in need of 3 different number fields with the following
criteria:
1. Task weight - PM sets this at the lowest level task - each summary
task at outline level 3 must equal 100
2. Milestone Weight - this is to be set at outline level 3 only
3. WP Weight - At outline level 2 this should be the sum of all
outline level 3 entries below it (note: there could be multiple
outline level 2 tasks in each schedule).

My problem is number 3 I currently have the following formula for the
field IIf([Outline Level]=3,[Number2],0) - this is setting the field
correctly on outline level 3...but...how to I add up the values to the
parent level 2. I tried this (don't laugh..) IIf([Outline Level]=3,
[Number2],IIf( [Outline Level] =2, sum[Milestone Weight] , 0 ) ) but
of course it didn't work.

Any ideas would be very much appreciated!!

...Cindy

 

[Cindy]

Jack,

Duh...I don't know why I didn't think of code.

This was very helpful. I didn't actually need the first step. While I said
that level 3 must equal 100 I simply meant the roll up of Task Weight should
equal 100 - I don't want to force it.

As far as setting number 3, the code works fine until I get to the
"t.number3 = wp" line. I get a "Run-time error 1101 The argument value is
not valid".

I thought it might be because wp was defined as long and number3 is double
so I redefined wp as double. Same error. I verified that wp is adding up
correctly with a watch.

I'm doing some research of my own but thought you might quickly know the
answer.

....Cindy

Cindy,

Summary task fields can either use a formula or aggregate the values from
tasks below them. They can not do both. It looks to me that you DO need to
do both, so using an iif statement in combination with summing may work. It
will require the use of more fields though.

For example you would need task weight for individual tasks. The summary of
task weights at outline level 3 could be the sum of the subtasks. You would
also have a task weight field for summary tasks at level 3 only (with a
formula to set it to 100 depending on outline level, then you could have
ANOTHER field which sums those two fields. This could be quite messy and
confusing to people though. I'm only going on a couple minutes of thought
here, but I THINK there is probably a way to make it work, however, it is
possible that it is impossible.

I think in this situation you are better off using a little bit of VBA to
generate these numbers and plug them into the right fields. The user would
just have to click a button to run the macro.

I think that is what I'd do.
Here is some very raw code. It can be optimized, but I'm following the
requirements step by step. Note that there is no activity involved in step
2. It is not clear where the milestone weights are derived from:

Sub Cindy()
Dim ts As Tasks
Dim cts As Tasks
Dim t As Task
Dim ct As Task
Dim wp As Long

Set ts = ActiveProject.Tasks
'first set level3 tasks to 100
For Each t In ts
If Not t Is Nothing Then
If t.OutlineLevel = 3 Then
t.Number1 = 100
End If
End If
Next t

'then rollup the value the milestones
'from level 3 (assuming the value is in Number2 field)
For Each t In ts
If Not t Is Nothing Then
wp = 0
If t.OutlineLevel = 2 Then
Set cts = t.OutlineChildren
For Each ct In cts
If ct.OutlineLevel = 3 Then
wp = wp + ct.Number2
End If
Next ct
t.Number2 = wp
End If
Next t

End Sub

I'm not sure you have defined the relationship clearly enough to make this
useful but post back if you have any questions.

-Jack Dahlgren

I seem to be having a (blonde, senior, stupid - take your pick)
moment...but...I can't figure out how to handle this formula.

We are in need of 3 different number fields with the following
criteria:
1. Task weight - PM sets this at the lowest level task - each summary
task at outline level 3 must equal 100
2. Milestone Weight - this is to be set at outline level 3 only
3. WP Weight - At outline level 2 this should be the sum of all
outline level 3 entries below it (note: there could be multiple
outline level 2 tasks in each schedule).

My problem is number 3 I currently have the following formula for the
field IIf([Outline Level]=3,[Number2],0) - this is setting the field
correctly on outline level 3...but...how to I add up the values to the
parent level 2. I tried this (don't laugh..) IIf([Outline Level]=3,
[Number2],IIf( [Outline Level] =2, sum[Milestone Weight] , 0 ) ) but
of course it didn't work.

Any ideas would be very much appreciated!!

...Cindy

 

[Cindy]

I feel really dumb now....as soon as I posted my reply I realized that I
never cleared the formula out of the custom field. After doing that the code
works like a charm!!

Thanks for all your help!

....Cindy

Jack,

Duh...I don't know why I didn't think of code.

This was very helpful. I didn't actually need the first step. While I said
that level 3 must equal 100 I simply meant the roll up of Task Weight should
equal 100 - I don't want to force it.

As far as setting number 3, the code works fine until I get to the
"t.number3 = wp" line. I get a "Run-time error 1101 The argument value is
not valid".

I thought it might be because wp was defined as long and number3 is double
so I redefined wp as double. Same error. I verified that wp is adding up
correctly with a watch.

I'm doing some research of my own but thought you might quickly know the
answer.

...Cindy

Cindy,

Summary task fields can either use a formula or aggregate the values from
tasks below them. They can not do both. It looks to me that you DO need to
do both, so using an iif statement in combination with summing may work. It
will require the use of more fields though.

For example you would need task weight for individual tasks. The summary of
task weights at outline level 3 could be the sum of the subtasks. You would
also have a task weight field for summary tasks at level 3 only (with a
formula to set it to 100 depending on outline level, then you could have
ANOTHER field which sums those two fields. This could be quite messy and
confusing to people though. I'm only going on a couple minutes of thought
here, but I THINK there is probably a way to make it work, however, it is
possible that it is impossible.

I think in this situation you are better off using a little bit of VBA to
generate these numbers and plug them into the right fields. The user would
just have to click a button to run the macro.

I think that is what I'd do.
Here is some very raw code. It can be optimized, but I'm following the
requirements step by step. Note that there is no activity involved in step
2. It is not clear where the milestone weights are derived from:

Sub Cindy()
Dim ts As Tasks
Dim cts As Tasks
Dim t As Task
Dim ct As Task
Dim wp As Long

Set ts = ActiveProject.Tasks
'first set level3 tasks to 100
For Each t In ts
If Not t Is Nothing Then
If t.OutlineLevel = 3 Then
t.Number1 = 100
End If
End If
Next t

'then rollup the value the milestones
'from level 3 (assuming the value is in Number2 field)
For Each t In ts
If Not t Is Nothing Then
wp = 0
If t.OutlineLevel = 2 Then
Set cts = t.OutlineChildren
For Each ct In cts
If ct.OutlineLevel = 3 Then
wp = wp + ct.Number2
End If
Next ct
t.Number2 = wp
End If
Next t

End Sub

I'm not sure you have defined the relationship clearly enough to make this
useful but post back if you have any questions.

-Jack Dahlgren

I seem to be having a (blonde, senior, stupid - take your pick)
moment...but...I can't figure out how to handle this formula.

We are in need of 3 different number fields with the following
criteria:
1. Task weight - PM sets this at the lowest level task - each summary
task at outline level 3 must equal 100
2. Milestone Weight - this is to be set at outline level 3 only
3. WP Weight - At outline level 2 this should be the sum of all
outline level 3 entries below it (note: there could be multiple
outline level 2 tasks in each schedule).

My problem is number 3 I currently have the following formula for the
field IIf([Outline Level]=3,[Number2],0) - this is setting the field
correctly on outline level 3...but...how to I add up the values to the
parent level 2. I tried this (don't laugh..) IIf([Outline Level]=3,
[Number2],IIf( [Outline Level] =2, sum[Milestone Weight] , 0 ) ) but
of course it didn't work.

Any ideas would be very much appreciated!!

...Cindy