Mystery Zero - 0 becomes 7.105427357601E-15

[SidKol]

Hi,

Been trying to solve this for hours, but beats me and my colleagues.


Cell A1:

=2.4793*24

Cell A2:

59.5032

Cell A3:

=IF(A2-A1=0;TRUE;FALSE)

Outcome in A3: FALSE


2.4793*24 is EXACTLY equal to 59.5032.
So I do not understand why A3 returns FALSE.

When using "Evaluate Formula" Excel shows the value of A1 as 7.105427357601E-15 instead of 0

Why does this happen and how can this be corrected so the outcome will be correct ("TRUE")

Please note that I don't want to use ROUND.
4 decimal places is not excessive and it should be working fine without.




Bonus mystery if you add the additional:

Cell A4 =

=A2-A1

Cell A5 =

0

Cell A6 = =

=A4=A5

Outcome in A6 --> TRUE

So even though A2-A1 really is 0, it does not return a TRUE in A3.

 

[Kyle123]

It's a floating point issue (how computers often store numbers) have a look here: https://support.microsoft.com/en-gb/kb/78113

 

[steve the fish]

Its because of floating point arithmetic used by excel. You have to use rounding if you want to solve your problem.

 

[pjmorris]

You can also tick the Options/Advanced/'When Calculating this workbook' - Set Precision as Display checkbox, which will solve the equality problem and avoid adding Round functions everywhere (its the same thing). I've used this in numerous spreadsheets without problem, but worth checking that it doesn't inadvertently introduce problems due to the lack of accuracy.

 

[SidKol]

Thanks all for your time to reply.

I have read the page about floating point arithmic, and I think I understand the principle.
At least I understand it for numbers with large amounts of decimal places.

But i still do not see the answer to my question.
Or am I missing something and can this floating point arithmic issue even occur with just 4 decimal places?

(As 2.4793*24 is EXACTLY 59.5032 and still is perceived as 7.105427357601E-15)

 

[AliGW]

It can occur even with numbers to one decimal place, as I saw in a query on another forum only this morning. ROUND will fix it, but you say you don't want to use that.

 

[Rick Rothstein]

Or am I missing something and can this floating point arithmic issue even occur with just 4 decimal places?

(As 2.4793*24 is EXACTLY 59.5032 and still is perceived as 7.105427357601E-15)

The problem is while 2.3793 has an exact representation in Base 10 (what our number system is based on), it does not have an exact representation in Base 2 (what the computer number system is based on), so in order to fit the Base 2 value into the limited memory space available for floating point numbers, some precision is lost when the longer Base 2 number is shoved into the limited memory space. That loss of precision is then magnified by the multiplication by 24. Think of is like this... in Base 10, 2 divided by 3 is 0.66666... with an infinite amount of 6 following. When you do that calculation on a calculator with, say, an 8 digit display, it shows either 0.6666666 or 0.6666667 depending on if it rounds to fit or not; however, neither of those numbers are the correct value and so calculation done on that calculator will not display an exactly correct value. This is the same problem you are seeing except in Base 2.

 

[SidKol]

Thanks Ali and Rick.
When it's necessary I will use the ROUND anyway
(Actually have integrated ROUND already in my workbook, but still am obsessed with the How part of this)

I'm still surprised this can happen though.
Especially given the result of the formula in row 7 underneath.
I do not feel it aligns with Rick's comment that 2.4793 has no exact representation in Base 2.
If that is correct I would suspect that the outcome in row 7 would be a FALSE as well.

In row 4 the result is FALSE when asked if B3-B2 is 0
The same question "B3-B2" results in 0 in row 5
When double checking in row 7 if this zero is equal to just the entry 0 it states TRUE

So if the theory with floating points is correct and applies here, then why does it only seem to give an issue in row 4 and not in row 7?
Should this not give a problem in both?

(screenshot underneath shows in column A the exact cell content of the next cell in column B)

 

[SidKol]

It even gives a difference in TRUE and FALSE when changing part of the formula in row 4 to "If B3-B2=B6"

As B5 = (B3-B2)
Why does B5=B6 give a TRUE

 

[Rick Rothstein]

I do not feel it aligns with Rick's comment that 2.4793 has no exact representation in Base 2.
If that is correct I would suspect that the outcome in row 7 would be a FALSE as well.

I think the problem is that 59.5032 does not have an exact Base 2 representation either. The full-out Base 2 digits for 2.4793 multiplied by 24 are different than the full-out Base 2 digits for 59.5032 (Excel calculates internally to a much higher bit-count than is needed to display values in a cell. What I think is that to the precision Excel can display a value to in a cell, the rounded values for those to numbers are the same, hence, Row 7 displays TRUE; however, when subtracted (using the higher bit-count that Excel uses internally), the difference between the two values are not exactly zero. The problem I think you are having is in believing what Excel shows you in a cell is what it uses for its internal calculations... it isn't... internally, Excel is calculating to a much higher precision and then rounding the values to the limits of its number display capabilities.