Identical text strings return false when compared.

[Msgjazz]

So I have a issue where the comparisons of two cell are the same but I get a FALSE statement instead of a TRUE statement. In the first cell it's a sum of a range. The second cell is a sumif results. I did a TRIM on both cells and one count is 9 and the other is 16. But I have other similar items and they are all the same. They all return TRUE except one that returns FALSE.

The Formula for Team 14, in cell J182 is "=SUM(J160:J168)". The formula in N182 is "=SUMIF($A:$A,14,$D:$D)". The same is true for all the other cell except that they correspond to the individual teams. For Team 4, where the error is, the formula is "=SUM(J47:J61)" in cell J174, and "=SUMIF($A:$A,4,$D:$D)" in cell N174. But Team 14 is TRUE, but Team 4 is FALSE. And as you can see in Columns Q and R, the ones returning FALSE has a longer decimal value, even though the format was set the same in Columns J and N.

Any help would be greatly appreciated. TIA

 

[dreid1011]

I would wager it has to do with a known floating point error in Excel. You may need to round the results manually. For reference: Floating Point Errors in Excel

Also, your title is misleading as you are not comparing text strings, but rather numerical values.

 

[bassplr19]

Use round(), roundup(), or rounddown(), then compare or compare with an epsilon, i.e., =IF(ABS(J164-N164)<0.01,TRUE,FALSE)

 

[Joe4]

Use round(), roundup(), or rounddown(), then compare or compare with an epsilon, i.e., =IF(ABS(J164-N164)<0.01,TRUE,FALSE)

I think you mean ABSOLUTE, not EPSILON!

 

[bassplr19]

I think you mean ABSOLUTE, not EPSILON!

Don't know if I'm using it exactly right, but per wikipedia:

• in numerical analysis and statistics it is used as the error term

 

[Msgjazz]

Thank you bassplr19. That worked