calculation not working accurately on numeric fields

Question

I have two numeric fields and I am using a simple if condition to compare them.

IF [Total] - [Remaining] = 0 THEN 'Equal' ELSE 'Not equal' ENDIF

If you look at row 1 in Screenshot 1, values are equal however the comparison column says not equal. Screenshot 2 shows field type and calculation behind it. Remaining column was initially a V_String column that was changed to numeric using ToNumber formula, later on sum was created using Summarized tool.

Re: Total column, Total PO Value was a Fixed Decimal that was converted to numeric and rounded to 2 decimal places, as shown in screenshot 2.

screenshot 2.PNG

screenshot 1.PNG

ayush_mishra · Answer

Thanks Guys. I tried both solutions and they both worked. I had to change the data type to Fixed Decimal for Total column. And 2nd solution worked with the suggested changes in IF condition.

Hayden_Arnold · Answer

I suspect Prometheus is right. As an alternative approach, you can set a tolerance in your formula. See screenshot for an example - here, I'm saying if the absolute value of the difference between my numbers is < 0.0001, then I consider them equal. Anything outside of that tolerance limit is considered not equal.

IF ABS([Num1]-[Num2]) < 0.0001 THEN "EQUAL" ELSE "NOT EQUAL" ENDIF

Tolerance Example.png

Prometheus · Answer

@ayush_mishra Sometimes there are numbers to the right of the decimal that we don't see. If you round the two numbers the same before they get to the Formula tool, you may be able to get the correct answers. I would also recommend that you change the data type to Fixed Decimal and specify how big you want your number to get.