Filtering by "does not equal 0" still returns records with 0

I have a sheet consisting of claims records showing dollar amounts. One column is labeled "Balance" where I determine whether we are owed money or if we owe money back to our client. The data is the result of arithmetic between several other columns

I am attempting to count and total the number of records where the Balance = 0. That is represented as "$0.00" in the column because the column is formatted for US $.

In formulas and when I manually filter looking for Balance is equal to 0, I have a handful of records that have $0.00 in that column, but they are not returned in the result. They actually appear in my formulas/filter for "does not equal 0".

I have copied formatting from rows that "work", confirmed the entire Balance column is set to numeric values... I've tried everything I can think of but I can't figure out why these handful of records are not being seen as numeric 0 in this Balance column.

Has anyone else ever encountered this and have a solution?

Find more posts tagged with

Accepted answers

All comments

BullandKhmer

I'm guessing, is it possible your calculation is actually returning a value such as 0.001 and is rounding to 0.00? So technially not =0?

If so, workarounds could including adding a rounding function to your column or filtering for <-0.01 and >0.01

SherriL

Thanks @BullandKhmer . I figured the same, but what I ended up having to do is create a separate column that did the simple calculation, then had no formatting to the column (i.e., no $ formatting). That worked, for some reason, and I just hid the column.

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.