Custom Field with Formula

# Custom Field with Formula

• Thursday, April 01, 2010 1:28 PM

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

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 Tuesday, April 06, 2010 8:46 AM
•
• Monday, April 05, 2010 3:05 PM
Moderator

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

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