Answered by:
Working with outline levels in Project 2007 formulas
Question

I’d a question for you about project formulas, if you may kindly address me.How to write a formula in a customized column with correct syntax for this case:
I have two colums:
Baseline Work at Outline Level 3 only
Work at Outline Level 4I would like to show in a third customized column a progress percentage ([Work]/[Baseline Work]*100) showing results at each Outline Level 3, calculated on items at Outline Level 4 belonging to that Outline Level 3.
May you kindly give me any input?
Thanks in advance for this
Gianluigi Goglio Edited by gigoglio Wednesday, March 26, 2014 10:47 AM
Answers

gigoglio,
Here's a piece of information that I think is relevant to what you think you want to do. A formula in a custom field can only operate on data that is in the same row. In your case you are asking for a custom field formula to calculate level 4 progress using data from level 3  sorry can't do it. You can however, use a simple VBA procedure to get what you want. Try the following:
Sub CalGigoglioProgress()
Dim t As Task
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel = 4 Then
t.Text1 = Format(t.Work / t.OutlineParent.BaselineWork * 100, "#.0") & " %"
t.OutlineParent.Text1 = Format(t.OutlineParent.Work / t.OutlineParent.BaselineWork _
* 100, "#.0") & " %"
End If
End If
Next t
End Sub
John
 Marked as answer by gigoglio Wednesday, April 2, 2014 12:14 PM
All replies

Hi Gianluigi,
Please correct me if I am wrong.
As far as I understood, you want to new field to be at level 3, calculating work divided by baseline work. Baseline work is only at level 3 and work is at level 4. We assum that work is rolledup so work is also summed at level 4 right?
Something like that where task 2 has been updated so baseline work and work are different:
Then why not simply having a formula like:
iif(outline level=3, work/baseline,NA).
Doing this you'll just have the calculation at the right level, using the work rolledup at summary rows.
Just as a comment, wouldn't you use the actual work instead of work for calculating progress versus baseline?
Hope this helps.
Guillaume Rouyre  MBA, MCP, MCTS

Hi Guillaume,
What I would like to obtain is the situation below, where the last column is a percent of middle column value/left column value.
Left column value is only entered at outline level 3. My goal is to have percent on third column, at least at outline level 3 (82.5%), percent at outline level 4 are a plus.
Ore vendute (baseline work)
Ore attuali (work)
% avanzamento (% progress)
446 hrs
368 hrs
82.5
0 hrs
50 hrs
11.2
0 hrs
150 hrs
33.6
0 hrs
102 hrs
22.9
0 hrs
32 hrs
7.2
0 hrs
16 hrs
3.6
0 hrs
18 hrs
4.0
Thanks again for your help
Gianluigi
 Edited by gigoglio Wednesday, March 26, 2014 4:02 PM

gigoglio,
Here's a piece of information that I think is relevant to what you think you want to do. A formula in a custom field can only operate on data that is in the same row. In your case you are asking for a custom field formula to calculate level 4 progress using data from level 3  sorry can't do it. You can however, use a simple VBA procedure to get what you want. Try the following:
Sub CalGigoglioProgress()
Dim t As Task
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel = 4 Then
t.Text1 = Format(t.Work / t.OutlineParent.BaselineWork * 100, "#.0") & " %"
t.OutlineParent.Text1 = Format(t.OutlineParent.Work / t.OutlineParent.BaselineWork _
* 100, "#.0") & " %"
End If
End If
Next t
End Sub
John
 Marked as answer by gigoglio Wednesday, April 2, 2014 12:14 PM
