Excel 2003 - sum limitations?? 1

[AlexTardif]

Hi,

I got a list of over 1400 amounts being between -500 000 000,00 and 500 000 000,00. The cells are in number format.

I wanna do a sum of those amounts. And I already know I should get an answer of zero. But Excel gives me this answer : -0,0000373721122741699.

In fact, if I do a sum on the first half of the list (which gives me a positive result : 25 769 600 833,56) and a sum on the first half of the list (which gives me a negative result : -25 769 600 833,56) and when I do a sum of those two, I get a perfect zero.

So, is there any limitations of the sum() formula? Or any idea of the problem?

Here's a sample of the list (there are 6 digits just to show the zeros after the "cents") :

22 184 935,980000
31 322 402,510000
9 249 032,150000
5 539 964,190000
-23 167 000,000000
1 433 478,350000
4 975 962,740000
-62 416 680,000000
-16 878 352,000000

Thanks!

Alex

 

[SkipVought]

hi,

It is not at all clear what you are summing.

You say "which gives me a positive result : 25 769 600 833,56" but your "result" is 4 separate values.

Please provide your formula.


You do realize that floating point arithmetic can intoduce errors of precision, don't you?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AlexTardif]

No, I don't know anything about those errors... I thought Excel was precise enough.

For the results I displayed, I just added a space between the thousands to be more clear. The results are : 25769600833,56 and -25769600833,56.

The formula : =sum(F6:F1431)

The sums of the two halves were just a test to know if Excel had any problem to sum those numbers.

Thanks for your answer.

 

[SkipVought]

Spaces do not add clarity: in this case they add CONFUSION!

I would not be loosing any sleep over 4 hundredthousandths.

As a fellow programmer you ought to realize...

http://blogs.office.com/b/microsoft...es-excel-give-me-seemingly-wrong-answers.aspx

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AlexTardif]

Since I normally code in VB, C, C#, I never encountered such errors. (And I don't have to sum such numbers either)

But, now that I know this, I'll use the round() formula more often, for sure!

Thanks a lot for the link!

 

[SkipVought]

http://cplus.about.com/od/learning1/ss/clessonone_6.htm

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hmlhml]

Enlarging on Skip's diagnosis: You are summing numbers with 11 significant digits. By the time you've summed 1000 of them, the result may have up to 14 significant digits, and is likely to be three orders of magnitude bigger than the 1001st value to add. This value is stored to 15 decimal places, but in aligning it to the current sum ready for addition, it has to be slid sideways losing 3 of them, so you're down to 12 decimal places to hold an 11-decimal place number. (Of course decimal places are approximate because it's actually happening in binary)

Both in the storage of the result, and in the handling of the things you are adding, you are dangerously close to losing a significant digit.

I have a subsidiary question though! Am I right in thinking that Excel's currency format is just a cosmetic format applied to an underlying floating point number, in contrast to Access's currency format, which is a fixed decimal format designed to avoid rounding errors?

 

[GlennUK]

Am I right in thinking that Excel's currency format is just a cosmetic format applied to an underlying floating point number, in contrast to Access's currency format, which is a fixed decimal format designed to avoid rounding errors?

Yes, you are exactly right.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

There needs to be a distinction between 1) the Currency DATA TYPE in Access, which, by the way, one can FORMAT as General, Percent, or Scientific, for instance, and 2) the Currency FORMAT in Excel.

Currency Monetary data Stores data as 8-byte numbers with precision to four decimal places. Use this data type to store financial data and when you don't want Access to round values

As stated above, Excel's Currency Format is just that: a FORMAT.

By applying a FORMAT to a number, NOTHING CHANGES IN THE UNDERLYING NUMBER.

-0,0000373721122741699 might DISPLAY as $0.00, but look at the value IN THE FORMULA BAR!

Changing the FORMAT, changes NOTHING!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Throughout this thread, people seem to have been using the thousands separator, i.e. "," as a decimal point, i.e. ".". Why?

This seems like a very confusing thing to do. Am I missing something?

Tony

 

[Loomah]

tony
using "," as the decimal point is quite common in continental europe (and so possibly elsewhere) so it may well depend on where alex is from or the nationality of his company.

unlikely to be more than that (but it might be!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[hmlhml]

Tony and Loomah, this is exactly why I (almost) never use a fixed decimal format with three digits after the decimal point. I will use 1, 2, 4 etc., but not a multiple of 3. Multiples of 3 make too much confusion when data are viewed by people from different countries.

Skip, you're right I should not have referred to Access's currency data-type as a "format". The distinction you make is exactly the distinction I wanted to get clear in my head.