Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12 dec
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12 dec
Use the ROUND function.
-- David Biddulph "frecar" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12 dec
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
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 if you have any further information you would like to share. "David Biddulph" wrote: Use the ROUND function. -- David Biddulph "frecar" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
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) frecar wrote: 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 if you have any further information you would like to share. "David Biddulph" wrote: Use the ROUND function. -- David Biddulph "frecar" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
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. -- David Biddulph "Glenn" wrote in message ... 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) frecar wrote: 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 if you have any further information you would like to share. "David Biddulph" wrote: Use the ROUND function. -- David Biddulph "frecar" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
On Jan 21, 7:19 am, frecar wrote:
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 ----- On Jan 21, 7:19*am, frecar wrote: 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 if you have any further information you would like to share. "David Biddulph" wrote: Use the ROUND function. -- David Biddulph "frecar" wrote in message ... 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 - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
On Jan 21, 7:43 am, frecar 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,01110223024625156540423631668090 8203125 66.54 66.5400000000000,062527760746888816356658935546875 18.79 18.7899999999999,991473487170878797769546508789062 5 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,984368059813277795910835266113281 25 -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 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 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.- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
On Jan 21, 7:56*am, Glenn wrote:
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
joeu2004 wrote:
On Jan 21, 7:56 am, Glenn wrote: 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? <SNIP 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... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Net should be zero but it is not; decimal values aroung the 12
On Jan 22, 6:13*am, Glenn wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Values | Excel Discussion (Misc queries) | |||
Hiding certain decimal values | Excel Worksheet Functions | |||
Summing decimal values to time | New Users to Excel | |||
Finding Values With more than 2 decimal Places | Excel Discussion (Misc queries) | |||
SUBTRACTING TIME VALUES INSTEAD OF DECIMAL!! | Excel Discussion (Misc queries) |