# 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.

2. ## Re: Formula calculating invisible decimals

Floating point arithmetic errors.

3. ## Re: Formula calculating invisible decimals

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

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..

5. ## Re: Formula calculating invisible decimals

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

6. ## Re: Formula calculating invisible decimals

sorry, will check this now properly

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?

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.
File>Options>Advanced>"When calculating this workbook"
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

9. ## Re: Formula calculating invisible decimals

will check this thoroughly thank you.

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.)

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.)

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.

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.

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).

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1