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:
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:
Total_Seats__c <> 0,
Seats_Used__c / Total_Seats__c ,
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!