Make your Formulas #Error! proof

I recently ran into an issue where a formula was producing a #Error! result. This was actually fine because I was expecting this since part of the formula wasn’t displaying. However, I wanted to make another formula field where I would use this field as part of my criteria. When you do this, the dependent formula breaks.

Here was my situation:

Original Formula:

Seats_Used__c / Total_Seats__c

This formula produced a simple percentage used formula. However, without the Total_Seats__c field filled in this formula results in #Error!. Again, I was fine with that until I used it in a dependent formula. Anytime a formula with #Error! as the result is referenced in a dependent formula the result will also be #Error! for that formula.

The fix is to make your formulas #Error! proof.

#Error! Proof formula:

IF(
Total_Seats__c <> 0,
Seats_Used__c / Total_Seats__c ,
0
)

This formula takes into account the source fields and selects the denominator and checks if this field is zero or not. Then we can return a result of the actual formula or zero. Either way, a result is returned and will never result in a #Error!

2016-04-25_1149.png