Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with round down
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with round down
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 --------------- Jacob Skaria "Chris" wrote: 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with round down
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Chris" wrote: 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with round down
"Chris" wrote:
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" wrote in message ... 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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with round down
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" wrote: "Chris" wrote: 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" wrote in message ... 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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with round down
"Chris" wrote:
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 ----- "Chris" wrote in message ... 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" wrote: "Chris" wrote: 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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
round up #.#0 and #.#5 | New Users to Excel | |||
Round() problem | Excel Worksheet Functions | |||
Don't Round... | Excel Discussion (Misc queries) | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions | |||
How can i round off .10 as .50 and .51 as 1.00 | Excel Worksheet Functions |