Weird Excel issue

[RizzoTheRat]

I've just found a weird thing in Excel 2016, that also happens on my colleagues' machines using the same version. I'd be interested to see if people have the same thing on other versions.

In a cell type 0.08
in the cell below type = <cell above> + 1
In the next cell down type = <call above> - 1
In the next cell down type = <first cell> = <third cell>
In the next cell down type = <first cell> - <third cell>


I get:

0.08
1.08
0.08
FALSE
0

It seems to think the numbers are not equal but gets exactly zero when subtracting.
The third cell displays as 0.0800000000000001 if I increase the number of decimal places, so I assume it's something to do with the level of precision on real numbers, maybe a 32 bit issue?

It only seems to happen between 0.051 and 0.099

Edited by RizzoTheRat on Thursday 4th November 12:06

[paulrockliffe]

Didn't you post exactly this a month or so ago?

[RVB]

RizzoTheRat said:

I've just found a weird thing in Excel 2016, that also happens on my colleagues' machines using the same version. I'd be interested to see if people have the same thing on other versions.

In a cell type 0.08
in the cell below type = <cell above> + 1
In the next cell down type = <call above> - 1
In the next cell down type = <first cell> = <third cell>
In the next cell down type = <first cell> - <third cell>


I get:

0.08
1.08
0.08
FALSE
0

It seems to think the numbers are not equal but gets exactly zero when subtracting.
The third cell displays as 0.0800000000000001 if I increase the number of decimal places, so I assume it's something to do with the level of precision on real numbers, maybe a 32 bit issue?

It only seems to happen between 0.051 and 0.099

Edited by RizzoTheRat on Thursday 4th November 12:06

My Office 2021 gives the same as your version.

I opened a blank worksheet and started with 0.08 in cell A1:
0.08
=A1+1
=A2-1
=A1=A3
=A1-A3

The cells displayed:
0.08
1.08
0.0800000000000001
FALSE
0

[bigandclever]

Excel is accurate to 15 significant figures; always has been, probably always will be. Bigger than that it can get a bit screwy.

ETA There's plenty of explanatory literature out there, but this is as good as any for a not-too-involved overview .. https://docs.microsoft.com/en-us/office/troublesho...

Long and short; it's not a bug and it's not unexpectedly weird; it's how the product's been built.

Edited by bigandclever on Thursday 4th November 14:22

[Doofus]

paulrockliffe said:

Didn't you post exactly this a month or so ago?

No, that was me.

https://www.pistonheads.com/gassing/topic.asp?h=0&...

[Beetnik]

That's one more good reason for using the 'ROUND' function.

[CharlesElliott]

Computer scientists know that comparing floating point numbers with '=' is not a good way to do it......

[plasticpig]

Excel 2019



Office 365 Web:



Office 2010

[bigandclever]

Increase decimals on any of your A3 cells .. they'll all be 0.0800000000000001 (well, yours won't because you've done different maths to the OP )

Edited by bigandclever on Thursday 4th November 14:49

[RVB]

Just tried it on Office 2016 and get the same result as my earlier try with Office 2021 (mentioned a few posts up):

I opened a blank worksheet and started with 0.08 in cell A1:
0.08
=A1+1
=A2-1
=A1=A3
=A1-A3

The cells displayed:
0.08
1.08
0.0800000000000001
FALSE
0

[CharlesElliott]

This is how floating point works, sorry.

https://en.wikipedia.org/wiki/Numeric_precision_in...

[RizzoTheRat]

CharlesElliott said:

Computer scientists know that comparing floating point numbers with '=' is not a good way to do it......

Surely computer scientists know not to use Excel?

I was actually using conditional formatting to highlight were 2 data sets were different. It surprised me as with the "general" format it shows 0.08, I'd have expected it to either show more decimal places when formatted as general, or to have internally rounded it, but it appears the general format only goes up to 9 decimal places, ie 0.080000001 displays the full length but 0.0800000001 displays as 0.08

Some potentially interesting links above, I'll have a read later, cheers.