Formula Result - Strange!!

[tlc53]

Hi there,

I have a basic formula of F230 - F232 = F234

Pasting the values of each cell returns the following results;

+ F230 421,028.9
- F232 396,028.9
= F234 24,999.9999999999


Why on earth is it giving the result 24,999.999999999 instead of 25,000 ???

I've tried changing all the number formats but that's not doing it. Completely stumped!

 

[xenou]

Number formatting should work for this. Are you saying you see 24999.99999999 in F234 or in the celll where you paste values?

 

[xenou]

Here is the basic explanation of this type of problem:
https://www.excel-easy.com/examples/floating-point-errors.html

Here is a more in depth one:
https://www.excel-easy.com/examples/floating-point-errors.html

I recommend using round() if the precision is a problem. Otherwise in almost all cases you don't need to worry since 24999.9999999999 is only billionths millionths different from 25000.

I would guess in your case one of your numbers has a tiny fractional difference in it that is affecting the result and causing what you are seeing here. This is not a bug - it is a limitation of how floating point values are stored when following the IEEE 754 standard (as is often the case with computer applications).

 

[tlc53]

The cell F234 visually shows 25,000.00

but in the cell next to it G234, I have a formula which reads F234 - 25,000 and it keeps returning the result (0.00) instead of just 0. I have conditional formatting assigned to this cell and it's not working because it relies on the cell being 0.

After investigation, when I copied and pasted values of F234, I found it was returning 24,999.9999999999.

 

[xenou]

Okay - that's sounds like it makes sense then. See above - for a case where you rely on precise results because you are evaluating an equality expression, you should use round() to be sure you get the correct results. You can even round to more than 2 decimal places if you want to ... the error in the precision is very very small.

 

[tlc53]

I still can't understand why it is happening because it sums cells which are all manually entered, and all entries are either zero or to two decimal places. I even re-keyed in everything again and made sure all blank cells were 0. I just don't see where the tiny fractional difference would come from.

However, your round() idea has fixed my problem. Thank you!!

 

[joeu2004]

I don't believe you are telling us the entire story. When I enter 421028.9 and 396028.9 into A1 and A2 and calculate =A1-A2 in A3, the result is indeed 25000 exactly. That is, =MATCH(25000,A3,0) returns 1, indicating an exact match.

My guess is: the values in F230 are F232 not constants, but the result of calculations instead. I suspect that =MATCH(421028.9,F230,0) and/or =MATCH(396028.9,F232,0) returns #N/A, indicating an infinitesimal difference.

You might see the difference if you temporarily format F230 and F232 to display 11 decimal places (for a total of 15 significant digits).

But sometimes we cannot see the infinitesimal difference because Excel formats only the first 15 significant digits, rounded. For example, if F230 is indeed exactly 421028.9 (MATCH returns 1), F232 might differ from 396028.9 by as little as 5.82E-11 with no visible indication.

(The fact that =F234-25000 appears to be (0.00), which is an infinitesimally negative value in Accounting format, suggests that F234 is infinitesimally less than 25000.)

You can see the infinitesimal differences, if any, with formulas of the following form, formatted as Scientific:
=F230-TEXT(F230,"0.00000000000000E+0")-0 .

That is 14 zeros after the decimal point. The redundant -0 is sometimes necessary to avoid a "correction" that Excel applies, forcing the true arithmetic result to exactly zero when it is "close".

The reason for these infinitesimal arithmetic "errors" (differences from expectations) has already been alluded to, to wit: Excel uses 64-bit binary floating-point to represent numbers and perform arithmetic, and most decimal fractions cannot be represented exactly in that binary form. Moreover, the approximation of a particular decimal fraction might vary with the magnitude of the integer part.

For example, IF(10.01 - 10 = 0.01, TRUE) returns FALSE (!). But IF(ROUND(10.01 - 10, 2) = 0.01, TRUE) returns TRUE, as expected.

The upshot is: not only should you use ROUND in F234, but for any other calculation that should be accurate to a specific number of decimal places, notably in F230 and F232.

 

[joeu2004]

Pasting the values of each cell returns the following results;
+ F230 421,028.9
- F232 396,028.9

My guess is: the values in F230 are F232 not constants, but the result of calculations instead.

That got mangled due to improper editing. I meant: ``the values in F230 and F232 are not constants``.

And perhaps I need to clarify.... Obviously, they are constants after you paste-value. But I am guessing that the original values that you copied are the result of calculations.

Usually, when we copy a calculated value, the pasted value retains the binary representation of the calculation.

Ironically, if you go to each of F230 and F232 and press f2, then Enter, the problem might have corrected itself.

The reason is: by pressing Enter, we "re-enter" the decimal value as it appears in the Formula Bar, which might have a different binary representation.

 

[tlc53]

I tried viewing 20+ decimal places for F230 and F232 but there doesn't seem to be anything amiss.

Is there a way I can attach a spreadsheet so you can view it?

 

[tlc53]

F2 didn't correct it either..