Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
Custom Field with Formula

Answered Custom Field with Formula

  • Thursday, April 01, 2010 1:28 PM
     
     
    Sign In to Vote
    0

    We are having difficulty understanding the logic by which custom fields with formulas work. This seems simple enough, but things that should obviously be working return an error for the formula. To debug this is hardly possible.

    Example
    We want to have a calculated field that shows us whether the work performed on a task is in relation to the cost. This should be a simple calculation. Percentage of cost at any time is: Actual Cost for task / Budgeted Cost for task (in formula language [Actual Cost] / [Cost]). And percentage of work performed is already available in [% Work Completed].

    To calculate the relationship between these we have defined this formula:
    Switch(
    [% Work Complete] > 0 And [Actual Cost] = 0,1,
    [% Work Complete] = 0 And [Actual Cost] = 0,1,
    [% Work Complete] = 0 And [Actual Cost] > 0,0,
    [% Work Complete] > 0 And [Cost] = 0,0,
    [% Work Complete] > 0 And [Actual Cost] > 0 And [Cost] > 0,[% Work Complete] / ([Actual Cost]/[Cost]))

    Only if all values are larger than 0 will a calculation be done. However the formula gives value #ERROR for lines where [% Work Complete] > 0 and [Cost] = 0. But this should be caught by the 4th statement in the formula. So it seems that the division part of the last statement in the formula is evaluated even though the condition is not true.

    How is it then possible to write a formula that always returns a numeric value?

    Maybe we are misunderstanding here, but this should be a quite straight-forward thing.

    Thanks, Stig

     

All Replies

  • Monday, April 05, 2010 2:02 PM
     
     Answered
    Sign In to Vote
    0
    Hello Stig, The Switch statement evaulates each and every expression before returning the result. If one of those expressions results in an error, the function returns #ERROR. In order for this to behave the way you expect, change your expressions so they are always valid: [% Work Complete] > 0 And [Actual Cost] > 0 And [Cost] > 0, IIf( [Cost]=0 OR [Actual Cost] = 0, 0, [% Work Complete] / ([Actual Cost]/[Cost]) Gord
    • Marked As Answer by Stig Tuesday, April 06, 2010 8:46 AM
    •  
     
  • Monday, April 05, 2010 3:05 PM
    Moderator
     
     
    Sign In to Vote
    0

    Stig:

    In addtion to Gord's explanation why, try returning a negative (-1) (or some similar technique) rather than zero to avoid the div/0 error condition.

    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG
     
  • Tuesday, April 06, 2010 8:48 AM
     
     
    Sign In to Vote
    0

    Thanks, guys. This does indeed compute correctly.

    My fault for trusting a non-Microsoft site that described the Switch function. If you google 'vba switch' the first site tells you that 'The Switch function is looking for the first expression that evaluates to TRUE.'. That is obviously not completely correct.

    Thanks, Stig