Ok - so I’m certainly no mathematician as anyone who knows me can attest - but even I can tell that 196 + 2.91 - 196 - 2.91 equals zero.
The negative numbers cancel out the positive, and you’re left with zero.
Unless you ask Excel, that is.
I use Excel quite a bit, but this is the first time I’ve come across this particular problem. According to Excel, the result is -3.55271E-15.
There’s nothing hiding in those numbers (type them in yourself and test). Excel just can’t hack it. If you change the order of the numbers it sometimes gets it right.
You can force it to change the formatting and that will hide the odd result and appear as zero - but it’s only hiding the issue.
So what’s going on?
I’ve tried multiple computers and different versions of Excel and had the same result.
I’m still not really sure what’s going on here - but from what I can dig up, it’s got to do with the fact that Excel uses floating point numbers, and they aren’t precise enough. So it actually is just getting it wrong.
This is an issue with floating point numbers, not Excel itself. I used floats in SQL and had the same result.
This is the first time I’ve come across this sort of issue by accident. Took me ages tearing my hair out trying to work out why my data wasn’t reconciling. Turns out that it was - if you discount the issue.
I'm slightly less embarrassed by my lack of Mathematical ability when I can get this right and a computer can't.
Trust me - regardless of what Excel says, the answer is zero.