• 0

Excel won't except ".05721". Floating point issue?


Question

This just came across my helpdesk and work and our IT group is stumped.

When a user enters 20000.05721 it changes to 20000.0572099999 in the formula bar.

In our testing we've found that is is always true if the value is between 16384.05721 and 65535.05721 (as long as value to the right of the decimal remains ?05721?).

We've tried Excel 2007 and 2010 and I've tried changing the field type to Number and Scientific with no change. I did find that adding a " ' " mark works though.

Link to comment
Share on other sites

9 answers to this question

Recommended Posts

  • 0

There was a bug in Excel 2007 (before SP1) that caused number close to 65535 and 65536 to show incorrect values.

I have just tested in 2007 (SP2) and the underlying data is still correct even though the number displayed on screen is wrong.

This seems to be a display issue - very similar to the issue uncovered when 2007 was first released!

Testing in VBA using activecell.value still shows "20000.05721" when I enter this as the cells value - even though the display shows "20000.0572099999".

If I enter "20000.0572099999" as the cell value then this is returned in VBA.

It is working fine in Excel 2003 - this probably doesn't help you much though!

I recommend that you take this up with MS directly - they may already be aware of the issue.

Please keep me updated here though!

Link to comment
Share on other sites

  • 0

"It is working fine in Excel 2003 - this probably doesn't help you much though!"

I just tried on our excel 2003 here, and got the problem

post-14624-0-12860100-1343157700.jpg

.05720 and .05722 are fine but if you type in that 20000.05721 number it changes it to the 0999999

Strange.

Link to comment
Share on other sites

  • 0
"It is working fine in Excel 2003 - this probably doesn't help you much though!" I just tried on our excel 2003 here, and got the problem post-14624-0-12860100-1343157700.jpg .05720 and .05722 are fine but if you type in that 20000.05721 number it changes it to the 0999999 Strange.

Yes, your right - I just tested again, I must've typed in the wrong number yesterday as I am getting different behaviour to yesterday.

Sorry!

Link to comment
Share on other sites

  • 0

Looks like you just found a 'serious' bug in Excel, especially since it's been present for probably almost 10 years now!

Link to comment
Share on other sites

  • 0

Anyone know where I could submit it to get it on Microsoft's radar?

Wouldn't say it's serious as we've only found one decimal case where it happens... though the whole number range is pretty big (16k-65k)

Link to comment
Share on other sites

This topic is now closed to further replies.