Two Identicals Sum to a Different Answer

[tzw1378]

I have two identical columns of 383 rows. The rows contain unique numbers, somewhat random.

I first check to see that both columns are identical, row for row. I then do a sum formula on each column and to my dismay, the sum result is different between the two column.

I'm hopeful this is a quick answer. I'm happy to upload or email the data.

It's most odd and so again, I hope an answer is easy to find and I apologize if I'm missing something glaringly obvious.

Thank you in advance and Happy New Year.

 

[eduzs]

Where's the formulas?
Some small variation in decimal cases?

 

[Sulprobil]

This is a typical issue about rounding floating point numbers, I think.
A worksheet function approach to solve this:

Rounding Values to 100%

I extract data and pull in to graphs. Quite often, I have 99% or 101% with the data that I am assessing. How do I tell Excel to look at my values and round those that make the most sense so that when added together equal 100%. I spend so much time looking at these partial percentages...

www.mrexcel.com

 

[joeu2004]

I first check to see that both columns are identical, row for row. I then do a sum formula on each column and to my dismay, the sum result is different between the two column.

As someone once wrote: "if a picture is worth 1000 words, an Excel file is worth 1000 pictures". Translation: provide the Excel file in one form or another, not a screenshot. Usually, this forum's contributors prefer that we use XL2BB; it does show us the formulas. But if one theory is correct, the Excel file itself is needed for us to see the root cause.

Do both to appease both needs. Copy-and-paste using XL2BB. __And__ upload an example Excel file (redacted) to a file-sharing website, and post the public download URL. I like box.net/files; others like dropbox.com. In any case, test the download URL first, being careful to close all windows that share the same login as the file-sharing website.

-----

IMHO, the difference in the totals -- 135,587.79 v. 138,073.76 -- is too great to be explained by binary rounding issues. That is, the infinitesimal differences of each row (e.g. B3-C3), or by the infinitesimal difference that results from the sum (or any calculation) of values with decimal fractions.

(And since Excel returns TRUE when comparing the B3 and C3, for example, presumably they cannot differ by large amounts due to normal calculations, e.g. 440.45123 v. 440.44987.)

Instead, I suspect that in some rows, one value is numeric, and the other value is text.

If the formula in column D is of the form =B3-C3=0, the text is converted to a number. Thus, their difference would indeed be zero.

But the SUM function ignores the text values.

Looks can be deceiving. And the format of the cells do not matter.

To confirm, enter formulas of the form =ISNUMBER(B3) and =ISNUMBER(C3) in columns E and F. I suspect that you will see TRUE for one and FALSE for the other in some rows.

 

[joeu2004]

PS....

IMHO, the difference in the totals -- 135,587.79 v. 138,073.76 -- is too great to be explained by binary rounding issues. [....] Instead, I suspect that in some rows, one value is numeric, and the other value is text.

Of course, another plausible explanation is that the ranges for the two SUM formulas(?) are simply different, and/or one SUM or both include extraneous data that might be below the 383 rows that you intended to total.

Again, your failure to show formulas makes it difficult to provide dispositive explanations.

 

[Sulprobil]

I think joeu2004 is right about the difference being too high for a rounding issue.

 

[tzw1378]

Thank you to all who replied.

After some more investigation, I'm happy to close this post by sharing that this was user error.

The number formatting was fine. The columns of data were in fact different and it was again, a user error.

Thank you each.