cell formatting problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2239550%22%20slang%3D%22en-US%22%3Ecell%20formatting%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2239550%22%20slang%3D%22en-US%22%3E%3CP%3EWhy%20does%20the%20cell%20show%20a%20zero%20when%20adjacent%20cells%20show%20a%20dash%3F%20The%20cell%20formatting%20across%20the%20row%20is%20set%20the%20same%3A%3C%2FP%3E%3CP%3E_(*%20%23%2C%23%230_)%3B_(*%20(%23%2C%23%230)%3B_(*%20%22-%22%3F%3F_)%3B_(%40_)%20Custom%20fomatting%3C%2FP%3E%3CP%3EThe%20formula%20is%26nbsp%3B%3DD14%2BD18%2BD23%20in%20one%20cell%20and%20results%20in%20a%20zero%3C%2FP%3E%3CP%3EThe%20formula%20is%20copied%20across%20and%20the%20adjacent%20cells%20are%20dashes.%3C%2FP%3E%3CP%3EWhen%20I%20changed%20the%20row%20to%20all%20general%20number%20formatting%2C%20the%20cells%20with%20a%20zero%20show%3A%3C%2FP%3E%3CP%3E2.91038E-10%26nbsp%3B%20What%20is%20that%3F%3F%3F%20Help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2239550%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Why does the cell show a zero when adjacent cells show a dash? The cell formatting across the row is set the same:

_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_) Custom fomatting

The formula is =D14+D18+D23 in one cell and results in a zero

The formula is copied across and the adjacent cells are dashes.

When I changed the row to all general number formatting, the cells with a zero show:

2.91038E-10  What is that??? Help!

2 Replies

@KayPin 

This means that the formula returned a value that is slightly different from zero because of rounding errors.

If all your numbers have up to (for example) 2 decimal places, change the formula to

 

=ROUND(D14+D18+D23,2)

 

The result will then be exactly 0, and be displayed as a dash.

Thank you! That worked great.