Floating-point arithmetic... (=SUM) doesn't work correctly for my basic decimal numbers

I will be crazy. Maybe I am very tired. I do not understand why my SUM formula is not working correctly.
Please help me

More details:
I believe my problem is very small. However, I do not know how to solve. I have a very large and complicated excel file, there are several formulas etc etc...One day, I noticed that my result are not correct. I check all the formulas, lines.. I could not find the reason...Then I decided to split the excel file by formula...Finally, I found out that the problem, as in the excel file in my first message.
I suppose that my formula (=SUM) result should be zero. However the result is not real zero...
I hope someone can tell me and show me a result.
Please check my attachmet, you will see the problem easily.

More information:
Let me make it easy
Please calculate your self in excel. My result is "-0.0000000000000222044604925031000". Why my result is not "zero"?

What is the SUM of these values;
49.350
-12.600
21.000
-0.025
-5.250
-6.300
-6.275
63.000
-12.600
-4.200
-10.500
-4.200
-6.300
-4.200
-2.100
-4.200
-6.300
-4.200
63.000
-4.200
-8.400
-4.200
-5.250
-12.600
-4.200
-4.200
-5.250
-3.150
-7.350
-4.200
-2.100
-4.200
-5.250
-4.200
-12.600
-2.100
-4.200
-5.250
-4.200

Hello mengince & Welcome to the Forum,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...

https://support.microsoft.com/en-gb/...sults-in-excel

Originally Posted by mengince

I do not want to use =ROUND.

Originally Posted by mengince

I tried MS Support "Method 2: Precision as displayed". It does not work for me.

It does work if you change the format of A40 to display 13 decimal places or less. To confirm, enter the formula =A40=0 into A41.

The option is called "Precision as displayed". (And really, it should be called "... as formatted" to cover the General format.)

So if you display too much precision, the option will not do the job that you require.

But the precision should be chosen based on need. Since your data has at most 3 decimal places, a format with 3 or fewer decimal places in A40 would be appropriate.

That said, I deprecate the use of PAD for many reasons. Among them are: (1) PAD applies to the entire workbook, not just selected cells; and (2) PAD applies only to the final value of a cell, not to sub-expressions.

It is important to make a backup copy of your Excel file before setting PAD.

The danger of #1 is: when you set PAD, it might permanently change constants anywhere in the workbook that you might have entered with more precision than you choose to display. This is not uncommon with interest rates, for example. We might enter the exact rate 1.23456789%, but display only 1.23%. When we set PAD, the actual rate becomes 1.23000000%. Even if we revert to disabling PAD, the rate remains 1.23000000%.

(The permanency of the change applies only to values that we enter, not to values derived by a formula.)

The impact of #2 is: even though A40 might become exactly zero when PAD is set and =A40=0 is TRUE, IF(SUM(A1:A39)=0,TRUE) results in FALSE because PAD does not apply to SUM(A1:A39) within the IF() expression. The only remedy is to round explicitly; to wit: IF(ROUND(SUM(A1:A39),3)=0,TRUE) .

In general, explicit rounding is the better solution, like it or not. But always round to the precision that you expect a calculation to be accurate to -- again, 3 decimal places for your example. Do not round to an arbitrary number of decimal places like 10, as many people suggest.

Apols. didn't see your post JB and hadn't considered the title TOO bad.

Try:
=ROUND(SUM($A$1:$A$39),2)

I htink you need to explain more clearly what you want.

Thanks for changing the title,
The problem was may be due to circular reference.
If not have to look in to file
Please attach a sample excel file, remove the sensitive data from the file

-0.0000000000000222
But the formula is giving the above results result in your attached file
which is not zero

so that you are getting
196.35 for >
-196.35 <
net looks like "zero" but it not zero

if you use the formula
=SUMIF(A1:A39,">0")=SUMIF(A1:A39,"<0")
It will give false

Originally Posted by nflsales

so that you are getting
196.35 for >
-196.35 <
net looks like "zero" but it not zero
if you use the formula
=SUMIF(A1:A39,">0")=SUMIF(A1:A39,"<0")
It will give false

Because 196.35 <> -196.35. (wink)

Note that =SUMIF(A1:A39,">0")=-SUMIF(A1:A39,"<0") does return TRUE.

The point you wanted to make is better demonstrated by the following....

=SUMIF(A1:A39,">0")+SUMIF(A1:A39,"<0") is exactly zero (0.00E+00 when formatted as Scientific).

But =SUMIF(A1:A39,">0")+SUMIF(A1:A39,"<0")=0 return FALSE, demonstrating that the calculation is not exactly zero.

The first formula is exactly zero only because Excel "makes it so" arbitrarily, because the last subtraction is "close enough" to zero.

Bottom line is that if you want to get excited about what is happening in the umpteenth decimal place you need to round you data. Excel can't count. End of....

May be The following is the reason

Calculation specifications and limits in excel

Feature Maximum limit

Number precision 15 digits


So that when you used =SUMIF(A1:A39,"<0") or =SUMIF(A1:A39,">0")
it is only taking left side 15 digits

Originally Posted by mengince

Let me make it easy[.] Please calculate your self in excel. My result is "-0.0000000000000222044604925031000". Why my result is not "zero"?

Most of the information in this thread is incorrect in detail. If Excel were limited to storing and calculating with a precision of 15 significant digits, we wouldn't have the problem in the first place, not with the simple example that "mengince" presents.

