Different Totals For Same Data

Hi All,

I observed a very strange problem which has got my head spinning.

I have a Column of numerical data (45000 Rows).

When in a cell I sum all the Values of the Column the answer is slight different when the Data is arranged Smallest to Largest Vs Largest to smallest.

Anyone know why would such a thing happen?

Attaching the file for reference.

Possibly a manifestation of the well-known floating point error in Excel: https://support.microsoft.com/en-us/...sults-in-excel

Hi @AliGW,

Thank you for the reply.

But why would it give different results when Sorted Smallest to Largest Vs Largest To Smallest?

The numbers are exactly the same. If the whole dataset resulted in incorrect summation when Sorted Smallest to Largest aswell as Largest To Smallest then it was understandable.

@ NeedForExcel,

That is very strange! I get the same behavior when rounding all the values! This is simple addition. I am using Office 2013. Are you getting this with all 2007:2016 versions?

But why would it give different results when Sorted Smallest to Largest Vs Largest To Smallest?

I am going to go with AliGW -- possibly floating point error. With floating point errors, the order of summation can make a difference. The classic example of this behavior is something like sum(for n=1 to infinity)(1/n) or sum(for n=infinity to 1)(1/n). Perform this calculation in a hypothetical 4 digit computer, and you can readily see that you will get a different result for adding large to small versus adding small to large.

@ MrShorty

OK. I can follow that. There is a division operation in that model. I don't see how that factors here. These are straight sums. Even with rounding the behavior is the same.

What am I missing? This is rather disturbing. What could be the remedy?

FlameRetired, you don't say how you used the ROUND() function in doing the sums. The thing to remember is that the ROUND() function can only mask, but not eliminate the conversion to binary. When a computer must add 0.01 to anything, it must approximate that terminating decimal number with a non-terminating binary number. At some level, you lose precision whenever you add a small fraction to a larger number (whether that fraction is hand entered or the result of a division operation). The result is a floating point error.

What remedies are there? The sum(for n=1 to infinity)(1/n) exercise is used to teach and emphasize the importance of always adding from small to large.

Another remedy, specific to the OP's case, is to completely get rid of fractions and perform the sum as integers, because integers up to 15 digits are represented exactly when converted to double precision binary. As I understand it, this is the basis of the "currency" data types available in VBA and other programming languages. The OP's spreadsheet could have a helper column =ROUND(A2*100,0) that would make each entry an integer, then sum this helper column. If needed at the end, divide by 100.

Originally Posted by FlameRetired

@ NeedForExcel,

That is very strange! I get the same behavior when rounding all the values! This is simple addition. I am using Office 2013. Are you getting this with all 2007:2016 versions?

Hi @FlameRetired,

Thank you for taking out time to look into the problem..

Currently, I have tried it on 2013 & 2016 and it does have the same issue on both versions.. Don't have 2007 & 2010 anymore. Sorry, I haven't updated that in my profile..

Originally Posted by MrShorty

The OP's spreadsheet could have a helper column =ROUND(A2*100,0) that would make each entry an integer, then sum this helper column. If needed at the end, divide by 100.

Hi,

I will give this a try to see if this works.

Thank you for the reply..

@ MrShorty,

Thank you for taking the time to explain this ... and suggesting a remedy.

You are correct. I failed to mention how I rounded. I did not do it the way you suggested, but in the next column rounded all of column A values. You explained why that doesn't work. I never knew that.

@ NeedForExcel,

MrShorty's solution works at my end. No problems.

Thank you all for all the inputs..

Marking the thread solved as of now..

Reps added for the valuable inputs..

You are welcome. Thank you for the feedback and rep.