Comparing Numbers in Different Columns

[JeremyN]

I have multiple number columns that I would like to compare that are initially strings.  I am converting these to doubles as they are numbers with decimal places.  Then I used the formula tool and divide one column by 100 to see if it equals another column.  These are not matching every time when I expect them to match.  Should I use different data types or are there certain tricks to ensure that checking for identical numbers works as intended?  I see that float/double can have rounding issues, but used double since I have decimal values.  Also, I'm not sure how to adjust for rounding issues.  Any advice would be great!

[CharlieS]

Could you share some sample data for testing? How the comparison is made will depend on the field structure and the formulas will depend on the values you're working with. 

[JeremyN]

Sure - see attached sample data.  It's a double at this point.

[hbraunius]

Hi @JeremyN,

I tried using the round function for both numbers, and there were still values that did not match, even though the numbers looked identical. I ended up converting these doubles to string, and then back to number, and that seems to have fixed it.

Value 1 conversion calculation:

tonumber(tostring(round([Value1],0.01)/100),0.0001)

For some reason, 0.305 would not round properly if you round the value after you divide by 100.

Value 2 conversion calculation:

tonumber(tostring([Value2]),0.0001)

Thanks,
Hanna

[benakesh · Accepted solution]

@JeremyN ,

After converting the numbers to fixed decimal  5  numbers don't match .

[Sumiya]

This is not working in the formula field. I have tried [Column1]=[Column2], with both are integer values.