Having trouble comparing two cells

I'm having some issue with comparing two cells in my Smartsheet. I want the box to be checked if the cells match, and if not, the value of the one on the left should turn red. I have 8 sheets for 8 different budget accounts that are all based on the same template. This works for every one of them except for this one.

In the Balance Check column, I have this formula -

=IF([Remaining Balance]@row = [Remaining Balance 2]@row, 1, 0)

The Remaining Balance value is calculated from summing other values on this sheet. The Remaining Balance 2 value is pulled in from another sheet using the "Link from Cell in Other Sheet" function. I've tried increasing the number of digits after the decimal to see if I had some fractional cents in there, but I do not. What else could be going wrong? Again, this exact formula and set up works in several other sheets derived from the same template. Should I use VLOOKUP instead of linking the cell?

Thanks for any recommendations on where I can look for this.

Find more posts tagged with

Formulas

Accepted answers

All comments

KPH

Hi @KOwsley

If the issue isn't the decimals, the next thing to check is whether the number is really a number or if it is text masquerading as a number.

These both look the same:

But the one on the left is a number and the one on the right is text. Using an IF to compare these will result in not equal.

Can you try clicking into the cell that is not calculated by a formula (Remaining Balance 2) and looking for a ' at the start of the string?

You can either remove it, or use a VALUE function to convert this text into a number.

I hope this helps. 🤞

Lucas Rayala

Check to see that the columns are the same data type to start. I would also add an INT or ROUND wrapper to both values in order to ensure you don’t have some weird rounding error. You might also consider adding a VALUE wrapper. If you aren’t worried about being a few cents off, I would do this:

=IF(INT(VALUE([Remaining Balance]@row)) = INT(VALUE([Remaining Balance 2]@row)), 1, 0)

KOwsley

Thanks Lucas. That one works. Still can't figure out what was wrong to begin with (?!?!), but this serves the purpose I needed. Thanks so much for both of your 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.