-$0.00 glitch in Numbers?

Discussion in 'Mac Apps and Mac App Store' started by spaceballl, Apr 15, 2008.

  1. spaceballl macrumors 68030

    spaceballl

    Joined:
    Nov 2, 2003
    Location:
    San Francisco, CA
    #1
    Hi all,

    So I'm a super nerd... and I do my monthly budget on Numbers with a lot of conditional formatting. I like my positive numbers to be green, my negatives to be red, and my 0s to be gray. I've stumbled across an issue that I find to be extremely annoying. Maybe there's a fix for it or i'm doing something wrong? I have a monthly take-home of $X.XX. The number is set to be a two decimal number. Then I list all of the outgoing expenses as $-X.XX until we get down to a $0.00 on the bottom. Does anyone have the issue where $0.00 is displayed as $-0.00? It really messes up my conditional formatting! Super annoying! I attached a screenshot to show what I mean. I hope it's something I'm doing wrong and not a Numbers glitch. Thanks!
     

    Attached Files:

  2. IJ Reilly macrumors P6

    IJ Reilly

    Joined:
    Jul 16, 2002
    Location:
    Palookaville
    #2
    If you're only doing addition and subtraction, then maybe it's a bug, but if you're doing any division or multiplication then you might be creating fractions smaller than 0.01. Turn off the decimal formatting on the offending cell and see.
     
  3. spaceballl thread starter macrumors 68030

    spaceballl

    Joined:
    Nov 2, 2003
    Location:
    San Francisco, CA
    #3
    Yup nothing but good ol fashion adding and subtracting...
     
  4. IJ Reilly macrumors P6

    IJ Reilly

    Joined:
    Jul 16, 2002
    Location:
    Palookaville
    #4
    Out of curiosity, what happens when you removed the decimal formatting?
     
  5. spaceballl thread starter macrumors 68030

    spaceballl

    Joined:
    Nov 2, 2003
    Location:
    San Francisco, CA
    #5
    Excellent test! Just did it and it still shows a -$0. I'm making a sample file to make sure it's reproducible and I'll upload it here. Also, unless someone can figure out what my issue is, i'm going to file a bug report with Apple and i'll link to it here.

    Interesting development! So I was going to copy out that table and try and make a sample file with the same issue, that way i'd avoid exposing all of my financial information to Apple. But take a look here... when I copy out the table, it gets rid of all of the sums that depend on the other tables, and instead, stores the values as they are. Look what the value it has for "0" is...
     

    Attached Files:

  6. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #6
    OK, the problem is that you aren't really earning $1000 dollars, but are actually earning $999.99999999999912314 or something, so this is throwing off the value from what it should be, its displaying -$0.00 as its slightly less than one.

    To fix it you need to wrap a round to 2dp around your sums, i.e. do the following:

    Code:
    =ROUND(SUM(whatever you're summing),2)
    
     
  7. spaceballl thread starter macrumors 68030

    spaceballl

    Joined:
    Nov 2, 2003
    Location:
    San Francisco, CA
    #7
    Eraserhead, thanks for the fix - that totally works and at least it no longer hurts my eyes!

    HOWEVER, i mean it's definitely a glitch.

    If I write that I earn $1,000, and then have enough of items that subtract to $0, my final answer should be $0!
     
  8. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #8
    ^^ Its a rounding error caused by the way that computers represent and add/multiply numbers. Basically they don't do it perfectly, so they don't get the answer exactly right each time, however mostly its close enough for real world usage, but in this case it hasn't quite rounded it correctly to zero, so as its slightly below zero it rounds to slightly below zero.

    To make matters even worse you'll get a different result on an Intel Mac from a Power PC Mac, and even a different answer on a Pentium 4 (if they ran Numbers) from a Core Duo.

    However Apple could fix the formatting so this issue wasn't apparent to end users. I've filed a bug on this.
     
  9. spaceballl thread starter macrumors 68030

    spaceballl

    Joined:
    Nov 2, 2003
    Location:
    San Francisco, CA
    #9
    Right I understand how computers do math, but as you were saying, Apple should have this fixed so it's transparent to the end users. In any case, your tip helped and you have already filed a bug with Apple so I can rest in peace :)
     
  10. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #10
    That's cool :). Hopefully it'll help other people reading the thread anyhow. FWIW the bug ID is 5866055 if you're curious to see how the issue is going.
     

Share This Page