problem with round down

[Chris]

I want to round a number down to three decimal places and have tried
rounddown, floor and trunc but I keep getting an unintended result in the
following situation:

If my number is 0.3021 i want to return 0.302
If my number is 0.3027 i want to return 0.302
If my number is 0.3020 i want to return 0.302, but I keep getting 0.301

All of these give me 0.301 when the fourth decimal place is a zero.
ROUNDOWN(cell,3)
FLOOR(cell,0.001)
TRUNC(cell,3)

 

[Jacob Skaria]

Try entering these numbers to a fresh sheet and apply the formula given in
cell B1...It should return 0.302 in all cases//

Col A Col B
0.3021 =TRUNC(A2,3)
0.3027 0.302
0.302 0.302

If this post helps click Yes

 

[Chris]

The number in column A is the result of a formula. If I just type 0.3020 in a
cell and then use TRUNC I get 0.302. If I reference the cell that contains
the result of my formula which is 0.302 I get 0.301 using TRUNC.

 

[JoeU2004]

If my number is 0.3020 i want to return 0.302, but I keep getting 0.301

Try:

=rounddown(round(A1,4),3)

Explanation....

When you format a number with 4 decimal places, the displayed value is
rounded. So 0.3020 is probably significantly less. You might be able to
verify that by formatting that cell to 15 decimal places. But sometimes,
even that is not good enough to see the difference.


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

 

[Chris]

Thanks Joe. That worked. I had already looked at the original number out to 8
decimal places and found no difference. After reading your post I had to take
it out to 16 places for the difference to show. Strange.

 

[JoeU2004]

I had to take it out to 16 places for the difference to show. Strange.

Actually not. This is a common problem. Just for fun, try: =IF(10.1 - 10
= 0.1, TRUE). It will return FALSE (!).

See http://support.microsoft.com/kb/78113 for a long explanation. 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.)

You might see these aberrations when you do things like =(A1-0.3020). The
parentheses are unneeded in this example. I use them habitually to avoid
Excel's half-baked attempt to correct these aberrations, which masks their
existence and effect in other contexts.

In your example, assuming you did not see the aberration when formatting to
14 decimal places, the difference was between -4.996E-16 and -5.44E-15.

If it makes sense in your application, you might consider using ROUND() at
the source of the aberration instead of simply in the ROUNDDOWN() formula.
The aberrant value displayed as 0.3020 might have a pervasive effect on
other dependent calculations. For example, if A1 is =A2*A3, change it to
=ROUND(A2*A3,4).

But the operative phrase is "makes sense". Sometimes, it is preferable to
retain the exact calculation in A1 and round only selective references.

That is one reason why I deprecate the use of an oft-mentioned alternative
to using ROUND() explicitly, namely setting the "Precision as displayed"
calculation option (Tools > Options > Calculation in Excel 2003). The PAD
option has a pervasive effect on all cells that are not formatted as
General.

Caveat: If you choose to experiment with PAD, be sure to copy your workbook
first. PAD has an irreversible effect on constants in cells that are not
formatted as General.


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