-$0.00 glitch in Numbers?

[spaceballl]

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!

 

[IJ Reilly]

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.

 

[spaceballl]

Yup nothing but good ol fashion adding and subtracting...

 

[IJ Reilly]

Out of curiosity, what happens when you removed the decimal formatting?

 

[spaceballl]

Out of curiosity, what happens when you removed the decimal formatting?

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...

 

[Eraserhead]

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:

=ROUND(SUM(whatever you're summing),2)

 

[spaceballl]

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!

 

[Eraserhead]

^^ 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.

 

[spaceballl]

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

 

[Eraserhead]

Right I understand how computers do math

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.