none
Working with outline levels in Project 2007 formulas RRS feed

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

    Thanks in advance for this
    Gianluigi Goglio 


    • Edited by gigoglio Wednesday, March 26, 2014 10:47 AM
    Wednesday, March 26, 2014 10:43 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
    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
    Moderator
  • 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
    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()
    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
    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 gigoglio Wednesday, April 2, 2014 12:14 PM
    Thursday, March 27, 2014 3:28 PM