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

Mick Tim · 09/22/22

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

Andrée Starå · 09/23/22

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!

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!

Darren Mullen · 09/22/22

@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!

Mick Tim · 09/23/22

https://community.smartsheet.com/discussion/comment/344470#Comment_344470

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!

Andrée Starå · 09/23/22

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!

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!

Mick Tim · 09/23/22

https://community.smartsheet.com/discussion/comment/344527#Comment_344527

This worked perfectly thank you so much!

Andrée Starå · 09/23/22

@Mick Tim

Excellent!

Happy to help!

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

Best Answer

Answers

Help Article Resources

Categories