How to overrule a 0 multiplication formula returning 0 as an answer?

Hi all, haven't been able to find anything on this by searching so I'm posting.

I'm hoping it is a simple fix as the formula seems very simple!

I am attempting to calculate accident frequency rates at my company. I fully understand that anything multiplied by zero returns zero. However, if there have been no accidents I would like to return one of two things. The total hours worked that month (meaning a full month without accidents) or a statement like the IFERROR function would return such as "No Accidents".

=IFERROR(SUM([Jul Accidents]@row * 200000 / July@row), "No accidents")

Jul Accidents = 0 but this obviously gets calculated and returns...0. If there is no data inputted then the error phrase is populated - as expected.

How do I get the formula to reference the hours worked in July and return that value? I'd rather not have to manually input if it can be formulated.



Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Mick Tim

    I hope you're well and safe!

    To add to Darren's excellent advice/answer.

    Try something like this.

    =IF(OR([Jul Accidents]@row = 0, [Jul Accidents]@row = ""), "No Accidents", IF([Jul Accidents]@row = 0, July@row, SUM([Jul Accidents]@row * 200000 / July@row)))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!