chrisd
(Chris Doyle)
February 5, 2021, 4:49am
1
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)
February 5, 2021, 1:33pm
2
@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)
February 5, 2021, 1:58pm
3
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)
February 5, 2021, 6:31pm
4
Interesting to see the the DIVIDE function is handled differently than the divide operation. Good to know.
system
(system)
Closed
May 5, 2021, 6:41pm
5
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.