Sum of a column changes when sorted???

[ctblalock]

I have a quantity of values listed in one column. The values range from 0.0018 to 22,620.0000
The last cell (A3859) has the formula =SUM(A1:A3858).
Changing the order of the values (ex: ascending to descending) causes the sum of the values to change
(ex: 973,867.904500000 to 973,867.904499998).
WHY?

Because I am talking about only one column of data and changes in the fourth decimal place, this may seem unimportant. However, this example is taken out of a very large analysis that I am doing which involves using "=EXACT" and conditional formatting. These are compromised by the fractional changes in sum.

Thanks,
ctb

 

[plettieri]

Hi:

Sounds strange! Changing the order of the cells being summed should not change the results....do have any cells of the interest group using round, special cell formats, or are the cells being summed reliant on other cells?

plettieri

 

[Oaktree]

Hmm. It's possible this is the outcome of floating point arithmetic errors:

http://support.microsoft.com/kb/214118/en-us

 

[ctblalock]

...do have any cells of the interest group using round, special cell formats, or are the cells being summed reliant on other cells?
plettieri

plettieri,

No re rounding.
No re links to other cells or summed from other cells.
Regarding special cell formats, not that I know of but because I had imported the data into Excel from our MRP system (csv format) and, just to make sure, I selected the entire worksheet, format, cells, number tab, number with two decimals points. The result did not prevent the sum from changing when sorted.

I read the article that Oaktree referred to and because the data was imported I thought that may be the culprit. HOWEVER, using the =ROUND workaround actually exacerbated the problem (even after using copy, paste special, values).

Any additional thoughts or suggestions for avoiding the problem are appreciated.

ctb

 

[plettieri]

Hi:


I am running out of ideas but...

You might try (in a helper cells ie Column B) verifying the cells of interest are in fact values by multiplying by them by 1., then sorting (resorting)and summing that range of cells.....does the total then still differ?

plettieri

 

[ctblalock]

plettieri,

i did as you suggested. that seems to have fixed it. the sum no longer changes no matter which way i sort it. any additional explanation as to what was going on? thanks very much.

ctb

 

[plettieri]

Hi ctblaock:


Sorry, I can't expain that ....My last effort to you was to be sure you were actually summing cells that contained values. When you created the helper column were there any differences bewteen the A and B columns?

Maybe one of the resident gurus can offer something concrete...

plettieri