Sum of a column changes when sorted???

ctblalock

New Member
Joined
Oct 19, 2006
Messages
3
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

ctblalock

New Member
Joined
Oct 19, 2006
Messages
3
...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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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

New Member
Joined
Oct 19, 2006
Messages
3
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,141,628
Messages
5,707,503
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top