# 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 4

I 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?

Gianluigi Goglio

• Edited by Wednesday, March 26, 2014 10:47 AM
Wednesday, March 26, 2014 10:43 AM

• 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()
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 Wednesday, April 2, 2014 12:14 PM
Wednesday, March 26, 2014 7:23 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 rolled-up 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 rolled-up 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

Wednesday, March 26, 2014 2:47 PM
• 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

Gianluigi

• Edited by Wednesday, March 26, 2014 4:02 PM
Wednesday, March 26, 2014 4:01 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()
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 Wednesday, April 2, 2014 12:14 PM
Wednesday, March 26, 2014 7:23 PM
• Thanks John,

It worked perfectly! I'have just added an if clause for null values on Work field.

Your code was exactly what I was looking for.

Thanks again.

Gianluigi

• Edited by Wednesday, April 2, 2014 12:14 PM
Thursday, March 27, 2014 3:28 PM