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.

Thanks!

Michael

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

functionality

Accepted answers

Andrée Starå

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!

All comments

Darren Mullen

@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

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å

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

Mick Tim

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

This worked perfectly thank you so much!

Andrée Starå

@Mick Tim

Excellent!

Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.