As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
After basic math, Excel is coming up with hig sigfig negative results out of nowhere.
JamesKeenan
Registered User regular
Registered User regular
That's what it looks like. All cells are formatted for "Accounting". The numbers in yellow are manual, the numbers in white are formulas, but the formulas are shown, and it's just basic subtraction.
What the hell is happening? I'm looking for "excel subtracting wrong" "excel giving wrong sum" but I haven't yet found the problem like mine. I don't think I've done anything weird, so I don't imagine this is a rare issue.
0
Posts
It's normally only two decimal places. Thing is, that very bottom amount... It shouldn't exist at all. That very bottom number is subtracting the yellow 229,121.74 from the white 229,121.74.
And you can see those two numbers expanded out.
So where the hell is 116415321826935 coming from?
Because this is STRANGE.
http://support.microsoft.com/kb/214118/en-us
But like that link mentioned before, this sort of issue can pop up due to how floating point numbers are stored in computers. Think about scientific notation, where you have a number as a decimal multiplied by 10 to some power. Floating point is sort of like that, but where the numbers are in binary instead of base 10. So like how 74/100=.74 has a terminating decimal representation but 1/3=.333333.... does not, you can only approximate many terminating decimals with repeating digits in binary.
For example, Google's calculator gives .74 = 0f3FE7AE147AE147AE in floating point hexadecimal, so you can see the repeating digits there. So when you calculate out .74 in one place you may be having a different underlying expanded binary number that is somewhat close to .74 than when you enter .74 into the spreadsheet manually.
Also somewhat as an aside, from a numerical analysis standpoint, subtracting two numbers that are very close together is like the gigantic red flag for where precision and significant figures problems appear. You can take a university level numerical analysis course if you want to really understand why these sorts of issues pop up, but it is a good idea to keep in mind that these sorts of numerical errors, especially when one step is subtraction of numbers close to equal to each other, will show up occasionally and in some situations can have a very important impact if you don't watch yourself.