Forum Discussion
cassandraann1
Jan 26, 2019Copper Contributor
Inconsistent Formatting in Cells with Formulas that Equal Zero
I have a spreadsheet with three columns of numbers. Then there are two additional columns that contain simple subtraction formulas. The solution of all the formulas is zero, yet the cells do not display zero consistently. I have checked that the format settings are consistent across the entire spreadsheet, and have double checked by painting the format settings down the entire spreadsheet. But still, the first half of the rows display "zero" as a dash and the second half of the rows display "zero" as 0.00 and (0.00). All of the numbers have only two digits after the decimal point. I don't know what else to attempt to fix this, but it is driving me crazy.
Inconsistent formatting of zero
I have a spreadsheet with three columns of numbers. Then there are two additional columns that contain formulas. The solution of all the formulas is zero, yet the cells do not display zero consistently. I have checked that the formatting settings are consistent across the entire spreadsheet, and have double checked by painting the format settings down the entire spreadsheet. But still, the first half of the rows display "zero" as a dash and the second half of the rows display "zero" as 0.00 and (0.00). All of the numbers have only two digits after the decimal point.
- It's possible that it's a decimal number that's more than 2dp
so you could wrap your formula in = ROUND( yourformula, 2)- cassandraann1Copper Contributor
Wyn - thank you for the suggestion. It does fix the issue with the inconsistent formatting.
However, it bothers me to not understand why this is happening. I have checked all the numbers in the Formula Bar and none have more than 2 decimal places. Formatting and formulas have all be copied to ensure they are consistent.
- Not 100% sure but it could be related to this
https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/