Formula error when date is empty

[chrisd (Chris Doyle)]

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!

[JCorrell (Jim - The Monday Man)]

@chrisd,

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.

[chrisd (Chris Doyle)]

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,
            ""
        )
)

[JCorrell (Jim - The Monday Man)]

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