Where are the decimals coming from?

[Solari]

I have a problem regarding the SUM-function. It doesn't seem to be able to correctly sum the numbers put in a sheet.

I guess it has to do with some sort of formatting, but I genuinely have tried to format it to the best of my knowledge.

I have put in all the numbers manually using 3 decimals for each cell, still the sum seems to think it has 16 decimals!
All the cells have the same format. How is this possible?

[TABLE="width: 335"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,630000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,510000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0,520000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1,600000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]55,300000000000000000000000000000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]-63,152000000000000000000000000000[/TD]
[/TR]
[TR]
[TD]SUM:[/TD]
[TD="align: right"]0,087999999999993900000000000000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Click on a number that makes up part of the SUM.

What does it display in the formula bar (just above columns D and E)?
If it displays long numbers like 0,5212345760808089079669 then that's what the values are (not 0,52) as displayed in the sheet and explains why you have a long SUM total.

 

[Solari]

Click on a number that makes up part of the SUM.

What does it display in the formula bar (just above columns D and E)?
If it displays long numbers like 0,5212345760808089079669 then that's what the values are (not 0,52) as displayed in the sheet and explains why you have a long SUM total.

Thank you, but that isn't the explanation. All the numbers have been entered by me, manually and I have checked them more than one time, they all are registered as 2 or 3 decimals long.

 

[Repush]

unevitable i'm afraid
it's a result of binary storage of the numbers
example: 0,1 to binary is like 0,0001100110011. . .
NB this is not a completely watertight answer but a bit of a shortcut

 

[Joe4]

See this link for an explanation of the issue, and workarounds:
https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

Using "Precision as Displayed" or the ROUND formula are the most common ways to correct this.

 

[Solari]

unevitable i'm afraid
it's a result of binary storage of the numbers
example: 0,1 to binary is like 0,0001100110011. . .
NB this is not a completely watertight answer but a bit of a shortcut

See this link for an explanation of the issue, and workarounds:
https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

Using "Precision as Displayed" or the ROUND formula are the most common ways to correct this.

Thank you both!