ROUND Function Not Always Rounding

Occasional Contributor

ROUND Function Not Always Rounding

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

 

 

Message 1 of 11
10 REPLIES 10
Certified Master Anaplanner

Re: ROUND Function Not Always Rounding

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

Message 2 of 11
Super Contributor

Re: ROUND Function Not Always Rounding

Hi I had similar issues too

 

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

Message 3 of 11
Occasional Contributor

Re: ROUND Function Not Always Rounding

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.

 

Message 4 of 11
Super Contributor

Re: ROUND Function Not Always Rounding

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

 

Regular Contributor

Re: ROUND Function Not Always Rounding

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

Message 6 of 11
Occasional Contributor

Re: ROUND Function Not Always Rounding

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

Message 7 of 11
Highlighted
New Contributor

Re: ROUND Function Not Always Rounding

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

Message 8 of 11
Certified Master Anaplanner

Re: ROUND Function Not Always Rounding

Perfect solution.

 

Thanks,

TEXT(ROUND(2.509 * 100, 0) / 100)
Message 9 of 11
Occasional Contributor

Re: ROUND Function Not Always Rounding

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.

Message 10 of 11