Excel rounding
Posted by Donald R. Hamilton on January 22, 2002 3:06 PM
would you please give me a tip on using the rounding feature. I want to calculate a value using a percentage
times a net number after deducting two specified amounts from the principal. I want two decimal places
I don't understand what the first argument on "number" represents because I want the final calculated number
rounded. I get the circular error message. What am I missing?
Posted by Steve Hartman on January 22, 2002 3:11 PM
Assuming the number you want to round to 2 decimal places in A1:
=Round(A1,2) in another cell will do it.
Posted by Joe Was on January 22, 2002 3:16 PM
Number is where you put your formula, no part of the formula can point to the cell containing the formula,
=ROUND(SUM(J23:J31),-3)
This is in J32 and it rounds the sum of J23 to J31, to the nearest thousand. JSW
Posted by Donald Hamilton on January 22, 2002 3:40 PM
Posted by Donald Hamilton on January 22, 2002 3:46 PM
Thanks for responding.
Here is my formula: Value in cell c27 is 400.
formula in cell d27 is: =.15*(400-53.85-124)
The answer is 33.3225 which is what I want rounded. My problem is that
after several similar calculations, the numbers do not add to the amounts
reflected on the screen which represents two place decimals.
I need to put this formula in several cells for further calculations and am
not placing the =round function properly apparently.
Thanks again
Posted by Aladin Akyurek on January 22, 2002 5:13 PM
> Here is my formula: Value in cell c27 is 400.
> formula in cell d27 is: =.15*(400-53.85-124)
> after several similar calculations, the numbers do not add to the amounts
> reflected on the screen which represents two place decimals.
> not placing the =round function properly apparently.
In D27 enter:
=ROUND(.15*(C27-53.85-124),2)
or if 400 must be used in other cells where you copy this formula
=ROUND(.15*($C$27-53.85-124),2)
If 53.85 and 124 are also in cells of their own as 400 is, you should consider using the cells (cell references) instead of using these numbers.
Hope this helps.
Aladin
Posted by Steve Hartman on January 23, 2002 5:42 AM
Ok, if I understand this correctly the problem is not actually with how you are rounding the numbers but that you ARE rounding the numbers. This is a problem with all computers since numbers are stored in binary form as either 0's or 1's. This works well for integerrs but with few exceptions decimals are not accurately stored. (If you are interested in why this is so I can point you to a good explanation) These slight innacuracies add up over a string of calculations and is known as rounding error. You can see an extreme example of this by setting the format in three adjacent cells to number and 0 decimals. Then type 1.4 in the first two cells and sum them in the third. You will then see excel tell you that 1+1=3! As a side note, it drives my wife crazy when I say that 1+1=3 for sufficeintly large values of 1. Maybe I should go into politics.
The best way to avoid rounding errors is to do all your intermediate calculations with no rounding and only round the final answer. If you want to see intermediate results at say two decimal places, set the format for the cell at 2 decimals. This will display the number as you wish without affecting the the number of decimals actually in the number.