It is true that Excel limits data entry to the first 15 significant digits. Any digits to the right are replaced with zeros, effectively truncating the data entry.

And it is true that Excel formats (displays) cell values up ot the first 15 significant digits, rounding any digits to the right.

But usually, calculations are not limited to 15 significant digits. Instead, they are limited to the precision of 64-bit binary floating-point (actually 80-bit binary floating-point), which can be represented in decimal with very many more significant digits.

However, Excel plays games with calculations in some contexts, which results in inconsistent behaviors. For example, in some contexts, =A1-A2 might result in exact zero, but =A1-A2=0 might result in FALSE with the same values in A1 and A2.

In your original example, it is only a concidence (and due to the games that Excel plays) that the workaround =SUMIF($A$1:$A$39,">0")+SUMIF($A$1:$A$39,"<0") returns exactly zero (0.00E+00 when formatted as Scientific).

Note that =SUMIF($A$1:$A$39,">0")+SUMIF($A$1:$A$39,"<0")-0 returns about 5.68E-14 when formatted as Scientific. The redundant -0 is sufficient to change the context to which Excel applies its "game".

-----

To explain more accurately, let's look at why IF(10.12-10=0.12,TRUE) returns FALSE(!).

Excel uses 64-bit binary floating-point to represent numeric values. In binary, values are represented by a sum of 53 consecutive powers of 2 ("bits") times an exponential factor.

Consequently, most decimal fractions cannot be represented exactly.

Also, the approximation of a decimal fraction depends on the magnitude of the integer part because some of the 53 bits must be used to represent the integer part.

So:

0.12 = 0.119999999999999,99555910790149937383830547332763671875
10.12 = 10.1199999999999,992184029906638897955417633056640625
10.12-10 = 0.119999999999999,2184029906638897955417633056640625

(I use period for the decimal place and comma to demarcate the first 15 significant digits, which is all that Excel displays.)

We can see the infinitesimal difference between the constant 0.12 and the calculated 0.12 (10.12-10).

The Excel "=" operator compares two values rounded to 15 significant digits [1]. In this case, the binary representation of 0.12 rounds to 0.120000000000000, but the bnary representation of 10.12-10 rounds to 0.119999999999999. Since the rounded values are not equal, "=" returns FALSE.

[1] That is not true of some other comparisons in Excel, notably lookup functions. So in some contexts, =(A1=A2) might return TRUE, but MATCH(A1,A2,0) might return FALSE.

-----

Something similar is happening when we sum the numeric values in your examples.

If we change the order of the calculation, the result can vary because the relative magnitude of the integer parts changes the binary approximations of the decimal fractions.

But that is only coincidence. We should not depend on the order of calculation to remedy a binary arithmetic anomaly because the success depends on the individual values involved.

Back in post #3, Glenn posted a link to a Microsoft article that explains it.
https://support.microsoft.com/en-gb/...sults-in-excel

I don't fully understand it either, but the basic problem underneath is that a BINARY system (like your pc) cannot precisely represent many decimal values.
In the complicated algorhythm to deal with this problem, many values get rounded. This is the part I don't fully understand, which values get rounded when and why.

To complicate it further, every application (like Excel) handles this issue differently than others.
And even within Excel, some functions handle it differently than others (as you've discovered with Sum vs Sumif)

The generally accepted solution is to use ROUND.

If ROUNDING to your highest used decimal place isn't accurate enough, then Excel isn't the right tool.

As has already been explained, this is due to floating point error. Floating point error is an inherent part of the way computers do arithmetic, so this isn't specific to Excel. Some additional links discussing floating point error: https://www.excelforum.com/groups/ma...nd-errors.html

Because floating point errors are such a fundamental part of computer arithmetic, you must do something different if you want this addition to come to exactly 0. You have already rejected the most common suggestion -- use of the ROUND() function. Another possible solution is to fake a "scaled integer" currency data type in a helper column and use that (I proposed this solution for someone else here: https://www.excelforum.com/excel-gen...ml#post4784658 ).

If you are unwilling to make any changes to the spreadsheet, you could consider using a different programming language -- one where you can dim your variables as currency or similar scaled integer data type and perform the calculations that way.

One way or another, though, you have got to do something different. There is no "fix" for this, because this is a fundamental part of how computer's do arithmetic. You, the programmer, need to have a strategy to deal with it. In order for us to help you, we need to know what kinds of strategies you will consider using.

You have been given one. Round your data to the desired number of dps.

try
=SUM(A1:A39)+100-100
or
=SUM(A1:A39)+1000-1000
It is working for your example

Here are 3 options, only one of which works. It STILL involves rounding. that is INEVITABLE. Since I had no idea where your numbers came from, I made up my own set (of 38 numbers). Nineteen positive numbers and the same 19 numbers multiplied by -1. So I KNEW that the sum was zero.

Simply summing them, as we already knew (Option 1) doesn't work. Surprisingly (to me) Option 3 (use an array formula ro round them and then sum them) didn't work either. However, the rounding option that I have been suggesting does work:

=ROUND(SUM($A$3:$A$41),3)

If that isn't good enough for you, then I think you need to explain clearly why it does not suit.

I HAVE read your first post, several times.

Your formulae are NOT correct. If you do not round, you will always be subject to problems with floating point arithmetic.

You have been given a solution. It works. You don't want to use it. That is your choice.

Good luck, but you are trying to locate the end of a rainbow.