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?

	0,520000000000000000000000000000
	0,630000000000000000000000000000
	0,510000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	0,520000000000000000000000000000
	1,600000000000000000000000000000
	55,300000000000000000000000000000
	-63,152000000000000000000000000000
SUM:	0,087999999999993900000000000000

<tbody>
</tbody>

 

[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!