 # Formula error when date is empty

Hi There, I’m having an issue with a formula. What I’m trying to do is calculate the accuracy of time estimates of my team.

Here are the fundamentals I’m working with:

• Accuracy should be the estimate/actual*100
• If an item doesn’t have a start & end date then the Accuracy should be blank
• If an actual value is lower than an estimate, they receive 100% accuracy

Here is the formula I have put together:

``````IF({Estimate}<{Actual},{Estimate}/{Actual}*100,
IF({Actual},100,
""
)
)
``````

The actual column itself is a formula as well:

``````WORKDAYS({End Date},{Start Date})
``````

The formulas are working fine, however I get an error for the accuracy column when the start and end date are not filled in:

Thanks in advance!

Logically, what you have SHOULD work. However, with the way the monday formula evaluator works, ALL portions of the formula must ALWAYS be valid. So, if you change …{Estimate}/{Actual} *100… to …{Estimate}/IF(({Actual}+0)<>0,{Actual},1)*100… you will not get the error.

Thank you! I actually was able to solve this late last night by substituting the arithmatic portions of the equation with their functional counterparts:

``````IF({Estimate}<WORKDAYS({End Date},{Start Date}),
ROUND(MULTIPLY(DIVIDE({Estimate},WORKDAYS({End Date},{Start Date})),100),0),
IF(WORKDAYS({End Date},{Start Date}),100,
""
)
)``````

Interesting to see the the DIVIDE function is handled differently than the divide operation. Good to know.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.