Accounting Format: shows 0.00 instead of "-"

[Bufo Calvin]

Okay, here's a stumper...kudos to who gets it!

One of my students has a spreadsheet, although it has
happened in other spreadsheets of hers as well. The
format of a formula cell is standard Accounting with 2
decimals. If the result of the formula is 0, it shows
0.00, not "-", as is normal. Other cells on the worksheet
behave normally.

We've tried lots of things: custom formats, copying
formats, deleting the cell, clearing formats, copying it,
you name it. With a custom format (for example, #,###.00,
(#,###.00), "-", "Sales"), it applies the positive format
to the number, not the format for 0.

Any heroes out there?

Thanks!

 

[JE McGimpsey]

There's probably a bit of rounding error in her formula, giving a small
positive result. For instance,

1E-15 will display as 0.00 with the Custom format you gave.

One solution is to use rounding. Instead of

A1: =<her formula>

use

A1: =ROUND(<her formula>, 2)

This will return exactly 0 for all values <5E-3.

 

[Bufo Calvin]

You da geek! Brilliant!

I had told her that the problem was that it was treating
zero as a positive number, but the rounding hadn't occured
to me.

Thanks much!

 

[Dave Peterson]

And sometimes if you say things like this with conviction, they'll believe you!

One way to find out what the formula evaluates to is to select the cell.
Hit F2, then F9, then look at the formula bar
(hit escape to revert to the previous formula or hit Edit|Undo if you actually
modified the cell.)