ROUND Function Not Always Rounding

[davee]

Hello,
I have an issue with the round function.
We are rounding after a complex function and then taking the text value and using that to look in a list.
In a few cases only, nothing is being retrieved from the list because the text value has more characters than it should.
For one example, reducing the issue to the simplest single formula that gives the error, gives:
TEXT(ROUND(2.509, 2))

This returns “2.5100000000000002” instead of the expected "2.51".

I can make the list contain 4 character text values like “2.510” instead of the current "2.51" and then use LEFT(above_value, 4) to get a string to check against the list but that gives the issue that sometimes the original value will have only 2 or 3 characters so there would be no match in the list.
I would need to put eg “2.5, “2.50” and “2.500” in the list to be sure of a match.
Or maybe concatenate “0000” to the text before the LEFT(...., 4) function.

Can anyone verify that they see the same ROUND behaviour for the above example to confirm if this is a bug.
Suggestions for a work around are welcome.

This was raised by email while the community was closed to new posts for the upgrade but no reply yet. Request (#57972).

Regards
Dave

[schulze]

I believe the issue may be because decimal fractions cannot be accurately stored in floating point binary.

This is a good explanation of the issue from a general technical standpoint:

https://docs.python.org/3/tutorial/floatingpoint.html

[stephen.brook]

We're pleased to announce that we've introduced a new mode for the ROUND function in this weekend's release that addresses the problem you're seeing.

You can view the updated Anapedia content for ROUND here: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/ROUND.html

And we have some guidance notes for customers on the use of the new 'EXACT' mode of the ROUND function: 

https://community.anaplan.com/t5/Releases/What-s-new-November-23-2019/ba-p/54370

In essence - nothing in your existing models will change, unless you choose to opt-in to using the new ROUND mode by adding the new EXACT keyword. 

Here's the example from the first post in this thread, showing the new behaviour:

[jwu]

But if you take it back to TEXT, it appears as the scientific format again.  Is there anyway to get it back to text but still formatted to limited decimal places?

[stephen.brook]

Hi - thanks for your feedback. The behaviour you've observed is a result of the way that the TEXT function works, and it depends on the magnitude of the input value, as follows:

• For input values smaller than 1e-3, TEXT will always return a result in scientific format
• For input values between 1e-3 and 1e7, TEXT will return the decimal presentation of the input value
• For input values larger than 1e7, TEXT will return a decimal presentation for integer inputs, and output in scientific format for non-integer input.

So at present, the only way to guarantee that you'll see a decimal representation (rather than the scientific format) for large input values is to ensure that they're rounded to the nearest integer.