Nested =IF() Issue - Logical statements are being interpreted wrong(?)

I'm having an issue with the Logical Statements when using nested =IF() statements. Essentially, it's not following the Logical Statements correctly.

Here is an example of a formula that is failing:

=IF([Surplus]@row = [Deficit]@row, "No Variance", IF([Surplus]@row > [Deficit]@row, (([Surplus]@row - [Deficit]@row) +" over"), (([Deficit]@row - [Surplus]@row) +" under")))

Essentially, this formula looks at two cells that will only ever display a positive number or 0. If the numbers are equal to each other, it should display "No Variance". If they are not equal, it subtracts the smaller from the larger and then concatenates the string " over" or " under" depending on which was larger.

Failure Example 1:

Sometimes, when both [Surplus]@row and [Deficit]@row are 0, it skips the first Logical Statement and results in "0 over". In other rows where both are 0, it properly shows "No Variance".

So, sometimes it knows that 0 = 0. Sometimes it thinks that 0 > 0, or that 0 < 0.

Failure Example 2:

Sometimes, when [Surplus]@row > [Deficit]@row, it just skips right past that second Logical Statement and goes to the third. Example input/output:

[Surplus]@row = 10

[Deficit]@row = 5

Results: "-5 under"

It doesn't think 10 is greater than 5, which is confusing. Except sometimes it does. There is no rhyme or reason to when it does and doesn't work, its completely random.

I've tested all sorts of changes; it always does this for those cells. Certain cells/rows just can't use Logical Statements correctly. I have many different cells/columns where these formulas (or very similar ones) are used, and I'd say 75% of the time it picks the right logical statement, but the rest of the time it's wrong.

There are no error messages. The input numbers are not strings. The input numbers come from various different formulas. It does not appear to be an issue with the formula or the input. Testing the same formulas and input in different cells gives mixed results.

WHAT IS GOING ON? Am I going crazy? Have I missed something obvious? Please help!

Find more posts tagged with

Formulas

Support

Accepted answers

All comments

Gillian C

Hi @Austin C

I've recreated your formula in my own sheet, and I've written the formula the way I would order it and it has made no difference which is good, however, I can't recreate your errors as the formula works for the manual inputs in my sheet.

You said that the input numbers come from various different formulas, or the rows that have the errors in them are the inputs defined by a particular formula?

Austin C

https://community.smartsheet.com/discussion/comment/447887#Comment_447887

@Gillian C

Yes, the inputs are defined by =SUMIFS() formulas that use cross-sheet references.

Example:

=SUMIFS({OtherSheet - Under}, {OtherSheet - Under}, >0, {OtherSheet - Category}, [Category]@row, {OtherSheet - Year}, [Year]@row, {OtherSheet - Checkbox}, 1)

It sums everything in the {OtherSheet - Under} column so long as the @row value for the Category and Year columns match those in the equivalent OtherSheet column, and also the Checkbox column from Othersheet must be checked. This formula is working fine.

So, let's say I have 6 different columns defining the inputs: Deficit1, Deficit2, Deficit3, Surplus1, Surplus2, Surplus3. These are all =SUMIFS() functions that are identical or nearly identical to what I wrote above. They all result in either 0 or a positive number, no errors.

There are also 2 label columns, [Category] and [Year], which are just used to determine which values the =SUMIFS() sum from the other sheet.

There are then 3 columns that use the formula I wrote in the question: Variance1 for Deficit1/Surplus1, Variance2 for Deficit2/Surplus2, and Variance3 for Deficit3/Surplus3.

There are 35 rows total. The Variance1/2/3 are failing randomly; there is no pattern. I have similar variations of this setup using different values in the label columns, with everything else nearly identical, and the Variance columns will randomly fail there as well. In the same row where Variance1 fails, Variance 2 will work fine, but on the next row Variance 2 might fail, etc., (no discernable pattern to the failures)

Gillian C

@Austin C

Wow, I can totally understand your frustration with this. Not that it will be any consolation at the minute but it sounds like you are checking all the right things. It's difficult to help when I can't recreate the errors is there a way you can share a sanitised version of your data? e.g. save your sheets (the working ones and the cross reference) as something else taking out any company sensitive info and I could have a look at that to see if I can spot anything?

Austin C

https://community.smartsheet.com/discussion/comment/447921#Comment_447921

@Gillian C

OK, I found a way to make this even more confusing!

Just for context: all of the numbers in the =SUMIFS() ranges are whole numbers, there should be no decimals. When I edit the formatting to show the max number of decimal places, it's still a whole number.

So, 0 = 0 should always be true, right?

For the 0 = 0 failing formulas, if I wrap the =SUMIFS() equations for the two input columns in a ROUND(), it fixes the issue and now recognizes that 0 = 0.

However, for the formulas where it thinks something like 120 < 50, the ROUND() does nothing (which I expected, as no amount of decimal rounding will make 50 greater than 120)

I'll try and get a cleaned version of the sheets to share, might take me longer than I can do today. There are a few layers I left out to simplify it, here is the actual workflow:

Sheet 1 - Form-Collected Data (many entries per Date, each with a Category, Sub-Category, etc.,) →

Sheet 1.5 - Set of Standard Values based on Categories/etc., to use as reference, no formulas →

Sheet 2 - Aggregated Data & Variances (Sheet 1 data summed by Date and compared to Sheet1.5 Standards to get Variances) →

Sheet 3 - Sliced & Summed Variances (Sheet2 Variance Data, organized & summed by Category/Sub-Category/etc., and various timeframes like YTD/Quarter/Month/Week/etc.,)

The issue is occurring on Sheet 3. I've been checking everything back and forth, the numbers/math is all straightforward so it's pretty much leaving me with a "0 does not equal 0".

Anyways, thanks for giving it a shot, I appreciate the extra set of eyes! Hoping I get more time tomorrow to add to this thread, ideally with a solution I've found that gives more context than just using Round().

NicoLHC

@Gillian C is the issue resolved

Gillian C

Hi @NicoLHC

I'm not sure I've not heard back from @Austen C

NicoLHC

@Austin C was the issue resolved?

Paul Newcome

Double check the source data. Sometimes there could be decimals stored but not displayed.

It could look like

0 and 0

But it could be

0.01 and 0.00

The easiest way to check this would be to expand the decimals in your target sheet (the one using the SUMIFS) to see if there is anything there in the rows that aren't following the expected logic. From there, you have a number of options for resolution that depend on your exact needs.

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.