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

 

 

Jaakko

Hi,

 

I can confirm that this is happening for me also. Tried multiple formulas and it seemed to happen with parameter of two decimals and when rounding up.

ROUND(2.509, 0, NEAREST) = 3
ROUND(2.509, 0, DOWN) = 2

ROUND(2.509, 0, UP) = 3

ROUND(2.509, 1, NEAREST) = 2.5
ROUND(2.509, 1, DOWN) = 2.5

ROUND(2.509, 1, UP) = 2.6

ROUND(2.509, 2, NEAREST) = 2.5100000000000002
ROUND(2.509, 2, DOWN) = 2.50

ROUND(2.509, 2, UP) = 2.5100000000000002

ROUND(2.509, 3, NEAREST) = 2.509

ROUND(2.509, 3, DOWN) = 2.509

ROUND(2.509, 3, UP)  = 2.509

harish_bk

Hi I had similar issues too

 

I then created another line item in number format and rounded off the same in the format.

davee

Thanks Jaako for confirming the symptom and Harish for the comment.

Harish,

I tried to follow your suggestion but TEXT of that still shows the long value not "2.51".

Can you give more detail?

I found that VALUE(LEFT(TEXT(ROUND(2.509, 2)), 4)) will give the correct rounded value, proven because TEXT of it = "2.51".

It will aslo work if the original value is only zero, 1 or 2 DP.

In the end the above is not a good solution and there seems to be a bug with the ROUND function.

 

harish_bk

Hi Davee

 

You have done the exact thing what I had done in the project.

 

Only thing is that for the new line item for which formula was Value(Text), I did not use the round function. I guess we use round off to limit the number of digits and if the number of digits are 0,1, or 2 it should not be a problem for us right, could you pleae give me more details on your business scenario to assist you further.

 

I went into blue print and then in the format i made the new line item as number format and limited the decimals to 2 as I had the number required for display purpose only.

 

Attaching file for your reference with sample module

 

peter_mcanena

Hi all,

 

This looks like a bug in Anaplan (and it's still happening as of today). However, you can get round it by using a different syntax for the ROUND() function.

 

Instead of rounding to 2 decimal places, multiply the number by 100, then round to 0 DPs, then divide by 100. So the original example becomes

TEXT(ROUND(2.509 * 100, 0) / 100)

which returns "2.51" as expected.

 

Hope this helps. But Anaplan, please fix this bug!

 

Pete

davee

Thanks Pete and all for the comments and fixes.

Pete, I thought yours might not work for TEXT(ROUND(0.02509 * 100, 2) / 100) but it does.

Still I don't know if we can be sure it will always work.

Anaplan have however acknowledged it and it is on their development list.

Regards

Dave

tsullivan_clns

Given that this bug has been open for a year, why is it not on the known issues / workarounds?

eije.wiersema

Perfect solution.

 

Thanks,

TEXT(ROUND(2.509 * 100, 0) / 100)

Prashant22may1

Hello All,

even i'm facing simmilar kind of issue regarding the calculation of decimal values or summary of line items having decimal values. I think there is a bug in Anapaln regarding the calculation of decimal values, that's why round() is also not working properly.

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.