How to write a formula that avoids dividing by zero
I am trying to write a formula to get an average for total absence, no shows and regrets; however some cells are zero which returns an error or unparseable message.
Below is the formula I've written that does not work. The problem is with dividing by the [Total Absent] because sometimes it is zero. If it is zero, I want it to default to 1.
=COUNTIF([09/25/23]@row:[06/03/19]@row, ="absent") / [Total Absent]@row IF([Total Absent]@roW=0, 1).
Will appreciate help with this.
Thanks,
Answers

@Lucy B Wrap the formula in the IFERROR function and set the default error value to 1:
=IFERROR(COUNTIF([09/25/23]@row:[06/03/19]@row, ="absent") / [Total Absent]@row IF([Total Absent]@roW=0, 1), 1)
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions

In addition to the IFERROR, you will also need to remove the IF portion.
=IFERROR(COUNTIF([09/25/23]@row:[06/03/19]@row, ="absent") / [Total Absent]@row, 1)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 217 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!