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

@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

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().