software

Two worksheets in same workbook.

Column E formatted - Format Cells - Accounting - Decimal Places 2 -
Symbol $

In one sheet the cell displays quot;$ - quot; while the other sheet
displays quot;$ (0.00)quot; whenever the cells have a zero balance.

The two sheets both use a formula =E469 C470-D470 - different rows but
same columns.

The sheet displaying quot;$ (1.00)quot; has numerous zero, and negative,
values.
The sheet displaying quot;$ - quot; has zero values, however no
negative values.

To test the second sheet, I placed some negative values in the second
sheet and quot;$ (1.00)quot; was displayed, however, a zero value still
displayed quot;$ - quot;

The cells are displayed in one column on a third sheet with a number of
cells from other sheets, which do not have zero or negative balances -
the quot;glaringquot; difference in the cell with quot;$ (0.00)quot; has annoyed me
for years, finally thought I would ask around. Would love to be able
to replace quot;$ (0.00)quot; with quot;$ - quot;.

The column just doesn't line up as orderly as I would like!

Dean Thomas--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: www.excelforum.com/member.php...oamp;userid=29345
View this thread: www.excelforum.com/showthread...hreadid=508457I don't know why two worksheets in the same workbook would display the same
number differently with the same number format. In fact I'm a little
dubious all those conditions can be true.

But anyway, with your number format a value that is not _exactly_ zero will
display as quot;0.00quot;. Enter .000001 in a cell with the accounting format to
see this. It's not usually for formulas to return near zero when the real
answer is exactly zero because of how binary processors deal with base 10
numbers. You might try setting quot;Precision as Displayedquot; on under Tools,
Options, Calculation. This mean that a cell will actually have the value it
appears to have. So your .00001 will in fact be zero with the accounting
format assigned to it. I swear by this setting but you have to be aware
that if a cell has say .75 typed in it but is formatted to show no decimal
places (so it appears as quot;1quot; then this setting will actually change the .75
to 1.

--
Jim
quot;Lost4Nowquot; gt; wrote in
message ...
gt;
gt; Two worksheets in same workbook.
gt;
gt; Column E formatted - Format Cells - Accounting - Decimal Places 2 -
gt; Symbol $
gt;
gt; In one sheet the cell displays quot;$ - quot; while the other sheet
gt; displays quot;$ (0.00)quot; whenever the cells have a zero balance.
gt;
gt; The two sheets both use a formula =E469 C470-D470 - different rows but
gt; same columns.
gt;
gt; The sheet displaying quot;$ (1.00)quot; has numerous zero, and negative,
gt; values.
gt; The sheet displaying quot;$ - quot; has zero values, however no
gt; negative values.
gt;
gt; To test the second sheet, I placed some negative values in the second
gt; sheet and quot;$ (1.00)quot; was displayed, however, a zero value still
gt; displayed quot;$ - quot;
gt;
gt; The cells are displayed in one column on a third sheet with a number of
gt; cells from other sheets, which do not have zero or negative balances -
gt; the quot;glaringquot; difference in the cell with quot;$ (0.00)quot; has annoyed me
gt; for years, finally thought I would ask around. Would love to be able
gt; to replace quot;$ (0.00)quot; with quot;$ - quot;.
gt;
gt; The column just doesn't line up as orderly as I would like!
gt;
gt; Dean Thomas
gt;
gt;
gt; --
gt; Lost4Now
gt; ------------------------------------------------------------------------
gt; Lost4Now's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29345
gt; View this thread: www.excelforum.com/showthread...hreadid=508457
gt;

Thanks Jim amp; others:

More Info - more investigation:

Excel97:

Column C - Credit Card Transaction
Column D - Credit Card Payment
Column E - Balance ------- =E97 C98-D98
Columns C,D amp; E formatted quot;Accounting-Decimal places 2-Style $quot;.

Coulmn E showing quot;$ (0.00)quot; when payment balance is 0.
Would like it to show quot;$ - quot;.

Originally the column did just that.

In June 2004, a credit reimbursement came our way. I entered a
negative amount into Column C - resulting in a display of quot;$ (53.88)quot;
- Column C. Several rows later, after a payment, the balance was back
to 0, however the column began displaying quot;$ (0.00)quot; instead of quot;$
- quot;.

I thought that going back and editing the reimbursement as a payment
instead of a negative charge might display quot;$ - quot; again -- didn't.

Not a major, or even minor, problem - just puzzling!

I did use 97 in the beginning, switched to 2000, but came back to 97 -
could a difference between the two be the cause?

While on this subject - since I have gone back to 97, I get the quot;This
was saved in a later version of .....quot; message - how get I get rid of
it? Saving it under a different name didn't work.

Dean Thomas--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: www.excelforum.com/member.php...oamp;userid=29345
View this thread: www.excelforum.com/showthread...hreadid=508457
Jim, finally caught on to what you where saying about not _exactly_
zero.

You suggested the following {You might try setting quot;Precision as
Displayedquot; on under Tools, Options, Calculation.} Haven't gone there -
will check it out though.

I used an IF function quot;=IF(C95 A96-B96lt;0.01,0,C95 A96-B96)quot;. No more
quot;$ (0.00)quot; - I've got my quot;$ - quot; back - THANKS A MILLION for
responding.

Dean Thomas--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: www.excelforum.com/member.php...oamp;userid=29345
View this thread: www.excelforum.com/showthread...hreadid=508457