Data not clearing out

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.

Accepted answers

All comments

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

Capture11_1052.JPG

Capture2_1052.JPG

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.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…