Re: Net should be zero but it is not; decimal values aroung the 12

On Jan 21, 7:43 am, frecar <fre...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Below are the numbers that I am working with.
[....]
33276.550000000000000000000000000000
[....]
As you can see, the only place where values appear beyond
the second decimal is in the net figure at the bottom.

The point is: fractions like 0.55 cannot be represented exactly when
using the normal binary internal representation, which Excel and most
applications do. No amount of Excel formatting will reveal that to
you, since Excel formats only the first 15 "significant" digits. And
for that reason, it does not make sense to format more than 15
significant digits (10 decimal places for the above number).

To make the point clear, 33,276.55 is represented in binary exactly as
33276.5500000000,02910383045673370361328125. (The comma demarks the
15 significant digits to the left.) The table at the end below [3]
shows the exact internal representation for all of your example
numbers.

The significance of these numerical "errors" depends, to some degree,
on the order in which the numbers are added. That is, sometimes they
are innocuous and/or cancel themselves out. This makes the problem
seem even more mysterious.

Compounding the mystery is the fact that Excel (at least Excel 2003)
implements some heuristics (algorithms) to try to ameliorate these
numerical "errors".

For example, if you add the first two numbers in your example (B1:
=A1+A2), the result will display as 34,434.88 [1], but the internal
representation of that result is not exactly the same as the internal
representation of entering 34434.88 [2] into a cell. Sometimes, such
differences cause comparisions to fail. But in this particular, =
(B1=34434.88) returns TRUE.

In most cases, prudent use of the ROUND function ensures that the
result has the same internal binary representation as if you had
manually entered what you displayed in the cell with the same number
of decimal places.

For example, =round(A1+A2,2) does exactly match the internal
representation of 34434.88. And =round(sum(A1:A20),2) is exactly
zero.

HTH.


End Notes:

[1] =A1+A2 (34,434.88) is represented internally exactly as
34434.8800000000,04656612873077392578125.

[2] 34434.88 is represented internally exactly as
34434.8799999999,973806552588939666748046875.

[3] The following is a table of the internal representation
of all your numbers. Sorry for formatting anomalies.

33,276.55 33276.5500000000,02910383045673370361328125
1,158.33 1158.32999999999,9927240423858165740966796875
0.45
0.450000000000000,011102230246251565404236316680908203125
66.54 66.5400000000000,062527760746888816356658935546875
18.79 18.7899999999999,9914734871708787977695465087890625
12,654.58 12654.5799999999,99927240423858165740966796875
16,332.28 16332.2800000000,00654836185276508331298828125
9,234.81 9234.80999999999,9490682967007160186767578125
309.88 309.879999999999,9954525264911353588104248046875
2,496.15 2496.15000000000,009094947017729282379150390625
0 0
19.99 19.9899999999999,98436805981327779591083526611328125
-31,452.87 -31452.8699999999,9898136593401432037353515625
-1,402.09 -1402.08999999999,9918145476840436458587646484375
-9,244.74 -9244.73999999999,9781721271574497222900390625
0 0
-514.58 -514.580000000000,0409272615797817707061767578125
-44.92 -44.9200000000000,017053025658242404460906982421875
-31,750.82 -31750.8199999999,997089616954326629638671875
-1,158.33 -1158.32999999999,9927240423858165740966796875



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

On Jan 21, 7:43 am, frecar <fre...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Thank for responding.  Below are the numbers that I am working with.  Each
number is the difference of two others.  However, all the numbers that I am
working with (using for subtraction) were entered into our spread*** with
no more than two decimals (cents).  Some are negative numbers.

Amount
33276.550000000000000000000000000000
1158.330000000000000000000000000000
0.450000000000000000000000000000
66.540000000000000000000000000000
18.790000000000000000000000000000
12654.580000000000000000000000000000
16332.280000000000000000000000000000
9234.810000000000000000000000000000
309.880000000000000000000000000000
2496.150000000000000000000000000000
0.000000000000000000000000000000
19.990000000000000000000000000000
-31452.870000000000000000000000000000
-1402.090000000000000000000000000000
-9244.740000000000000000000000000000
0.000000000000000000000000000000
-514.580000000000000000000000000000
-44.920000000000000000000000000000
-31750.820000000000000000000000000000
-1158.330000000000000000000000000000
0.000000000016370904631912700000  (This is the net of all the above numbers;
s/be zero all the way through)

As you can see, the only place where values appear beyond the second decimal
is in the net figure at the bottom.  Please let me know if you have any more
information.  I did not try your suggestion because some of the numbers have
to be negatives and as I understand absolute value, all results will yield
positive numbers.  Thanks.



"Chip Pearson" wrote:
Many computer programs don't use equality to zero to test subtractive
results. Instead, if the difference is less than some really small
number, you substitute 0 for the actual result. E.g, instead of

=A1-B1
use
=IF(ABS(A1-B1)<0.00000001,0,A1-B1)

Thus, if the difference between A1 and B1 is less than 0.00000001, the
numbers are assume to be "close enough" to equal that they can be
treated as such.  This isn't a bug in Excel. It is simply the result
of how nearly every piece of software in the world works with very
small numbers.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 20 Jan 2009 11:43:02 -0800, frecar
<fre...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I netted debits and credits and the answer is zero - it appears - as it
should be.  However, there are decimal values beginning about decimal place
twelve.  This keeps me from uploading an electronic journal entry because our
software Peachtree says the entry is out of balance.- Hide quoted text -

- Show quoted text -

.

Loading