Deduction in Excel 2007 gives incorrect value behind decimal

The only real alternative to using the ROUND function is to switch on "Set precision as displayed" (Options > Advanced > When calculating this workbook)

Looks like you're stuck with using ROUND then!

The only other option that springs to mind is a bit of code to reduce near zero values to actual zeros - something like this:

Please Login or Register  to view this content.

Originally Posted by Ronin1981

Call it lazy, but probably I will go with ROUND in cases when I notice the issue.

That's exactly what that code I posted does for you - it monitors changes in your worksheet, and if a value of less than 0.000001 is returned, it changes the formula to round to zero. You simply have to paste that code into the worksheet module, once. That's it.

Originally Posted by Ronin1981

Ok, I see that it does the trick with formula results, but it doesn't affect values that are not formula. That is good.

Almost - it converts values < 0.000001 to 0, but changes formulas resulting in <0.000001 to a round formula, preserving the original formula, keeping the worksheet dynamic.

Originally Posted by Ronin1981

Now I got my first step in VBA code using. First step is always a bit of pain. Thank you!

Try it, you'll love it And we're always here to offer help.

Originally Posted by Sebastián Cabeza

I was looking after a solution for a similar problem. It is new for me, although I have been using Excel a lot, for a lot of years.
[....]
In general, is this floating dot mathematics problem related to newest versions of Excel? or running in 64 bits?

It is not specific to 64bit versions of Excel. It occurs in 32bit versions as well. And it has been an issue since at least Excel-97, based on Microsoft KBs; but I suspect much longer, perhaps from the very first Excel.

The problem is due to the inherent nature of 64-bit binary floating-point representation of values and arithmetic. Note: 64-bit binary floating-point is used in 32bit versions of Excel.

For some idea of the nature of the issue, you might read http://support.microsoft.com/kb/78113.

But in a nutshell, these problems arise because most most non-integers cannot be represented exactly. That creates infinitesimal differences in most arithmetic. For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!).

Originally Posted by Sebastián Cabeza

My questions:
"paste the code into the worksheet module" means to create a new module in the VBAProject and pasting it there, like if it was a macro?

No. It means: right-click on the worksheet tab at the bottom of the Excel window, and click on View Code.

Originally Posted by Sebastián Cabeza

Does this code make the calculation process slower?

Technically, yes. But only for the cell(s) just edited. The concomitant recalculation of any dependent cells is not impacted by the Worksheet_Change event macro.

However, there are other reasons why I would not recommend this approach.

1. Rounding to 6 decimal places is arbitrary. Instead, you should round to the precision that you expect a calculation to be accurate to. For example, 2 decimal places for many financial calculations.
2. Arbitrarily rounding all calculations is often not the correct answer. That is one of the many reasons why setting the option "Precision as displayed" is dangerous and not recommended in most circumstances.
3. The Worksheet_Change event macro (and setting "Precision as displayed") will not correct some rounding problems. For example, IF(10.1 - 10 = 0.1, TRUE) will still return FALSE(!).
4. I believe olly's Worksheet_Change event macro is flawed, although I have not tested it myself. Try it when the changed cell contains a text value.

IMHO, it is best to explicitly round each expression that you expect to be accurate to a specifice number of decimal places, even though that might seem tedious.