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:

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.