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
Best 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!
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.
Answers
-
@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!
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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!
-
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!
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.
-
This worked perfectly thank you so much!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!