• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

column sum changes on data sorting

Hi All,

Can anyone help me on this.my sum of column L as per attached file keeps changing when I sort data on various columns,There is no formula in the file.Also when I draw pivot on it the sum turns upto to a different figure.

Please help.
 

Attachments

  • ASIA_CMH_PIVOT_ACTUAL_3603.zip
    576.1 KB · Views: 12
That's rather Bizarre ! ! !

It will need some more investigation
 
Last edited:
The two largest numbers in the Sum Amount column are being treated as text as they have more than 15 digits

upload_2016-10-28_16-15-40.png

Remove them and it all works ok

The fact that those two numbers add up to 9 Quadrillion and there is no way the other values can add uip to enough to offset those values
 
Hi ,

The fact about everything being OK when the 16-digit numbers are removed is correct.

But Excel does its calculations right even with the 16-digit numbers !

Entering a formula such as :

=SUM(LARGE($L$2:$L$10030,{1,2})) + SUM(SMALL($L$2:$L$10030,{1,2}))

and evaluating it after every kind of sort , shows Excel correctly identifying the 16-digit numbers , and doing the arithmetic correctly !

As Hui said , it is bizarre.

One reason can be that the 16th digit on the right is 0 ; if it had been a non-zero digit , there might have been erroneous results.

Narayan
 
Back
Top