Apple Vision Pro is now available in the U.S.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers Formatting

I am using a Numbers document for budgeting. I don't have any complicated formulas, just a lot of adding and subtracting between cells. However, I'm getting an error now that's bugging me.


I have a cell, E228, that has a simple formula: C228−F228. All 3 cells are formatted the same: Currency with 2 decimal points.


I have another cell set up with conditional formatting: if the value of E228 is ever over/under 0, it colors the cell red indicating my budget is off balanced.


Here's the thing: right now the cell has a value of -$0.00 so the cell set with conditional formatting is red. Odd as I didn't think -0 was a thing. Upon further investigating, the cells value is not actually -$0.00, it's -$0.000000000000227374. So Numbers is being quite literal when it reports the value of the cell is in fact, not 0. What I can't figure out is why the cell is calculating so far past the hundredths value. All values being calculated are currency formatted and there is no multiplication in play that would produce such a value. There are over 1800 cells in my table so I suppose there could be one cell not properly formatted to that has a random number but I can't locate it.


Any thoughts?


I'm using Numbers 6.0 on MacOS 10.14.4. However, I don't think the problem is based on the version of the software as I have seen the -$0.00 value before but ignored it until I started using conditional formatting.

iMac 21.5", macOS 10.14

Posted on Apr 19, 2019 7:29 AM

Reply
Question marked as Best reply

Posted on Apr 19, 2019 7:47 AM

Yes, unless you tell it not to, Numbers can keep more decimal places internally than it displays. That is the same in other spreadsheets; it has to do with how computers store numbers using the binary system. In your "check" cell you could tell Numbers to ignore those extra decimal places by using the ROUND() function, which actually chops off the extra places instead of just suppressing their display in the currency format. You would something like ROUND(C288-F228,2). You could also use the TRUNC() function.


More on ROUND() here.


More on TRUNC() here.


SG



Similar questions

2 replies
Question marked as Best reply

Apr 19, 2019 7:47 AM in response to d_icon

Yes, unless you tell it not to, Numbers can keep more decimal places internally than it displays. That is the same in other spreadsheets; it has to do with how computers store numbers using the binary system. In your "check" cell you could tell Numbers to ignore those extra decimal places by using the ROUND() function, which actually chops off the extra places instead of just suppressing their display in the currency format. You would something like ROUND(C288-F228,2). You could also use the TRUNC() function.


More on ROUND() here.


More on TRUNC() here.


SG



Numbers Formatting

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.