# Formula calculating invisible decimals

1. ## Formula calculating invisible decimals

Hi i have downloaded data in cells A1 A2 A3 A4 .
9669.86
9669.62
9669.62
9669.51

this formula in B1
=AND((A1-A2)>(A3-A4)).

in evaluate formula it is showing like this
=AND(0.23999999999782>0.110000000001582)
=TRUE.
how to remove this invisible decimal calculation?

actually it should calculate like this
=AND((9669.86-9669.62)>(9669.62-9669.51))
=AND(0.24>0.11)
=TRUE.  Register To Reply

2. ## Re: Formula calculating invisible decimals

Floating point arithmetic errors.  Register To Reply

3. ## Re: Formula calculating invisible decimals

=AND(ROUND(A1-A2,2)>ROUND(A3-A4,2)).
Does that work for you?  Register To Reply

4. ## Re: Formula calculating invisible decimals

Hi Olly i have read your link...still clue less what to do? iam bad at maths... i need to format the data ?
previously many times used this formula with same type of data but never faced such decimal calculations...why i am getting like this now..because of this sometime my calculations are showing TRUE instead of FALSE Hi ChemistB .. formula not working..  Register To Reply

5. ## Re: Formula calculating invisible decimals

Sigh,perhaps you could explain what you mean by "not working" See attached.  Register To Reply

6. ## Re: Formula calculating invisible decimals

sorry, will check this now properly  Register To Reply

7. ## Re: Formula calculating invisible decimals

working fine.. thank you. do i need to do this for every formula? can i format the data to avoid this decimals?  Register To Reply

8. ## Re: Formula calculating invisible decimals

You can set the workbook to do "Precision as displayed" That means all calculations will only use what is formatted on the sheet.
You will permanently lose any values you enter greater than the formatted amount.
For example, if you have a cell formatted as 2 decimals and you enter 1.749, it will instantly convert it to 1.75. If you convert it back out of "precision as displayed" it will remain 1.75  Register To Reply

9. ## Re: Formula calculating invisible decimals

will check this thoroughly thank you.  Register To Reply

10. ## Re: Formula calculating invisible decimals Originally Posted by rajre Hi i have downloaded data in cells A1 A2 A3 A4 .
9669.86
9669.62
9669.62
9669.51
this formula in B1
=AND((A1-A2)>(A3-A4)).
in evaluate formula it is showing like this
=AND(0.23999999999782>0.110000000001582)
=TRUE.
how to remove this invisible decimal calculation?
Setting "Precision as displayed" (PAD) will not remedy the problem with the formula exactly as shown.

PAD only ensures that the final value in a cell is rounded to the precision indicated by the cell format.

So, for example, if B2 is =A1-A2 and B3 is =A3-A4, both formatted with 2 decimal places, =(B2>B3) will behave as you might expect based on the displayed values.

But =(A1-A2>A3-A4) might still continue to surprise you due to the increased precision and anomalies of 64-bit binary floating-point arithmetic.

(Note: The use of AND is superfluous in this context.)  Register To Reply

11. ## Re: Formula calculating invisible decimals Originally Posted by rajre Hi i have downloaded data in cells A1 A2 A3 A4 .
9669.86
9669.62
9669.62
9669.51
this formula in B1
=AND((A1-A2)>(A3-A4)).
in evaluate formula it is showing like this
=AND(0.23999999999782>0.110000000001582)
[....]
how to remove this invisible decimal calculation?
PS.... Why do you care if =(A1-A2>A3-A4) appears differently than expected when using Evaluate Formula?

The result of the comparison is what we should expect, given those values in A1, A2, A3 and A4.

Perhaps you simply chose a poor example. Certainly there are combinations of numbers where =(A1-A2>A3-A4) has surprising results. For example:

A1: 9669.28
A2: 9669.17
A3: 967.28
A4: 967.17

The formula =(A1-A2>A3-A4) returns TRUE(!) because A1-A2 is 0.110000000000582 and A3-A4 is 0.110000000000014 (approximately).

(The actual subexpression values have even more precision than Excel is willing to format.)  Register To Reply

12. ## Re: Formula calculating invisible decimals

With simplistic arithmetic expressions, one would expect differences only at the 15th decimal place so as long as we round each side of the > before comparing, we should not have any issues rounding to 2 decimal places.  Register To Reply

13. ## Re: Formula calculating invisible decimals Originally Posted by joeu2004 A1: 9669.28
A2: 9669.17
A3: 967.28
A4: 967.17
The formula =(A1-A2>A3-A4) returns TRUE(!) because A1-A2 is 0.110000000000582 and A3-A4 is 0.110000000000014 (approximately). Originally Posted by ChemistB With simplistic arithmetic expressions, one would expect differences only at the 15th decimal place
The example I provided demonstrates the fallacy of that assertion. The difference is in the 13th significant digit.

In fact, where the difference occurs depends on the magnitude of the numbers. For example, =70368744177663.1-70368744177663 is 0.1015625, a difference in the 3rd significant digit. Originally Posted by ChemistB so as long as we round each side of the > before comparing, we should not have any issues rounding to 2 decimal places.
That is, explicit rounding. I agree.

I took expection to the (incorrect) suggestion that setting "Precision as displayed" (PAD) would remedy the problem of comparing subexpressions, e.g. =(A1-A2>A3-A4). It does not.

@rajre: With my example, you might notice that =A1-A2-A3+A4 is exactly zero as you would expect, but =(A1-A2-A3+A4>0) is TRUE(!).

This is due to a dubious Excel heuristic that is poorly described under the title "... close to zero" in support.microsoft.com/kb/78113. Sometimes, Excel treats values as equal when they are "close enough". The problem is: the criteria for "close enough" are not defined, and the heuristic is applied inconsistently.  Register To Reply

14. ## Re: Formula calculating invisible decimals

Yes, on consideration, the use of precision as displayed would not fix the problem unless you broke the formula up into two cells (A1-A2) and (A3-A4) and then compared (thus doing the same thing as rounding).  Register To Reply