Re: Minus Zero

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Jan 30, 12:22 pm, "Niek Otten" <nicol...@xxxxxxxxx> wrote:
<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 -----

On Jan 30, 12:22 pm, "Niek Otten" <nicol...@xxxxxxxxx> wrote:
<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

"joeu2004" <joeu2...@xxxxxxxxxxx> wrote in message

news:7faf0439-0bd2-4860-9193-84bd906dcf22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 30, 2:00 am, "mlv" <mike.safetycatchvinc...@xxxxxxxxx> wrote:

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 work***.  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 -----

On Jan 30, 2:00 am, "mlv" <mike.safetycatchvinc...@xxxxxxxxx> wrote:



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 work***, 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 work*** uses only currency calculations, is ticking the
'Precision as displayed' box in Tools/Options/Calculation (Excel 2002 SP3)
the answer?

Thanks,
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply
.

Quantcast