View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
frecar frecar is offline
external usenet poster
 
Posts: 3
Default Net should be zero but it is not; decimal values aroung the 12

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.


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