# Anaplan calculates 0.000000000000000111022 value but it should be zero.

Options

Can someone help me or point me to if there are already discussion around this topic?

In Anaplan, I am doing a moving sum prior 11 months and it looks like it returns a 0. As seen in the screenshot for Sep 23. But when I copy and paste the value into Excel, it is showing 0.000000000000000111022.

Copied SEPT 23 into in Excel show a nonzero value:

What is causing this and how can Fix this issue?

I would like to understand why first.

I think I can simply fix it by adding to the formula, highlighted in yellow in screenshot below, but I would like to understand why it is not calculating correctly a zero, when it looks like a zero, "-".

• Options

Have you tried using the Round function?

• Options

@n0200309 - Just to provide an explanation on why, I believe this is a data issue. I would like you to clear all values from the POL_CNT line item by making the formula =0

Once all values are zeroed out, then you can re-input the valid values, like 1 for Mar and Apr and -1 for Jul and Sep - and hopefully, you will then see a 0 in both Anaplan and Excel. Let me know if this works!

• Options

`rob_marshall`, the value should not be rounded though. Decimals are allowed.

`gaurav_sharma-bng`, I zeroed out the POL_CNT line item by making the formula 0 and putting the formula back in. I also zero'd out the POL_CNT and reinput the values and SEP 23 is still showing "-" in Anaplan but when copied and paste into Excel, it is showing 0.000000000000000111022.

Here is Anaplan:

Here is copy and paste:

Oct 22

Nov 22

Dec 22

Jan 23

Feb 23

Mar 23

Apr 23

May 23

Jun 23

Jul 23

Aug 23

Sep 23

POL_CNT

0.0

0.0

0.0

-0.41667

0.0

1.0

1.0

0.0

0.0

-1.0

0.0

-0.58333

POL_CNT MOVINGSUM

7.41667

7.41667

4.41667

4.5

3.5

4.0

3.41667

-0.5833300000000001

-1.25

-1.4166699999999999

-0.9166699999999999

1.1102230246251565E-16

As mentioned, I can add "IF MOVINGSUM(POL_CNT, -11, 0) < 0.01 AND MOVINGSUM(POL_CNT, -11, 0) > -0.01 THEN 0" but I just don't understand why Anaplan is not calculating it correctly.

• Options

@rob_marshall @gaurav_sharma-bng Tagging you for visibility - the tag in the comment above didn't work (the poster's reply to each of you).

Thanks!

Ginger

Community Manager

• Options

In the blueprint, how many decimals are defined?

• edited October 2023
Options

0 Decimal place.

But even if I changed the decimal places, it still shows as "-" in Anaplan and copy and paste still shows the same:

Oct 22

Nov 22

Dec 22

Jan 23

Feb 23

Mar 23

Apr 23

May 23

Jun 23

Jul 23

Aug 23

Sep 23

Oct 23

Nov 23

Dec 23

POL_CNT

0.0

0.0

0.0

-0.41667

0.0

1.0

1.0

0.0

0.0

-1.0

0.0

-0.58333

0.0

0.0

0.0

POL_CNT MOVINGSUM

7.41667

7.41667

4.41667

4.5

3.5

4.0

3.41667

-0.5833300000000001

-1.25

-1.4166699999999999

-0.9166699999999999

1.1102230246251565E-16

0.0

0.0

0.0

• Options

Because it is not truncating the underlying data, it is a rendering mechanism to show, in your case, 0 decimal points which is the hyphen. This is why I mentioned to use the round function.