Handling #ERROR in formulas

[Earl Lewis]

Anyone know how to capture an error value in a formula?

Switch([Baseline Cost]=0,0,[Cost]/[Baseline Cost]> 0 AND [Cost]/[Baseline Cost]<=1 OR [Cost] / [Baseline Cost] < 0,1,[Cost]/[Baseline Cost]>1 And [Cost]/[Baseline Cost]<1.2,2,[Cost]/[Baseline Cost]>=1.2,3)

This formula is supposed to provide a budget variance indicator. The problem is that if a project hasn't had a baseline saved the formula results in #ERROR. Any way to trap that and show another value?

Earl

 

[Earl Lewis]

For anyone that wants to know how I fixed this - I wrapped the whole switch formula in an IIF statement, like this:

IIf([Baseline Cost]<>0,Switch([Cost]/[Baseline Cost]>0 And [Cost]/[Baseline Cost]<=1 Or [Cost]/[Baseline Cost]<0,1,[Cost]/[Baseline Cost]>1 And [Cost]/[Baseline Cost]<1.2,2,[Cost]/[Baseline Cost]>=1.2,3,[Baseline Cost]=0,0),0)

Basically the error message I was getting was perfectly correct, after all division by zero is not a legal mathematical operation. So I just needed to test for zero in the divisor before I went to the switch formula.

Earl
Anyone know how to capture an error value in a formula?

Switch([Baseline Cost]=0,0,[Cost]/[Baseline Cost]> 0 AND [Cost]/[Baseline Cost]<=1 OR [Cost] / [Baseline Cost] < 0,1,[Cost]/[Baseline Cost]>1 And [Cost]/[Baseline Cost]<1.2,2,[Cost]/[Baseline Cost]>=1.2,3)

This formula is supposed to provide a budget variance indicator. The problem is that if a project hasn't had a baseline saved the formula results in #ERROR. Any way to trap that and show another value?

Earl