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,

Find more posts tagged with

Accepted answers

All comments

Darren Mullen

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

Paul Newcome

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)

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.