excel 2010 displaying 0 as an infinitesimal number in scientific notation

[indesignkat]

I'm guessing it's just an Excel oddity, but when I sum numbers I'm sometimes getting ridiculously tiny numbers in scientific notation instead of just getting a 0. For example, copy/paste the below numbers into a blank sheet. If I click on the top one and drag down, the little info area on the bottom right tells me the average is 0, the count is 4, and the sum is 0. If I click on the bottom one and drag up, it tells me the average is -9.4591E-13 and the total is -3.78364E-12. I wouldn't care overmuch, except it's showing the tiny scientific notation number in my pivot table instead of showing 0.

Any ideas?

-20,914.43
-35,853.30
56,756.89
10.84

idk

 

[Mike H..]

I'm guessing it's just an Excel oddity, but when I sum numbers I'm sometimes getting ridiculously tiny numbers in scientific notation instead of just getting a 0. For example, copy/paste the below numbers into a blank sheet. If I click on the top one and drag down, the little info area on the bottom right tells me the average is 0, the count is 4, and the sum is 0. If I click on the bottom one and drag up, it tells me the average is -9.4591E-13 and the total is -3.78364E-12. I wouldn't care overmuch, except it's showing the tiny scientific notation number in my pivot table instead of showing 0.

Any ideas?

-20,914.43
-35,853.30
56,756.89
10.84

idk

Hi,

What you're seeing is a result of Excels precision in carrying out floating point arithmetic. Like nearly all spreadsheet packages Excel adheres (nearly) to IEEE754 which is the computer standard for carrying out floating point math. It's all explained at the link below. Take particular note on the section about rounding to catch these precision errors

LINK

 

[Robyn S521]

Hello. I read the article about the floating point math and can "fix" my problem. However, I am curious to see if anyone has seen this before and might know why it is happening in the first place. It is driving me crazy!
I am using Excel 2013. I have some dollar amount data entered in a spreadsheet and it is formatted Accounting with 2 decimal points and no $. I used the data to make a pivot table. The pivot table amounts are also formatted Accounting with 2 decimal points and no $. The rows should sum to zero and be displayed as a dash "-", but a particular row is summing to 1.81898940354586E-12 and displays "0". I have tried to apply the round function to the data, but it does not fix the problem. Changing the precision as displayed option fixes the problem, but I was wondering what I did to cause the problem in the first place.
All the data was hand entered. The 9/13/14 data uses the round function. Please see the file and thank you for your help!

 

[NARAYANK991]

Hi ,

The problem is always one of subtracting large amounts where the result will be infinitesimal ; using a calculated field with a formula such as :

=ROUND(SUM( Amount ) , 2)

will ensure that the Grand Total of the rows is 0.

Narayan

 

[Robyn S521]

Thank you, Narayan. I appreciate your response; however, I do not understand it as it relates to the problem in my spread sheet. As you can see in my spreadsheet, the numbers in the spreadsheet are not large. $10,000 being the largest.

The numbers are dollar values of bank account transfer data. The amount leaving (debited in) one account needs to equal the amount coming in (credited to) the other account, the result being zero when they are combined.

I am going through each bank statement, entering each transfer amount and letting the pivot table do the work of combining the transfers from all the accounts. I should not need to enter a formula for each transfer amount I enter. I use the row totals to identify anything for further investigation. Even though I simplified the spreadsheet I uploaded because I am working with 54 bank accounts that have transfers moving between them, you can still see the problem still exists. I have checked everything I can think of that would result in the anomaly, but am still not sure what is causing the issue in this one line on my pivot table. It does not happen on any other pivot table line resulting from the thousands of lines in the data.

I am an Excel user that loves to know why Excel does something the way it does. I thought that maybe someone here might have faced my issue in the past and could tell me what I might have done to cause the problem in the first place. If no one knows right now, I can move on. I will post here if I ever figure it out. Thank you again for responding Narayan.

If anyone else would like to look at the spreadsheet I uploaded and help me, I would appreciate it.
Respectfully,
Robyn