Data not clearing out

PrevContributor

I have an issue with a decimal showing up in a formula that's supposed to generate 0. Line Item A: 48250.4 Line Item B: 65810.4 Line Item ClearAB: A+B-45250.4-65810.4 yields 0 Line Item ClearABAlt: A+B-(45250.4+65810.4) yields 7.275957614183426E-12 All items have same numeric format.

peter_mcanena

Hi Jay,

The problem is that there are tiny rounding errors in each step of your calculation. Sometimes these rounding errors cancel each other out, sometimes they add up, and that can depend on unpredictable factors such as the nesting of brackets in your formula. You may have observed the same phenomenon in Excel. In your case the error is 7.276E-12, which is then that one billionth of a penny/cent.

The solution is to add rounding to your formulae, to give you the accuracy you require - say two decimal places. So instead of

ClearAB: A + B - 45250.4 - 65810.4
ClearABAlt: A + B - (45250.4 + 65810.4)

you'd have

ClearAB: ROUND(A + B - 45250.4 - 65810.4, 2)
ClearABAlt: ROUND(A + B - (45250.4 + 65810.4), 2)

This will give you the expected zeroes.

PrevContributor

Thanks for the response Peter.
However, the point I wanted to make (but failed) in the first post is that the data was not a computed number; it was a hard coded number.
You can try out yourself with 3 line items in a module.
A: type in 48250.4
B: type in 65810.4
C: use the formula bar: A + B - (48250.4 + 65810.4)

PrevContributor

Hi Jay Lee,

I tried and got the same error which you are getting. But when I changed the format of the line items A,B and C to Decimal then I got 0. You can try it out. It will solve your issue

peter_mcanena

Yes, I understood that it was hardcoded. But the answer is the same: Anaplan will sometimes produce small rounding errors, which you can either hide by using the "decimal places" format that Gaurav suggests or get rid of completely by using the ROUND() function as I've suggested. The same applies to other technologies, not just Anaplan.

This is particularly important where you have a check later that says IF x = 0 THEN ... because if x contains a small value the check will fail, even if you've hidden it with your "decimal places" formatting.

peter_mcanena

This is an alternative, but you should be careful because it doesn't actually change the result; it just hides it. If you add another line iten checking if the result is = 0 then you'll see that it still contains a small rounding error. The only complete solution is to use ROUND.