NumberFormat for small non-zero numbers?
 

I have a column that subtracts two other columns and is displayed as currency. If the other two columns are "close", this column shows zero.

Most of the rows will be zero, and it's the non-zeros that are "interesting". So I've put in a NumberFormat to eliminate the zero, so it's a blank.

However, in many cases the number is not zero, but some very small value... 0.0000000000123

Is there anything I can do to make these fall into the zero format as well? I could round it, but is there an easier way?

NumberFormat for small non-zero numbers?
 

Hi,

Am Mon, 26 Nov 2012 09:30:41 -0800 (PST) schrieb
:

Is there anything I can do to make these fall into the zero format as well? I could round it, but is there an easier way?

no, you have to use ROUND


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

NumberFormat for small non-zero numbers?
 

wrote:
I have a column that subtracts two other columns and
is displayed as currency. If the other two columns are
"close", this column shows zero.
Most of the rows will be zero, and it's the non-zeros
that are "interesting". So I've put in a NumberFormat
to eliminate the zero, so it's a blank.
However, in many cases the number is not zero, but some
very small value... 0.0000000000123
Is there anything I can do to make these fall into the
zero format as well? I could round it, but is there an
easier way?

It would be prudent to explicitly round all currency calculations, even
simple subtractions. For example, =ROUND(A2-A1,2).

That avoids infinitesimal anomalies that arise in Excel arithmetic due to
the way that Excel represents numbers internally (64-bit binary
floating-point).

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10,2)=0.1,TRUE) returns TRUE as expected.

But if you wish, you might use the following Custom format:
[<0.005]"";$#,##0.00

Note that that works only for non-negative currency values.

The following Custom will also display negative currency values properly:
[<=-0.005]-$#,##0.00;[<0.005]"";$#,##0.00

However, both Custom formats display "-" (without quotes) for negative
values that are intended to displayed as just the null string. (A defect,
IMHO.)