Format Cells - Accounting - Discrepancy?
 

Two worksheets in same workbook.

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

In one sheet the cell displays "$ - " while the other sheet
displays "$ (0.00)" 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 "$ (1.00)" has numerous zero, and negative,
values.
The sheet displaying "$ - " has zero values, however no
negative values.

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

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 "glaring" difference in the cell with "$ (0.00)" has annoyed me
for years, finally thought I would ask around. Would love to be able
to replace "$ (0.00)" with "$ - ".

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

Dean Thomas


--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=508457

Format Cells - Accounting - Discrepancy?
 

I 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 "0.00". 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 "Precision as Displayed" 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 "1" then this setting will actually change the .75
to 1.

--
Jim
"Lost4Now" wrote in
message ...

Two worksheets in same workbook.

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

In one sheet the cell displays "$ - " while the other sheet
displays "$ (0.00)" 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 "$ (1.00)" has numerous zero, and negative,
values.
The sheet displaying "$ - " has zero values, however no
negative values.

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

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 "glaring" difference in the cell with "$ (0.00)" has annoyed me
for years, finally thought I would ask around. Would love to be able
to replace "$ (0.00)" with "$ - ".

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

Dean Thomas


--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile:
http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=508457

Format Cells - Accounting - Discrepancy?
 

Thanks Jim & 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 & E formatted "Accounting-Decimal places 2-Style $".

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

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 "$ (53.88)"
- Column C. Several rows later, after a payment, the balance was back
to 0, however the column began displaying "$ (0.00)" instead of "$
- ".

I thought that going back and editing the reimbursement as a payment
instead of a negative charge might display "$ - " 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 "This
was saved in a later version of ....." message - how get I get rid of
it? Saving it under a different name didn't work.

Dean Thomas


--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=508457

Format Cells - Accounting - Discrepancy?
 

Jim, finally caught on to what you where saying about not _exactly_
zero.

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

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

Dean Thomas


--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=508457