none
Custom field formula error RRS feed

Answers

  • alarranaga,

    I guess the other two guys aren't on-line yet, so let me address your issue.

    Try the following syntax:

    IIf([Baseline Finish]>50000,"no baseline",IIf([Cost]/[Baseline Cost]>1.2,"overbudget by 20% or more",IIf([Cost]/[Baseline Cost]>1.005,"overbudget","under budget")))

    I'm not sure why the last test needs 1.005 - may be due to rounding error or it may be a glitch in Project 2010 - I didn't try the formula in earlier versions of Project.

    Hope this helps.

    John

    • Marked as answer by alarranaga Wednesday, January 4, 2012 8:21 AM
    Monday, January 2, 2012 5:36 PM
  • Hans,

    Commas versus semicolons is determined by the system setting for list separator. Depending on alarranaga's system settings, switching to a semicolon separator may not help. As a test I changed my list separator from a comma to a semicolon and the formula still doesn't work. Before posting yesterday I tried all three variations of formulas presented in the msdn article cited above. None of them work completely. In particular the "underbudget" part of the formula gives incorrect results in a simple test file. However, the formula I post does work.

    I'm using Project 2010 Pro under Windows XP but I do not have SP1 installed. Am I missing something?

    By the way, I put the formula in Text1.

    John

    Update: Further testing shows that the msdn formula does work if the baseline is set by Project. However it does not always work for manually entered baseline cost data.
    • Edited by John - Project Tuesday, January 3, 2012 6:34 PM
    • Marked as answer by alarranaga Wednesday, January 4, 2012 8:21 AM
    Tuesday, January 3, 2012 4:34 PM

All replies

  • What is the formula?
    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Friday, December 23, 2011 6:44 PM
    Moderator
  • Dear alarranaga,

    If you will share the formula with us then will more helpful, any way you shoud try that formula in Project Professional first, then use the same formula (copy and paste) to Project server custom fields.

    and let us know the formula... :)


    Avinash kumar | e2eProjects Pvt. Ltd | Blog:http://om-avinash.blogspot.com
    Tuesday, December 27, 2011 2:27 PM
  • Sorry for the late answer, but I've been on holidays,...

    the formulas I get the error are the following:

     

    Switch(Len(CStr([Baseline Finish])) < 3, "No baseline",
    ([Cost] + 1) / ([Baseline Cost] + 1) > 1.2, "Overbudget by 20% or more",
    ([Cost] + 1) / ([Baseline Cost] + 1) > 1, "Overbudget",
    True, "Under budget")

     

    Switch(Not ([Baseline Estimated Finish] >= #1/1/1984#
    And [Baseline Estimated Finish] < #1/1/2050#), "No Baseline",
    ([Cost] + 1) / ([Baseline Cost] + 1) > 1.2, "Overbudget by 20% or more",
    ([Cost] + 1) / ([Baseline Cost] + 1) > 1, "Overbudget",
    True, "Under budget")

    Thanks and sorry for the delay

    Monday, January 2, 2012 1:18 PM
  • alarranaga,

    I guess the other two guys aren't on-line yet, so let me address your issue.

    Try the following syntax:

    IIf([Baseline Finish]>50000,"no baseline",IIf([Cost]/[Baseline Cost]>1.2,"overbudget by 20% or more",IIf([Cost]/[Baseline Cost]>1.005,"overbudget","under budget")))

    I'm not sure why the last test needs 1.005 - may be due to rounding error or it may be a glitch in Project 2010 - I didn't try the formula in earlier versions of Project.

    Hope this helps.

    John

    • Marked as answer by alarranaga Wednesday, January 4, 2012 8:21 AM
    Monday, January 2, 2012 5:36 PM
  • Hi alarranaga,

    You need to replace the commas by semicolons. In addition, you can check for the existance of a date value by using ProjDateValue.

    The results in following formula that should work:

    Switch([Baseline Finish]=ProjDateValue("NA");"No baseline";([Cost]+1)/([Baseline Cost]+1)>1,2;"Overbudget by 20% or more";([Cost]+1)/([Baseline Cost]+1)>1;"Overbudget";True;"Under budget")

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Monday, January 2, 2012 10:12 PM
    Moderator
  • Hans,

    Commas versus semicolons is determined by the system setting for list separator. Depending on alarranaga's system settings, switching to a semicolon separator may not help. As a test I changed my list separator from a comma to a semicolon and the formula still doesn't work. Before posting yesterday I tried all three variations of formulas presented in the msdn article cited above. None of them work completely. In particular the "underbudget" part of the formula gives incorrect results in a simple test file. However, the formula I post does work.

    I'm using Project 2010 Pro under Windows XP but I do not have SP1 installed. Am I missing something?

    By the way, I put the formula in Text1.

    John

    Update: Further testing shows that the msdn formula does work if the baseline is set by Project. However it does not always work for manually entered baseline cost data.
    • Edited by John - Project Tuesday, January 3, 2012 6:34 PM
    • Marked as answer by alarranaga Wednesday, January 4, 2012 8:21 AM
    Tuesday, January 3, 2012 4:34 PM