EXACT formula returns FALSE for identical numbers

[SashSegal]

I recently had to compare numbers in two columns in two different worksheets (located in the same Excel file). I used the EXACT function to determine whether the numbers were the same or not.

There were a number of instances where the EXCAT formula returned a FALSE even if the numbers were identical. I set the cells to a general format, so that all the decimals would be displayed and the numbers were ablsolutely identical, to the last decimal.

Does anyone have an explanation to why this happens?

 

[jeffreybrown]

Welcome to the Forum SashSegal,

The first thing jumps to my mind would be to test the length of both of those cells. =Len(A1). See if they return the same number.

 

[Richard Schollar]

Hi

You can't perform this kind of comparison using floating point numbers without rounding the values first due to the limited precision within Excel and the way computers store values in Binary. Hence you should round both numbers first:

=ROUND(A1,4)=ROUND(B1,4)

Using EXACT is unnecessary - you should only use EXACT for string comparisons where case sensitivity is important.

 

[jeffreybrown]

Thanks Richard...I have a lot to still learn.