Why Are These Not The Same

[will2learn]

I'm using a conditional format in order to highlight if two cells do not match. For some reason they are shown as errors even though they appear the same. I changed the cell format to numbers and have noticed that the numbers differ slightly.

Can anyone advise why this has happened?

Original Format
00:05 00:05
01:15 01:15
00:24 00:24
01:50 01:50

Format shown as numbers
0.003472222222222220000000000000 0.003472222222222320000000000000
0.052083333333333300000000000000 1.052083333333330000000000000000
0.016666666666666700000000000000 0.016666666666666600000000000000
0.076388888888888900000000000000 0.076388888888888800000000000000

 

[Norie]

It's because of the way computers store and do calcuations with floating point numbers.

If you typed all those values directly in the cells each pair should be equal but it one of them is a result of a calculation it might differ slightly.

I'm sure someone else can give a far better, more technical explanation.

 

[T. Valko]

It's because of the way computers store and do calcuations with floating point numbers.

If you typed all those values directly in the cells each pair should be equal but it one of them is a result of a calculation it might differ slightly.

I'm sure someone else can give a far better, more technical explanation.

Here's some related info from MS:

Understanding IEEE Floating Point Errors

http://support.microsoft.com/kb/42980

How to correct rounding errors in floating point arithmetic

http://support.microsoft.com/kb/214118