decimal places more than 15 do not total right:

elaine.novel · January 2021

Dear Team,

I would like to seek for your enlightenment on this area. I tried to copied several times an amount (percentage in real number format) with a decimal places of 19-20. it copied correctly in anaplan and i selected the number format of significal number to 15 places. However, it does not total correctly. it would total 0.99999999999999900000 . So what I did is to export it to excel and compare. My comparison would show differences in 19th or 20th decimal places but i compared the figure, they are exactly the same. the LEN of both are the same: 20. The result of comparing the two is TRUE across. Please see screenshot for your reference.

Hoping for your enlightenment on this.

Should you prefer to do it in anaplan, these are the breakdown of the numbers:

0.000151169934355968000

0.001479197807673140000

0.001731731161170940000

0.003416144143810420000

0.028347710204074500000

0.036188032765282300000

0.033112371714541600000

0.031574541189171200000

0.068877233700210600000

0.041857551369453200000

0.028347710204074500000

0.031574541189171200000

0.006773364368892000000

0.004554858858413880000

Thanks again,

Elaine

ben_speight · January 2021

Both Anaplan and Excel work with the IEEE754 binary double-precision floating point number representation internally, which equates to just over 15 significant figures of decimal precision. Differences can arise when a more precise representation is or is not used to hold intermediate results, or if other rounding is performed.

https://help.anaplan.com/ee98e93f-702e-409c-92db-70eaa962e55d-Large-Number-Calculations

https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

JaredDolich · January 2021

@elaine.novel

Good callout. And @ben_speight is correct about the precision.

I worked on a use case where $0.01 (one cent expense) equated to $1 million savings, so decimal point precision really mattered. One thing we did was to multiply the values by 1000 in order to obtain 3 additional decimals. Not ideal, but the only other option we had was to cut/paste 12 decimal places.

I also believe there is an idea on the idea exchange to add additional precision options for numeric fields, similar to most RDBMS solutions. Believe it or not, submitted by @ben_speight!

https://community.anaplan.com/t5/Idea-Exchange/Single-precision-numeric-format-option/idi-p/39666

elaine.novel · January 2021

Thanks to both of you. Appreciated your responses.

It is correct the the decimal places indeed matters. @JaredDolich @ben_speight .. Will look forward to the update on this then.

Thanks again,

Elaine

decimal places more than 15 do not total right:

Answers

Categories