Sum not working

[ShaunAldridge]

Hi,

I have an issue whilst summing and it's really making my brain hurt. I hope someone can help me please!

I have a list of numbers that are all rounded to 2dp, which I have confirmed by selecting each cell and checking each value. The total of the values is 8579.52 which the sum function gives correctly in the cell that it's calculated in. BUT, when I paste values of the total it gives 8579.51999999999. I cant for the life of me figure out which value isn't rounding to 2dp correctly. The same thing happens if I use a sumif but it seems to work correctly if I sum half of the values, then the other half and add them together!


Does anyone have any ideas? I'm trying to import a csv to our accounting system and it is rejecting it because I guess it can see that one of the values isn't rounded correctly.

Thanks,

Shaun

 

[curiouscat408]

PS....

So I have a list of values that I have rounded using the round function

You need to explicitly apply the ROUND function to all __calculated__ values, including the "sum" results, even the summed values are already rounded.

You do not need to apply the ROUND function to constants, like literally 123.56, as long as they have no more than 2 decimal fraction digits.

 

[curiouscat408]

PPS....

Upload an Excel file to a file-sharing website [....] Post the shared download URL of the file(s).

If you cannot post the URL in the normal way, type it out without special characters. For example, www dot mrexcel dot com.

Some of these forums have idiotic restrictions on users until they have posted "enough" messages. Editing postings, is one of them (sigh). So, sorry for the incessant postings.

 

[Joe4]

Some of these forums have idiotic restrictions on users until they have posted "enough" messages.

Not so "idiotic", as they are Spam control measures (we don't just make restrictions "willy nilly" for no good reason).

 

[ShaunAldridge]

If you cannot post the URL in the normal way, type it out without special characters. For example, www dot mrexcel dot com.

Some of these forums have idiotic restrictions on users until they have posted "enough" messages. Editing postings, is one of them (sigh). So, sorry for the incessant postings.

Can you give an example of one I can use?

 

[ShaunAldridge]

I have gone back to the original source, a statement of account from one of our suppliers. It's an xlsx file and the same thing happens here. So it's not to do with saving as a csv etc. Values are formatted as numbers. I round each of them to 2dp, sum the rounded numbers and the sum gives 8579.51999999999 when I show it as 11dp. I dont even need to paste values, this is what the sum shows

 

[curiouscat408]

Can you give an example of one I can use?

I like box.net/files. It is easy to upload files and get and copy the shared URL. Other people like dropbox.com. I'm not familiar with it.

I round each of them to 2dp, sum the rounded numbers and the sum gives 8579.51999999999 when I show it as 11dp. I dont even need to paste values, this is what the sum shows

Previously, you wrote that __initially__ the sum result __is__ (not merely "shows") 8579.52, and it __becomes__ (not merely "shows") 8579.51999999999 only after copy-and-pasting values.

That is plausible. But are you changing the explanation now?

You say that you sum "rounded numbers". You do not say that you __explicitly__ round the sum itself, using the ROUND function.

We are really throwing darts in the dark. You have not shown us the values. You have not shown us the formulas that derive those values, if any. You have not shown us the formula that you use to sum the values.

It is not unusual at all for arithmetic operations to result in anomalies like you describe, even simple addition, especially with a mix of positive and negative values.

Here is one simple example:

B6: =0.85+0.06+0.09
B7: =0.85+0.09+0.06

Note: The only difference is the order of addition.

Both appear to be 1.000000000000000, even when formatted to display 15 decimal places. And:

=B6=B7 returns TRUE
=B6-B7 returns 0.00E+00 when formatted as Scientific (exact zero)

But:

=B6-B7=0 returns FALSE
=ISNUMBER(MATCH(B6,B7,0)) returns FALSE
=SUM(B6,-B7) returns -1.11022302462516E-16

That difference is not even visible. Examples of visible differences are even more common.

The point is: I hope that helps you appreciate the need for you to provide an example Excel file with sufficient details.

 

[ShaunAldridge]

Sorry for not being clear, I'm trying to describe as best I can. Yes I'm changing what I'm saying the problem is because I've been looking at it all day and what seems to be the issue is changing.

I have got a URL from google drive, hopefully you're able to download it in Excel and not google sheets. Let me know if it doesnt work. Thanks

Sum issue.xlsx

drive.google.com

 

[curiouscat408]

Thanks for the upload. I'll look at it more closely.

But on first blush, it is not good enough. You might need to choose a different file-sharing website; or upload to the google drive differently. I suspect that google modified the file.

When I open the URL, both sums __appear__ to be the same, namely 8759.52.

And when I download to Excel (2010), both sums __appear__ to be 8759.51999999999.

In other words, there is no __apparent__ difference. And in fact, in Excel, MATCH(B3,C3,0) returns 1, which indicates that there is indeed no binary difference.

(And the binary representation of all values in B5:B169 and C5:C169 are exact for the displayed values. In contrast, the values that __appear__ to be 8579.51999999999 are actually 8759.51999999999 + 5.45696821063757E-12. No surprise; just want to make a point: things are not always as they appear, due to arbitrary formatting limitations in Excel.)

 

[curiouscat408]

Yes I'm changing what I'm saying the problem is because I've been looking at it all day and what seems to be the issue is changing.

No problem. It is quite common that our understanding of a problem changes over time as we work the problem.

But just to be clear: are you saying that you can "simplify the conditions" that __appear__ to demonstrate the "same" problem?

Or are you saying that the original description was incorrect in some details?

I would prefer to explain the original problem, as I understand it, to wit: initially, the sum of a set of values __is__ (not merely displays) 8759.52, but the sum __becomes__ (not merely displays) 8759.51999999999 after you only copy-and-paste the values.

That is very plausible. But the explanation might be different.

The point is: don't do us any "favors" by changing the circumstances that you think have the same result. There might be two different ways to get there.

 

[curiouscat408]

PS....

Thanks for the upload. [....] But on first blush, it is not good enough.

Nevertheless, it might demonstrate some mistakes that you are making.

Column B contains constants that have no more than 2 decimal places. Column C contains formulas of the form =ROUND(B5,2).

It is __not_necessary__, not useful, to explicitly round __contants__ that are already rounded correctly, __unless__ those "constants" are actually the result of calculations that you copy-and-paste-value.

On the other hand, the sum of the rounded values in C3 is simply =SUM(C5:C169).

That is a __calculation__. So as we all explained previously, __that__ is where you must __explicitly__ rounded, to wit: =ROUND(SUM(C5:C169), 2).

Formatting to display just 2 decimal places is not sufficient.

(Unless you set the Precision As Displayed option and properly format the cell as something other than General. But I discourage the use of that option for many reasons.)