Rounding Numbers

[nikita]

Often when carrying out simple multiplication and addition between two
cells with numeric values, I must ROUND my answer to get a whole
number, like 0.

E.G. 4 - 4 = 0.0000000000454564543 or something like that. Why do
these inconsistencies happen? How can I avoid them.

 

[JoeU2004]

Why do these inconsistencies happen? How can I avoid them.

Answering the second question first.... You already do the correct thing to
avoid them, namely: the prolific, but prudent use of the ROUND() function.

The operative word is "prudent". Sometimes it is preferable to retain the
exact value of a computation in the cell and to round only selective
references to the cell.

Another alternative might be setting the "Precision as displayed"
calculation option (Tools > Options > Calculation in Excel 2003). But I
deprecate the use of PAD for a number of reasons. For one thing, it is not
selective; it applies to all cells that are not formatted as General.

Caveat: If you choose to experiment with PAD, be sure to copy the Excel
file first. Setting PAD can have some irreversible pervasive effects.


As to the first question.... For a detailed explanation, take a look at
http://support.microsoft.com/kb/78113 .

In a nutshell: most numbers with decimal fractions cannot be represented
exactly as displayed because of the internal format used by Excel and most
applications. This causes "numerical aberrations" to arise in most
arithmetic operations. (I try to avoid the phrase "numerical error" because
this is not a defect.)

For example, try =IF(10.1 - 10 = 0.1, TRUE). It returns FALSE (!).

The constant 10.1 has a different approximation of 0.1 than the constant 0.1
itself. When we subtract 10 from 10.1, we are left with the different
approximation.

In this case, the difference is big enough that Excel does not consider them
equal. But in other cases and in some contexts, Excel has a half-baked
algorithm that tries to hide the difference.


----- original message ------

 

[JoeU2004]

PS....

Since you used the term ROUND (all caps), I assumed you are asking about
Excel.

But since m.p.e.programming is usually used for VBA questions, I should
add....

For example, try =IF(10.1 - 10 = 0.1, TRUE).
It returns FALSE (!).

Similarly, in VB:

Msgbox 10.1 - 10 = 0.1

displays FALSE.

Caveat: If you are using VBA, I would use WorksheetFunction.Round, not the
VB Round() function. They behave differently with some values. The VB
Round() function does "banker's rounding". For example, compare Round(0.5)
and Round(1.5) with WorksheetFunction.Round(0.5,0) and
WorksheetFunction.Round(1.5,0)


----- original message -----