Minus Zero

[mlv]

I'm probably missing something obvious, but I have a worksheet where some
cells are formatted for currency to two decimal places, with negative
numbers showing in red with a leading 'minus' sign.

The problem I have is that when the result is zero, the number is displayed
as -0.00 (coloured red).

A typical formula for the cell is:

=IF(N1<>"",IF(SUM(N13:O38)=0,SUM(P7,J39)-SUM(N8:O12),""),"")

All the cells referenced by the formula are formatted for currency to two
decimal places, with negative numbers showing in red with a leading 'minus'
sign.

In the above example:

Cell N1 = 40.00
SUM(N13:O38) = 0.00
SUM(P7,J39) = 5,290.90
SUM(N8:O12) = 5,290.90

What would possess the formula to return -0.00 (in red)?

If I remove the 'IF' function and change the formula to just:

=SUM(P7,J39)-SUM(N8:O12)

the formula then returns 0.00 (in black)

Thanks,

 

[joeu2004]

The problem I have is that when the result is zero,
the number is displayed as -0.00 (coloured red).
A typical formula for the cell is:
=IF(N1<>"",IF(SUM(N13:O38)=0,SUM(P7,J39)-SUM(N8:O12),""),"")

Try:

=if(N1<>"",if(round(sum(N13:038),2)=0,
round(P7+J39-sum(N8:O12),2),""),"")

And in other cells, use ROUND(formula,2) prolificly.


----- original posting -----

 

[Harald Staff]

A result like
-0.004
would with your formatting display as
-0.00 (coloured red).
It is not zero, it is -0.004

HTH. Best wishes Harald

 

[Shane Devenshire]

Hi,

Computers work in binary, we work in decimals which results in
approximations by Excel and any computer.

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

 

[mlv]

Thanks guys,

I was aware of the 'round' function, but didn't expect this to be an issue
as I was only adding or subtracting currency that had been entered to two
decimal places.

I did look at the -0.00 (red) result to 30 decimal places, and there are
some non-zero digits after the twelfth decimal place. My concern is, where
are they coming from? I've checked every cell that the formula is looking
at, and the numbers have only zeros after the second decimal place.

If I sum every cell in the worksheet, the result still has only zeros after
the second decimal place.

Is there any global setting that will round every cell entry to 2 decimal
places? To do each cell at this stage will be a forever job.

As the entire worksheet uses only currency calculations, is ticking the
'Precision as displayed' box in Tools/Options/Calculation (Excel 2002 SP3)
the answer?

Thanks,

 

[Jerry W. Lewis]

Since the cells are formatted to 2 decimal places and you are only adding and
subtracting, Precision as Displayed, should give you the results that you
expect. It may not work so well on sheets that do not meet these criteria,
but fortunately is not an option that persists into other workbooks.

Jerry

 

[joeu2004]

I was only adding or subtracting currency that had been
entered to two decimal places.
[....]
is ticking the 'Precision as displayed' box in
Tools/Options/Calculation (Excel 2002 SP3) the answer?

No, not entirely.

It might eliminate the need to use ROUND for the result of each cell.
It might even seem to work throughout in your worksheet. But
generally, it will eliminate the need to use ROUND in some places.

By your own example in your original posting, you are not "only adding
and subtracting". You have other kinds of expressions, notably IF
functions that compare arithmetic expressions.

So, consider the following example. Set "Precision as displayed", and
format a column as Currency with 2 decimal places. In A1:A3, enter
the following numbers (note: the order matters!): 12.22, -12.20 and
-0.02. In A4, enter: =SUM(A1:A3).

Yes, the result in A4 is now exactly zero. But IF(SUM(A1:A3)=A4,TRUE)
returns FALSE(!).

The point is: "precision as displayed" applies only to the final
result stored into a cell, not to intermediate calculations. (FYI, IF
(A1+A2+A3=0,TRUE) also returns FALSE.)

And by the way, it appears that the cell must be formatted as a number
format other than General for "precision as displayed" to be
effective.

