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.
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:
[% 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.
Monday, April 05, 2010 2:02 PMHello 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 PMModerator
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.