Excel floating point representation - two 6s are not the same!

[j66_europe]

I'm really struggling when trying to understand how Excel deals with number representation. It would appear that (1.2 / 0.2) and (12 / 2) are NOT the same. Although both results appear as '6' in the formula bar, I noticed that in a pivot table, Excel 2013 was not summarising both values. Instead, the '6' appeared twice in the pivot results.

Bizarrely, the following formula returns true:

=IF((1.2/0.2)=(12/2),TRUE,FALSE)

However, clearly the two 6s can't be equivalent if the pivot table is not aggregating them.

I then experimented with dec2bin and dec2hex:
=DEC2BIN(1.2/0.2) yields 101 which is 5, yet...
=DEC2BIN(12/2) yields 110 which is 6

So what's going on here? How can I check how Excel is storing my numbers, because when it displays both as 6, and when my if function suggests the values are the same, how can they be? Is this expected behaviour? Thanks

 

[wideboydixon]

See here:

https://support.microsoft.com/en-gb/kb/78113

Perhaps you could use ROUND?

(1.2/0.2) = (12/2) -> FALSE
INT(1.2/0.2) = 5
ROUND(1.2/0.2) = 6
ROUND(1.2/0.2) = ROUND(12/2) -> TRUE

WBD

 

[j66_europe]

Thanks. But (1.2/0.2) = (12/2) -> TRUE, not FALSE

And INT(1.2/0.2) = 6 not 5!

Hence my confusion.

 

[wideboydixon]

Interesting. Not on my PC they aren't! In the immediate window:

? (1.2/0.2) = (12/2)
False
? INT(1.2/0.2) = INT(12/2)
False
? INT(1.2/0.2)
 5 
? INT(12/2)
 6 
? ROUND(1.2/0.2) = ROUND(12/2)
True

WBD

 

[j66_europe]

Fascinating! Yes, in my VBA immediate window,

?INT(1.2/0.2)
 5

But in the spreadsheet, =INT(1.2/0.2) -> 6.

Can anyone explain what's going on? Thanks

 

[j66_europe]

I got a good explanation here, and am pasting the link in case anyone else finds this information useful.

https://social.technet.microsoft.co...n-spreadsheet-vs-immediate-window?forum=excel