Unexpected Results with FLOOR function in the Formula tool

Question

I have come across a few examples where output from using the FLOOR function is giving unexpected results.

The general formula I am using is FLOOR(([number]/100*100)).  In this case, if a number is an integer, i would expect the output to be that same integer since we are dividing by a 100 and then multiplying by a number.  However, for specific numbers like 29, 57, and 58 the output is showing as the number - 1 (i,e. FLOOR((29/100*100)) returns a 28).

The attached workflow does this evaluation for all integers through 1000 and shows which one don't match.

Is there something I am missing with the expected calculation?

Incorrect Floor Result.yxmd

CharlieS · Accepted Answer

Hi @kkg

The formula tool will convert intermediary calculation values to Double types, which are subject to floating point error in some cases. Notice that this works as expected if you modify the numbers to larger values (integers) during the calculation (multiple then divide) rather than divide then multiply as you have, which will convert to Double/decimals in the intermediary step.

So for very small numbers, it always safest to multiply those values to integers before calculation steps, or convert large numbers to strings as explained in this post:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Big-numbers-and-Alteryx/ta-p/105952