This seems like a numeric error to me, but does anybody have an explanation.

[TomCon]

Its hard to explain, but i try to lay it out here. All the data comes from big tables, so that i cannot really post a mini-section of the worksheet that can be worked with.

I have an array formula which attempts to count the values between two tables that are equal. Its results (SPILL) are in Col 2 of the paste below. As you see, the SUM it returns 26. The array formula is:

=SUM(IF(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1)-OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1)=0,1))

The problem is in row 26 of the result, where the array formula in Col 2 returns FALSE, but in every other way that i can think of to test, it appears that it should return TRUE.

Here is a screen shot of 30 rows and 7 columns, and the explanation of the columns.
Co1 1 - numbers 1 to 30 for the index function to use (column R in the formulas)
Col 2 - An array formula that spills its results to 30 rows. It is the above array formula, without the SUM() function

=IF(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1)-OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1)=0,1)

Col 3 - Index to the values that the array function gets from the table rng_data

=INDEX(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1),$R37,1)

Col 4 - Index to the values that the array function gets from the table rng_wt

=INDEX(OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1),$R37,1)

Col 5 - Difference Col 3- Col 4. as you see, row 26 returns 0. All other rows return 0 if Col 2 is 1. I have increased decimal to 16, and it is 0s all the way

=T37-U37

Col 6 - A logical function that tests whether the values in the two tables are equal. Row 26 returns TRUE. All other row consistent with Col 2.

=INDEX(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1),$R37,1)=INDEX(OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1),$R37,1)

Col 7 - Absolute value of the difference in Col 2 and Col 3 < .00001. Row 26 returns TRUE. All other rows consistent with Col 2.

=ABS(T37-U37)<0.00001

Does anybody have an explanation for what is going on here, why row 26, when equality is tested by the array formula returns FALSE, but when the same data is tested in 3 other ways, TRUE is the result? If it was just a matter of the precision of the number, would not all these other tests also all return FALSE? Should not row 26 be consistent for all of columns 2, 5, 6, 7?

Thank you if you can offer some thoughts on this!

 

[StephenCrump]

Here is the issue: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Column 2 is calculated as the difference between two numbers. In a spare cell, put the formula:

=OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1)-OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1)

And then test for equality with 180.30. You should find that there is a very tiny discrepancy?

 

[TomCon]

Here is the issue: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Column 2 is calculated as the difference between two numbers. In a spare cell, put the formula:

=OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1)-OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1)

And then test for equality with 180.30. You should find that there is a very tiny discrepancy?

Thanks so much. That article was very helpful...but one thing still strikes me as "odd" and possibly inconsistent on the part of Excel itself, not just the way numbers are represented.

I fixed my issue using the ROUND function, as suggested in the article.

=SUM(IF(ROUND(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1),10)-ROUND(OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1),10)=0,1))

and that gives me the "correct" answer for all entries in the 30-length array.

The thing that i find interesting is that it seems that the "inexactness of binary representation of numbers" does not seem to work the same, with the same number, in Excel, depending on the function that is referencing the number. I illustrate the above issue here, much more simply.
1. If i access the cells directly

=data!B1386=wt!C1386

==> Returns TRUE
2. If i use the INDEX function

=INDEX(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1),25,1)=INDEX(OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1),25,1)

==> returns TRUE
(note formula 2 references the same cells as formula 1)
3. <<BUT>>
If i use the original array formula, and let it SPILL, the 25th value in the SPILL array is FALSE!!

=IF(OFFSET(rng_data[[#Headers],[Wt]],TodayRwCal,0,-30,1)-OFFSET(rng_Wt[[#Headers],[WtCalc]],TodayRwCal,0,-30,1)=0,1)

So, that strikes me as odd, it does not seem to be 100% attributable to the way binary numbers are (sometimes inexactly) stored, but also something to do with the specific comparison formula that one uses in Excel.

The moral of the story for me seems to be to use the ROUND function in equality comparisons to 0.

Thanks much for your post!