accounting format for zero show 0.00 in one cell "-" in another

I have formatted cells with the accounting format with no symbol. Two cells
that compute to zero show differently. One shows 0.00 and the other shows a
"-" dash. I have rechecked all of the cells that are included in the formula
for each and I cannot find any inconsistencies; all are formatted the same.
I realize that this seems petty but for the life of me I cannot determine why
the same formatting shows two different designations for zero. I prefer the
"-" dash.

Can anyone suggest what I can look at to try and resolve this?

Thank you.

If the cells contain formulas, one cell may return a value that's very
small, e.g. .00000000001

This would display as zero in the cell, due to rounding. The cells that
actually contain zero show a dash.

SundanceKidLudwig wrote:
> I have formatted cells with the accounting format with no symbol. Two cells
> that compute to zero show differently. One shows 0.00 and the other shows a
> "-" dash. I have rechecked all of the cells that are included in the formula
> for each and I cannot find any inconsistencies; all are formatted the same.
> I realize that this seems petty but for the life of me I cannot determine why
> the same formatting shows two different designations for zero. I prefer the
> "-" dash.
>
> Can anyone suggest what I can look at to try and resolve this?
>
> Thank you.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,
Thank you for getting back to me on this. The thing is the formulas
involved are only addition and subtraction at 2 decimal places. I rechecked
each cell involved by adding or subtracting the cell amount to ensure it was
zero and all cells except 1 showed "-" as the total. The cell in question is
a formula that takes the result of 1 cell, 90,413.63 and subtracts 90,150.98
which equals 262.65. If I subtract 262.65 from formula in that cell I get
0.00. When I expand it to 24 places the resulting number is
0.000000000008753886504564. I understand why the cell shows 0.00 but I don't
understand how the result of 262.65 is really 262.650000000009000000000000
especially when the two components are only 2 decimal places. Other similar
formulas are not presenting this same problem. I don't get it. Thank you
again for any insight to this issue.






"Debra Dalgleish" wrote:

> If the cells contain formulas, one cell may return a value that's very
> small, e.g. .00000000001
>
> This would display as zero in the cell, due to rounding. The cells that
> actually contain zero show a dash.
>
> SundanceKidLudwig wrote:
> > I have formatted cells with the accounting format with no symbol. Two cells
> > that compute to zero show differently. One shows 0.00 and the other shows a
> > "-" dash. I have rechecked all of the cells that are included in the formula
> > for each and I cannot find any inconsistencies; all are formatted the same.
> > I realize that this seems petty but for the life of me I cannot determine why
> > the same formatting shows two different designations for zero. I prefer the
> > "-" dash.
> >
> > Can anyone suggest what I can look at to try and resolve this?
> >
> > Thank you.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

The information in the following MSKB article may help explain the problem:

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113

SundanceKidLudwig wrote:
> Debra,
> Thank you for getting back to me on this. The thing is the formulas
> involved are only addition and subtraction at 2 decimal places. I rechecked
> each cell involved by adding or subtracting the cell amount to ensure it was
> zero and all cells except 1 showed "-" as the total. The cell in question is
> a formula that takes the result of 1 cell, 90,413.63 and subtracts 90,150.98
> which equals 262.65. If I subtract 262.65 from formula in that cell I get
> 0.00. When I expand it to 24 places the resulting number is
> 0.000000000008753886504564. I understand why the cell shows 0.00 but I don't
> understand how the result of 262.65 is really 262.650000000009000000000000
> especially when the two components are only 2 decimal places. Other similar
> formulas are not presenting this same problem. I don't get it. Thank you
> again for any insight to this issue.
>
>
>
>
>
>
> "Debra Dalgleish" wrote:
>
>
>>If the cells contain formulas, one cell may return a value that's very
>>small, e.g. .00000000001
>>
>>This would display as zero in the cell, due to rounding. The cells that
>>actually contain zero show a dash.
>>
>>SundanceKidLudwig wrote:
>>
>>>I have formatted cells with the accounting format with no symbol. Two cells
>>>that compute to zero show differently. One shows 0.00 and the other shows a
>>>"-" dash. I have rechecked all of the cells that are included in the formula
>>>for each and I cannot find any inconsistencies; all are formatted the same.
>>>I realize that this seems petty but for the life of me I cannot determine why
>>>the same formatting shows two different designations for zero. I prefer the
>>>"-" dash.
>>>
>>>Can anyone suggest what I can look at to try and resolve this?
>>>
>>>Thank you.
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

