Calculation doesnt show exact result

[el_ja]

Hello
This is something that has happened to me some times but never understood why.
I'm doing a simple calculation which I can easily do in my head, and the answer should be 0 but excel returns a negative 0.
Upon further inspection the 14th decimal position has a value, but it should be 0.
Anyone knows how to correct this? (tried it in 2 computers with the same result)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]7212.50[/TD]
[TD]4141.25[/TD]
[TD]2907.01[/TD]
[TD]164.24[/TD]
[TD]+A1-A2-A3-A4[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Your computer uses a binary floating point representation of numbers that can't store most decimals exactly, just as finite decimals can't store 1/3 exactly. Use ROUND().

 

[MARK858]

As shg stated wrap the formula in a round function.
The link below explains it if you really want to understand it.

https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

 

[el_ja]

I can live with the negative 0, just wanted to know if perhaps there was a setting to correct it.
Thanks a lot to both of you

 

[joeu2004]

I can live with the negative 0, just wanted to know if perhaps there was a setting to correct it.

First, it is not "negative zero". It only appears that way due to your cell formatting choice. It is important to understand that A5=0 is FALSE (!).

Second, there is a setting that might "correct" the calculation.

But I do not recommend it. If you choose to experiment, be sure to save a copy of your Excel file first. Just enabling the option can change some constants irreversibly, potentially changing the behavior of your entire workbook unintentionally.

The option is called "Precision as displayed" (PAD). On my version of Excel, I would click File > Options > Advanced, and scroll down to the lower half of the list.

Before you set the option (and after you save a copy of your file), you might want to review the format of every cell. Setting PAD is useful only when you use numeric options with a limited number of decimal places.

Note that PAD will only "correct" such calculation anomalies in the final cell value. For example, the formula =A1-A2-A3-A4 will result in exactly zero if it is formatted with 2 decimal places. But the expression A1-A2-A3-A4=0 still returns FALSE. That might cause inconsistencies in the behavior of your workbook.

Instead of setting PAD, I prefer and recommend that you explicitly round any calculation that you expect to be accurate to some number of decimal places. And round to that number of decimal places, not some arbitrary number (some suggest 10). The latter will not always work. Of course, you must make a prudent choice about when to round a calculation. That is why I say: "when you expect [it] to be accurate".

 

[MARK858]

The link I provided does explain the PAD option but I totally agree with joeu2004 it is not an option I would use.

 

[el_ja]

That's a very thorough and nice explanation joeu2004.
I Definetly don't need to enable PAD, I'm working with money so a 0,00 result is more than enough (even if it looks red due to formatting)
I was mostly curious and you guys have answered my question completely