Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

After basic math, Excel is coming up with hig sigfig negative results out of nowhere.

JamesKeenanJamesKeenan Registered User regular
cNVoi.png

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.

Posts

  • ThundyrkatzThundyrkatz Registered User regular
    How Many decimal places is that cell formatted for? 40?

  • JamesKeenanJamesKeenan Registered User regular
    I only expanded it to see why the sum was (0.00) instead of -

    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?

  • Casually HardcoreCasually Hardcore Once an Asshole. Trying to be better. Registered User regular
    why not use the round macro?

  • JamesKeenanJamesKeenan Registered User regular
    why not use the round macro?

    Because this is STRANGE.

  • ThrackThrack Registered User regular
    I came across something similar once, it's a problem with how floating point numbers are processed by binary systems.
    http://support.microsoft.com/kb/214118/en-us

    image-1_zpsdcb9eee1.png
  • SavantSavant Registered User regular
    There may be another bug in excel, I'm guessing maybe some sort of display thing since I'm guessing the .74000... too many zeroes thing isn't an exactly correct representation of the underlying floating point number.

    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.

    Essee
Sign In or Register to comment.