And to apply that information to this specific instance, the binary
approximations involved are
90413.630000000004656612873077392578125
-90150.979999999995925463736057281494140625
- 262.6499999999999772626324556767940521240234375
--------------------------------------------------
0.0000000000087538865045644342899322509765625
which Excel correctly displays to its documented limit of 15 figures as
0.00000000000875388650456443

Bottom line: the math is correct, but most decimal fractions have no
exact binary representation and hence must be approximated. With
decimal fractions, digits beyond the 15th may not be what you expect and
subtraction may reveal evidence of those approximations. In cases like
this, where you know that anything beyond a certain decimal place is
residue from binary approximations to decimal fractions, then you should
round results to that number of places to avoid surprises.

Jerry

Debra Dalgleish wrote:

> The information in the following MSKB article may help explain the problem:
>
> Floating-point arithmetic may give inaccurate results in Excel
> http://support.microsoft.com/kb/78113
>
> SundanceKidLudwig wrote:
>
>> Debra,
>> Thank you for getting back to me on this. The thing is the formulas
>> involved are only addition and subtraction at 2 decimal places. I
>> rechecked each cell involved by adding or subtracting the cell amount
>> to ensure it was zero and all cells except 1 showed "-" as the total.
>> The cell in question is a formula that takes the result of 1 cell,
>> 90,413.63 and subtracts 90,150.98 which equals 262.65. If I subtract
>> 262.65 from formula in that cell I get 0.00. When I expand it to 24
>> places the resulting number is 0.000000000008753886504564. I
>> understand why the cell shows 0.00 but I don't understand how the
>> result of 262.65 is really 262.650000000009000000000000 especially
>> when the two components are only 2 decimal places. Other similar
>> formulas are not presenting this same problem. I don't get it. Thank
>> you again for any insight to this issue.

Debra and Jerry,

Thank you both for your information! It was a great help and very
informative.

Thanks,
SundanceKidLudwig

"Jerry W. Lewis" wrote:

> And to apply that information to this specific instance, the binary
> approximations involved are
> 90413.630000000004656612873077392578125
> -90150.979999999995925463736057281494140625
> - 262.6499999999999772626324556767940521240234375
> --------------------------------------------------
> 0.0000000000087538865045644342899322509765625
> which Excel correctly displays to its documented limit of 15 figures as
> 0.00000000000875388650456443
>
> Bottom line: the math is correct, but most decimal fractions have no
> exact binary representation and hence must be approximated. With
> decimal fractions, digits beyond the 15th may not be what you expect and
> subtraction may reveal evidence of those approximations. In cases like
> this, where you know that anything beyond a certain decimal place is
> residue from binary approximations to decimal fractions, then you should
> round results to that number of places to avoid surprises.
>
> Jerry
>
> Debra Dalgleish wrote:
>
> > The information in the following MSKB article may help explain the problem:
> >
> > Floating-point arithmetic may give inaccurate results in Excel
> > http://support.microsoft.com/kb/78113
> >
> > SundanceKidLudwig wrote:
> >
> >> Debra,
> >> Thank you for getting back to me on this. The thing is the formulas
> >> involved are only addition and subtraction at 2 decimal places. I
> >> rechecked each cell involved by adding or subtracting the cell amount
> >> to ensure it was zero and all cells except 1 showed "-" as the total.
> >> The cell in question is a formula that takes the result of 1 cell,
> >> 90,413.63 and subtracts 90,150.98 which equals 262.65. If I subtract
> >> 262.65 from formula in that cell I get 0.00. When I expand it to 24
> >> places the resulting number is 0.000000000008753886504564. I
> >> understand why the cell shows 0.00 but I don't understand how the
> >> result of 262.65 is really 262.650000000009000000000000 especially
> >> when the two components are only 2 decimal places. Other similar
> >> formulas are not presenting this same problem. I don't get it. Thank
> >> you again for any insight to this issue.
>
>

THANK YOU! Adding =ROUND(existingSUM(),5) solved it for me.

Was driving me nuts! (Okay, my bride corrected me--nuttier!)

Thank you for this explanation.

MAKE it a GREAT Day!