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

[frecar]

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.

 

[David Biddulph]

Use the ROUND function.

 

[Chip Pearson]

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)

 

[frecar]

I tried using the round function but it does not solve the problem. Below is
what I am talking about. I am adding amounts that have no more than two
decimals (pennies) but the sum that should be zero is not truly zero. When I
try to upload it to Peachtree, that program recognizes the decimals and does
not permit the posting of the entry because debits minus credits does not
equal zero.

Amount
33,276.55
1,158.33
0.45
66.54
18.79
12,654.58
16,332.28
9,234.81
309.88
2,496.15
-
19.99
(31,452.87)
(1,402.09)
(9,244.74)
-
(514.58)
(44.92)
(31,750.82)
(1,158.33)
0.00000000001637090463191270 (this is the net)

Please e-mail me at (e-mail address removed) if you have any further
information you would like to share.

 

[frecar]

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 spreadsheet 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.

 

[Glenn]

If you are uploading the individual amounts to Peachtree and they are uploading
correctly, and Peachtree is telling you that they don't equal zero, then how is
this an Excel problem? There are workarounds to make the total equal exactly
zero in Excel, using the ROUND() function, but that doesn't solve the problem in
Peachtree, does it?

=ROUND(SUM(A1:A20),2)

 

[David Biddulph]

And if you don't understand where the rounding errors are coming from, work
out the *exact* binary representation of 33276.55 and tell us what it is
when you've done it.

 

[joeu2004]

I tried using the round function but it does not
solve the problem.

It works for me, with your numbers below. Specifically:

=round(sum(a1:a20), 2)


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

 

[joeu2004]

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 -----

 

[joeu2004]

There are workarounds to make the total equal exactly
zero in Excel, using the ROUND() function, but that
doesn't solve the problem in Peachtree, does it?

=ROUND(SUM(A1:A20),2)

For my edification, are you saying that that ROUND expression is not
sufficient to permit the OP to upload the results to Peachtree?

If not, why not?

In this particular case, that ROUND expression does indeed result in
exactly zero with the OP's sample of 20 values.

I don't know how Excel worksheets are uploaded to Peachtree, but at a
minimum, I would think that the transfer would use the CSV format; and
if that's the case, I would expect an exact zero would be transfered.

If your point is: more generally, the ROUND(...,2) only results in
the same internal binary representation as if you manually entered the
number with 2 decimal places, so numbers like 34434.55 are never
exactly that, I understand.

But even so, I would think that does not befoul an upload to
Peachtree.

Surely, the upload must be done in such a way that manually entered
numbers with 2 decimal places can be updated to Peachtree without
complaint -- and represented in Peachtree just as if you had entered
those numbers directly to Peachtree. Right?

So since ROUND(...,2) produces the same internal representation as the
manual entry of that number, I would be surprised if that is not
sufficient for the purpose of uploading to Peachtree.

However, admittedly, I am speaking out of school. I am not familiar
with Peachtree per se or with the upload procedure. If you have some
examples where ROUND(...,2) does not solve the upload problem, perhaps
you can share it with us, just for my edification. You've piqued my
curiosity.

 

[Glenn]

For my edification, are you saying that that ROUND expression is not
sufficient to permit the OP to upload the results to Peachtree?

If not, why not?

However, admittedly, I am speaking out of school. I am not familiar
with Peachtree per se or with the upload procedure. If you have some
examples where ROUND(...,2) does not solve the upload problem, perhaps
you can share it with us, just for my edification. You've piqued my
curiosity.

I, too, am unfamiliar with Peachtree. My point was stated in the part you
snipped from my post:

If you are uploading the individual amounts to Peachtree and they are
uploading correctly, and Peachtree is telling you that they don't equal
zero, then how is this an Excel problem?

If the balance is also being uploaded from Excel, then it appears the ROUND()
function will solve the problem. If just the individual amounts are uploaded,
and the balance is being calculated in Peachtree, then that's where the problem
needs to be fixed.

Maybe the OP will eventually respond with the solution that worked...

 

[joeu2004]

My point was stated in the part you
snipped from my post
[....]
If just the individual amounts are uploaded,
and the balance is being calculated in Peachtree,
then that's where the problem needs to be fixed.

Oh, good point! You might be right. Conceptually, it makes sense
that the OP would upload the data (debits and credits), and Peachtree
would balance them in its own way.

I had ass-u-me-d that the OP was uploading the balance (perhaps in
addition to the debits and credits) because in two postings, that
seemed to be the only value that the OP identified as problematic.

In the OP's initial, the OP writes (annotations added): "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 [the non-zero decimal fraction in the netted amount]
keeps me from uploading __an__ [single] electronic journal entry
because our software Peachtree says __the__ [single] entry is out of
balance".

And later the OP writes: "As you can see, the only place where values
appear beyond the second decimal is in the net figure at the bottom".

Arguably, the list of debits and credits would not be a single journal
entry. But playing devil's advocate, the OP might mean a "journal
transaction", which for Peachtree might be a compound operation.
Again, I am not familiar with Peachtree at all.

Notwithstanding my interpretation of what the OP is trying to do, you
could still make a strong case for seeing this as flaw in Peachtree.
If Peachtree is designed only for financial uses, I think it should
round financial numbers ("dollars and cents") to a reasonable number
of decimal places -- 4 or 6 if not 2.

Be that as it may, finger-pointing, no matter how correct, is not
going to solve the OP's problem. As you and I pointed out, rounding
the netted amount should. I wish the OP would post back to let us
know.

I suspect the initial vague suggestion to use ROUND led the OP to
round the individual amounts(!), but not the SUM; alternatively, the
OP misused ROUND in some way (I can think of several). That is the
only way I can imagine that ROUND did not solve the OP's upload
problem.