decimal places more than 15 do not total right:

Contributor

decimal places more than 15 do not total right:

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.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.000151169934355968000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001479197807673140000    0.001731731161170940000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.003416144143810420000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.036188032765282300000    0.033112371714541600000    0.031574541189171200000    0.031574541189171200000    0.031574541189171200000    0.031574541189171200000    0.031574541189171200000    0.068877233700210600000    0.041857551369453200000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.028347710204074500000    0.031574541189171200000    0.031574541189171200000    0.031574541189171200000    0.031574541189171200000    0.006773364368892000000    0.004554858858413880000    0.004554858858413880000    0.004554858858413880000    0.004554858858413880000    0.004554858858413880000    0.004554858858413880000    0.004554858858413880000

 

 

Thanks again, 

Elaine

 

3 REPLIES 3
Super Contributor

Re: decimal places more than 15 do not total right:

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-resu...

 

Master Anaplanner/Community Boss

Re: decimal places more than 15 do not total right:

@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

 

 


Jared Dolich
Contributor

Re: decimal places more than 15 do not total right:

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