Format cells to automatically round
Posted by Dwight on June 14, 2001 4:02 AM
A1 is formatted for currency & contains a formula which usually yields an uneven amount. Results must be rounded to the nearest penny. I know how to use ROUND function in B1 to get rounded result. Is there a way to effectively format A1 to round the result of the formula it contains so I'm making use of one cell instead of two?
Posted by zen on June 14, 2001 4:35 AM
formating the cell to currency with two decimal places will round the result, i don't know what you mean otherwise, i'm using british pennies, are you? what sis formula your using?
zen
Posted by Dwight on June 14, 2001 5:48 AM
Need to round the number; not what's displayed
Zen:
Formating to 2 decimals just rounds the number for viewing; underlying number remains the same. Problem is I have a column of numbers which, when summed,have a result which appears to be different (usually just a penny) from the sum of the numbers appreaing on the sheet/screen. Want to get rid of this "rounding error" by actually rounding the numbers before summing them. Formulas in the column vary, but basically are just a dollar amount times an item number.
Dwight
Posted by Joe Was on June 14, 2001 6:06 AM
Use formula
I use a formula to overcome this problem.
In cell B1: =IF(A1>0,ROUND(A1,2),0)
this gives to the whole penny.
The ROUND syntax is ROUND(Formula or Address,code)
code: 0 = nearest one place.
1 = nearest 1/10th
2 = nearest 1/100th
-1 = nearest ten's
-2 = nearest hundred
etc.
You can nest the ROUND function as much as you want, to condition elements of your formula. JSW
Posted by Aladin Akyurek on June 14, 2001 6:20 AM
How about
=CEILING(your-formula-that-computes-an-amount,0.05) ?
Aladin
Posted by zen on June 14, 2001 7:59 AM
Re: Need to round the number; not what's displayed
as suggested use this in a1 =(your_formula,2), the thing about it is you've got to go back to the formats cells and put to currency again.
any help?
zen
Posted by Mark W. on June 14, 2001 8:09 AM
Dwight, there is a way to cause Excel to round to
the same precision as the cell format. You can
choose the Tools | Options... menu command's
Calculation tab and check "Precision as Displayed".
Keep in mind that this is applied to the entire
Workbook and will irreversibly alter any and
all constants.
Posted by Richard S on June 14, 2001 11:53 PM
Re: Need to round the number; not what's displayed
Try Tools, Options, Calculation tab, and click precision as displayed. Data will lose detailed accuracy permanently, but it will get rid of rounding errors. Applies to entire workbbok
