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

Options
✭✭

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.

Thanks!

Michael

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

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!

Best,

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭✭✭✭✭
Options

@Mick Tim Inside the ifferror, include an if statement to check if the sum = 0, and if the sum= 0, then display "No accidents", otherwise display the sum. It would look like this:

=iferrror(if(SUM([Jul Accidents]@row * 200000 / July@row)= 0, SUM([Jul Accidents]@row * 200000 / July@row), "No Accidents"), "REPLACE WITH YOUR MESSAGE IF ERROR")

I didn't run this in Smartsheet, so can't guarantee I didn't miss a comma or parenthesis, but I think you get the idea!

• ✭✭
Options

Thanks for the response Darren. The formula is just about there but not returning the expected values. Still returning a "0". It may be too early here in the UK for my brain but I cannot seem to figure out what's not right. Looking at the formula guide/prompt and have rearranged it, added parentheses to the end but it just focuses on the SUM and produces a 0. I will keep working on it, thanks for your help!

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

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!

Best,

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭
Options

This worked perfectly thank you so much!

• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | 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!