I did look at the -0.00 (red) result to 30 decimal places,
and there are some non-zero digits after the twelfth decimal
place. My concern is, where are they coming from?

(If you try the following examples, be sure that "precision as
displayed" is not selected.)

In my example, 12.22, -12.20 and -0.02 cannot be represented exactly
in the binary form that Excel uses internally (as do most
applications). Instead, they are stored internally exactly as:

12.2200000000000,006394884621840901672840118408203125
-0.0200000000000000,004163336342344337026588618755340576171875
-12.1999999999999,99289457264239899814128875732421875

(The comma demarks 15 "significant digits" to the left.)

When those numbers are added in that order, the exact internal result
is:

0.00000000000000134961486430996,0060972229656834881853957780234627705784333784322370775043964385986328125

It might be noted that Excel does have some heuristics to try to
ameliorate the problem. There are some instances where Excel will
replace some infinitesimal results with exactly zero.

But as you can see, the heuristic is not "perfect". At issue is:
just how small should "infinitesimal" be? That's rhetorical; there is
no single answer that is right for all applications.

Also, it is important to understand that order sometimes matters. If
you compute SUM(A1,A3,A2) (or change the order of the values in
A1:A3), the result will be exactly zero.

In this case, Excel's heuristics cause this; compare with =(A1+A3+A2),
with the "extra" parentheses. Sometimes, it is simply an artifact of
the way that computers do binary arithmetic; the relative magnitude of
each pair of operands added or subtracted may make a difference.

HTH.


----- original posting -----

 

[joeu2004]

Errata....

It might eliminate the need to use ROUND for the result of each cell.
It might even seem to work throughout in your worksheet. But
generally, it will eliminate the need to use ROUND in some places.

Ah, that elusive "not". I meant to write: it will __not__ eliminate
the need to use ROUND in some places". Klunk!


----- original posting -----

I was only adding or subtracting currency that had been
entered to two decimal places.
[....]
is ticking the 'Precision as displayed' box in
Tools/Options/Calculation (Excel 2002 SP3) the answer?

No, not entirely.

It might eliminate the need to use ROUND for the result of each cell.
It might even seem to work throughout in your worksheet.  But
generally, it will eliminate the need to use ROUND in some places.

By your own example in your original posting, you are not "only adding
and subtracting".  You have other kinds of expressions, notably IF
functions that compare arithmetic expressions.

So, consider the following example.  Set "Precision as displayed", and
format a column as Currency with 2 decimal places.  In A1:A3, enter
the following numbers (note: the order matters!):  12.22, -12.20 and
-0.02.  In A4, enter:  =SUM(A1:A3).

Yes, the result in A4 is now exactly zero.  But IF(SUM(A1:A3)=A4,TRUE)
returns FALSE(!).

The point is:  "precision as displayed" applies only to the final
result stored into a cell, not to intermediate calculations.  (FYI, IF
(A1+A2+A3=0,TRUE) also returns FALSE.)

And by the way, it appears that the cell must be formatted as a number
format other than General for "precision as displayed" to be
effective.

I did look at the -0.00 (red) result to 30 decimal places,
and there are some non-zero digits after the twelfth decimal
place.  My concern is, where are they coming from?

(If you try the following examples, be sure that "precision as
displayed" is not selected.)

In my example, 12.22, -12.20 and -0.02 cannot be represented exactly
in the binary form that Excel uses internally (as do most
applications).  Instead, they are stored internally exactly as:

 12.2200000000000,006394884621840901672840118408203125
 -0.0200000000000000,004163336342344337026588618755340576171875
-12.1999999999999,99289457264239899814128875732421875

(The comma demarks 15 "significant digits" to the left.)

When those numbers are added in that order, the exact internal result
is:

0.00000000000000134961486430996,0060972229656834881853957780234627705784333­784322370775043964385986328125

It might be noted that Excel does have some heuristics to try to
ameliorate the problem.  There are some instances where Excel will
replace some infinitesimal results with exactly zero.

But as you can see, the heuristic is not "perfect".  At issue is:
just how small should "infinitesimal" be?  That's rhetorical; there is
no single answer that is right for all applications.

Also, it is important to understand that order sometimes matters.  If
you compute SUM(A1,A3,A2) (or change the order of the values in
A1:A3), the result will be exactly zero.

In this case, Excel's heuristics cause this; compare with =(A1+A3+A2),
with the "extra" parentheses.  Sometimes, it is simply an artifact of
the way that computers do binary arithmetic; the relative magnitude of
each pair of operands added or subtracted may make a difference.

HTH.

----- original posting -----

Thanks guys,

I was aware of the 'round' function, but didn't expect this to be an issue
as I was only adding or subtracting currency that had been entered to two
decimal places.

I did look at the -0.00 (red) result to 30 decimal places, and there are
some non-zero digits after the twelfth decimal place.  My concern is,where
are they coming from?  I've checked every cell that the formula is looking
at, and the numbers have only zeros after the second decimal place.

If I sum every cell in the worksheet, the result still has only zeros after
the second decimal place.

Is there any global setting that will round every cell entry to 2 decimal
places?  To do each cell at this stage will be a forever job.

As the entire worksheet uses only currency calculations, is ticking the
'Precision as displayed' box in Tools/Options/Calculation (Excel 2002 SP3)
the answer?

Thanks,

- Show quoted text -

 

[Niek Otten]

<And by the way, it appears that the cell must be formatted as a number
format other than General for "precision as displayed" to be
effective.>

Yes, the wording is misleading. It should be something like "Precision as
Formatted".
But it is good that it is this way. It means that the "rounding" will only
apply to cells that are explicitly formatted and that is the way it should
be; no unexpected side-effects.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

I was only adding or subtracting currency that had been
entered to two decimal places.
[....]
is ticking the 'Precision as displayed' box in
Tools/Options/Calculation (Excel 2002 SP3) the answer?

No, not entirely.

It might eliminate the need to use ROUND for the result of each cell.
It might even seem to work throughout in your worksheet. But
generally, it will eliminate the need to use ROUND in some places.

By your own example in your original posting, you are not "only adding
and subtracting". You have other kinds of expressions, notably IF
functions that compare arithmetic expressions.

So, consider the following example. Set "Precision as displayed", and
format a column as Currency with 2 decimal places. In A1:A3, enter
the following numbers (note: the order matters!): 12.22, -12.20 and
-0.02. In A4, enter: =SUM(A1:A3).

Yes, the result in A4 is now exactly zero. But IF(SUM(A1:A3)=A4,TRUE)
returns FALSE(!).

The point is: "precision as displayed" applies only to the final
result stored into a cell, not to intermediate calculations. (FYI, IF
(A1+A2+A3=0,TRUE) also returns FALSE.)

And by the way, it appears that the cell must be formatted as a number
format other than General for "precision as displayed" to be
effective.

I did look at the -0.00 (red) result to 30 decimal places,
and there are some non-zero digits after the twelfth decimal
place. My concern is, where are they coming from?

(If you try the following examples, be sure that "precision as
displayed" is not selected.)

In my example, 12.22, -12.20 and -0.02 cannot be represented exactly
in the binary form that Excel uses internally (as do most
applications). Instead, they are stored internally exactly as:

12.2200000000000,006394884621840901672840118408203125
-0.0200000000000000,004163336342344337026588618755340576171875
-12.1999999999999,99289457264239899814128875732421875

(The comma demarks 15 "significant digits" to the left.)

When those numbers are added in that order, the exact internal result
is:

0.00000000000000134961486430996,0060972229656834881853957780234627705784333784322370775043964385986328125

It might be noted that Excel does have some heuristics to try to
ameliorate the problem. There are some instances where Excel will
replace some infinitesimal results with exactly zero.

But as you can see, the heuristic is not "perfect". At issue is:
just how small should "infinitesimal" be? That's rhetorical; there is
no single answer that is right for all applications.

Also, it is important to understand that order sometimes matters. If
you compute SUM(A1,A3,A2) (or change the order of the values in
A1:A3), the result will be exactly zero.

In this case, Excel's heuristics cause this; compare with =(A1+A3+A2),
with the "extra" parentheses. Sometimes, it is simply an artifact of
the way that computers do binary arithmetic; the relative magnitude of
each pair of operands added or subtracted may make a difference.

HTH.


----- original posting -----

 

[joeu2004]

<And by the way, it appears that the cell must be formatted
as a number format other than General for "precision as displayed"
to be effective.>

Yes, the wording is misleading. It should be something like
"Precision as Formatted". But it is good that it is this way.
It means that the "rounding" will only apply to cells that are
explicitly formatted and that is the way it should be; no
unexpected side-effects.

And I might add that it makes sense that PAD does not apply to cells
with a General format when I realized that "precision as displayed"
means "precision of result", not "precision of computation".

But it caught me by surprise again when I was in the middle of testing
my examples, thought I had formatted the cells explicitly, but still
had a non-zero result unexpectedly. I thought it was worth mentioning
because I can imagine that it might be a common mistake, both in
interpretation of the option and in its usage. But in hindsight, it
was probably a distracting digression.


----- original posting -----

<And by the way, it appears that the cell must be formatted as a number
format other than General for "precision as displayed" to be
effective.>

Yes, the wording is misleading. It should be something like "Precision as
Formatted".
But it is good that it is this way. It means that the "rounding" will only
apply to cells that are explicitly formatted and that is the way it should
be; no unexpected side-effects.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

I was only adding or subtracting currency that had been
entered to two decimal places.
[....]
is ticking the 'Precision as displayed' box in
Tools/Options/Calculation (Excel 2002 SP3) the answer?

No, not entirely.

It might eliminate the need to use ROUND for the result of each cell.
It might even seem to work throughout in your worksheet.  But
generally, it will eliminate the need to use ROUND in some places.

By your own example in your original posting, you are not "only adding
and subtracting".  You have other kinds of expressions, notably IF
functions that compare arithmetic expressions.

So, consider the following example.  Set "Precision as displayed", and
format a column as Currency with 2 decimal places.  In A1:A3, enter
the following numbers (note: the order matters!):  12.22, -12.20 and
-0.02.  In A4, enter:  =SUM(A1:A3).

Yes, the result in A4 is now exactly zero.  But IF(SUM(A1:A3)=A4,TRUE)
returns FALSE(!).

The point is:  "precision as displayed" applies only to the final
result stored into a cell, not to intermediate calculations.  (FYI, IF
(A1+A2+A3=0,TRUE) also returns FALSE.)

And by the way, it appears that the cell must be formatted as a number
format other than General for "precision as displayed" to be
effective.

I did look at the -0.00 (red) result to 30 decimal places,
and there are some non-zero digits after the twelfth decimal
place.  My concern is, where are they coming from?

(If you try the following examples, be sure that "precision as
displayed" is not selected.)

In my example, 12.22, -12.20 and -0.02 cannot be represented exactly
in the binary form that Excel uses internally (as do most
applications).  Instead, they are stored internally exactly as:

 12.2200000000000,006394884621840901672840118408203125
 -0.0200000000000000,004163336342344337026588618755340576171875
-12.1999999999999,99289457264239899814128875732421875

(The comma demarks 15 "significant digits" to the left.)

When those numbers are added in that order, the exact internal result
is:

0.00000000000000134961486430996,0060972229656834881853957780234627705784333­784322370775043964385986328125

It might be noted that Excel does have some heuristics to try to
ameliorate the problem.  There are some instances where Excel will
replace some infinitesimal results with exactly zero.

But as you can see, the heuristic is not "perfect".  At issue is:
just how small should "infinitesimal" be?  That's rhetorical; there is
no single answer that is right for all applications.

Also, it is important to understand that order sometimes matters.  If
you compute SUM(A1,A3,A2) (or change the order of the values in
A1:A3), the result will be exactly zero.

In this case, Excel's heuristics cause this; compare with =(A1+A3+A2),
with the "extra" parentheses.  Sometimes, it is simply an artifact of
the way that computers do binary arithmetic; the relative magnitude of
each pair of operands added or subtracted may make a difference.

HTH.

----- original posting -----

Thanks guys,

I was aware of the 'round' function, but didn't expect this to be an issue
as I was only adding or subtracting currency that had been entered to two
decimal places.

I did look at the -0.00 (red) result to 30 decimal places, and there are
some non-zero digits after the twelfth decimal place. My concern is, where
are they coming from? I've checked every cell that the formula is looking
at, and the numbers have only zeros after the second decimal place.

If I sum every cell in the worksheet, the result still has only zeros
after
the second decimal place.

Is there any global setting that will round every cell entry to 2 decimal
places? To do each cell at this stage will be a forever job.

As the entire worksheet uses only currency calculations, is ticking the
'Precision as displayed' box in Tools/Options/Calculation (Excel 2002 SP3)
the answer?

Thanks,

 

[Harvey Schmidlapp]

An alternative to round()...

Instead of comparing your sum to zero as you do here:

=IF(SUM(A1:B9)=0,"Zero","Non-Zero")

Consider comparing the absolute value to a suitably small decimal:

=IF(ABS(SUM(A1:B9))<0.001,"Zero","Non-Zero")

You can replace 0.001 with any number that gives you the precision you
want.

 

[joeu2004]

An alternative to round()...
[....]

=IF(ABS(SUM(A1:B9))<0.001,"Zero","Non-Zero")

You can replace 0.001 with any number that gives you the
precision you want.

And that might be okay when using "precision as displayed" and
explicit numeric formats (not General). I also think that using ABS
is more efficient that using ROUND.

(But I suspect the efficiency difference is perceptible only when
there are many tens of thousands of cells using ROUND or ABS.)

However, as a modus operandi -- especially when not using "precision
as displayed", or when using PAD and mixing General and other numeric
formatted cells -- I would choose 0.005 because I believe most people
want a WYSIWYG solution. When they format the cell as Number with 2
dp and the cell displays 0.01, the underlying value can be 0.005 or
larger [1].

In any case, I suspect that ROUND is the more intuitive solution for
most people.

In general, they are not comparing against zero, but some other value
or expression. For example, to test SUM(A1:B9)=C1+C2 with 2 dp, you
might write IF(ABS(SUM(A1:B9)-(C1+C2))<0.005,...). I would write IF
(ROUND(SUM(A1:B9),2)=ROUND(C1+C2,2),...) [2]. I suspect the latter is
more intuitive, if for no other reason than it mimicks what Excel
formatting does, which most people understand (except that many
incorrectly expect the underlying value to reflect that). Moreover,
ROUND can be used within other expressions; it becomes a habit, as I
believe it should be. [3]

But reasonable people might disagree reasonably. "Pity the
horse" .


End Notes:

[1] I wrote: "when they format the cell as Number with 2 dp and the
cell displays 0.01, the underlying value can be 0.005 or larger".
That is not precisely right. For example, the underlying value could
be 0.005-2^-61, which is obviously less than 0.005. What I should say
is: the underlying value can be 0.005 or larger when formatted to 15
"significant digits".

FYI, Excel can indeed represent 0.005-2^-61 as a distinctly different
internal value than 0.005. In fact, it is the largest number less
than 0.005. The exact internal representation is
0.00499999999999999,923672167057020487845875322818756103515625 (the
comma demarcates 15 "significant digits" to the left). Although such
numbers seem contrived, they can arise as the result of normal
calculations.

[2] Actually, I use ROUND only in Excel and occassionally in Excel
VBA. I am in the habit of using the ABS formulation in "real"
computer languages unless it's convenient to use ftrunc or similar
function.

[3] I also wonder if there are borderline situations where, due to the
peculiarities of computer binary arithmetic, ABS(x-y)<0.005 might be
true where ROUND(x,2)=ROUND(y,2) is false or vice versa. Maybe not.
This is wild speculation; I have not explored it.


----- original posting -----