I have a formula in Smartsheet that calculates Accuracy by using the following formula: Estimated amount-actual amount/actual amount. Until the “actual amount” is filled in (sometimes weeks or months later) the formula returns a “#DIVIDE BY ZERO” error. The error is fine as far as the calculations go on my pivot tables. However, when that column comes over from Smartsheet into Excel, it transfers as TEXT and therefore impacts my pivot tables for my Accuracy metrics. I have to manually input the formula in Excel before I update my pivot tables so I can update the metrics. Is there a fix for this?
Any guidance offered is greatly appreciated.