The Excel Round function rounds a supplied number up or down, to a specified number of decimal places.
The syntax of the function is:
ROUND( number, num_digits )
Where the arguments are as follows:
number - The initial number.
num_digits -The number of decimal places to round the supplied number to.
Note that:
. a positive num_digits value specifies the number of digits to the right of the decimal point
. a num_digits value of 0 specifies rounding to the nearest integer
. a negative num_digits value specifies the number of digits to the left of the decimal point
The above arguments can be supplied as either simple numbers, references to cells containing numbers, or as values returned from other functions or formulas.
Using the Round Function When Comparing Numbers in Excel:
It is often a good idea to use the Round function when comparing two numbers in Excel, especially if the numbers are the result of multiple mathematical calculations. This is because multiple calculations may result in the introduction of rounding errors, which may cause small inaccuracies in the numbers stored in Excel.
For example, the decimal 5.1 may, due to rounding errors, be stored in cell A1 of your spreadsheet, as 5.10000000000001. When compared to the exact value 5.1, the value in cell A1 will not be equal to the exact value 5.1.
However, applying the Round function to the value in cell A1 removes the rounding error.
ie. the expression
ROUND( A1, 1 ) = 5.1
or even
ROUND( A1, 10 ) = 5.1
will evaluate to TRUE.
-If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
-If num_digits is 0, the number is rounded to the nearest integer.
-If num_digits is less than 0, the number is rounded to the left of the decimal point.
-To always round up (away from zero), use the ROUNDUP function.
-To always round down (toward zero), use the ROUNDDOWN function.
-To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.
Example:
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
1.Select the example in this article.
IMPORTANT Do not select the row or column headers.
Selecting an example from Help
2.Press CTRL+C.
3.In Excel, create a blank workbook or worksheet.
4.In the worksheet, select cell A1, and press CTRL+V.
5.IMPORTANT For the example to work properly, you must paste it into cell A1 of the worksheet.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.
Microsoft’s Excel spreadsheet program is an exceptional program for formatting and listing important data. If you want to display round numbers, you can round numbers in Excel in two ways: with cell formatting or with formulas. Cell formatting is best when you want to round the numbers in a box, while formatting is best when you want to reference other data in the sheet.
*Using cell formatting:
1)Enter your data series into your Excel spreadsheet. Save it frequently to avoid losing data.
2)Highlight any cell(s) you want rounded.
For multiple cells, highlight by clicking on the top left-most cell of the data and dragging your cursor down and to the right until you’ve reached the bottom right-most cell. The cells you’ve highlighted should automatically shade if you’ve highlighted correctly.
3)Right-click your mouse on any highlighted cell.
Scroll down the drop down menu until you find “Format Cells.”
4)Ensure you are in the “Number” tab in the formatting dialogue box.
Then, choose “Number” in the list on the left hand side of the box.
5)Find the place where it says “Decimal Places.”
Click on the arrow button, up or down, until you get to the desired amount of decimal places.
*For instance, if you want to round the number 16.47334 to 1 decimal place, make sure the “Decimal Places” box shows “1.” After clicking “Ok,” the number should read “16.5.”
*For example, if you want to round the number 846.19 to a whole number, make sure the “Decimal Places” box shows “0.” After clicking “Ok,” the number should read “846.”
6)Click “Ok” to apply your changes and return to your sheet.
*In order to ensure that this change is made with the entire sheet going forward, return to the sheet and click anywhere to remove highlighting. Click the “Format” menu at the top and choose “Cells.”
*Repeat the action, choosing the Number tab and the Number section. Then, ensure the Decimal Places number is listed as desired.
*Using formulas:
1)Enter the data into your spreadsheet. Save the sheet often as you enter.
2)Create a new column to show the rounded numbers in the field.
Click on the blank cell next to the number you want to round.
3)Bring your cursor to the blank field next to the “fx” button on your home tab.
You can also press the “fx” button if you want help building your formula.
4)Type an equals sign and the word “ROUND” into the field. Make sure to use all capitals.
5)Start an open parenthesis.
Type the cell location where the original data is listed, such as A1, followed by a comma and the number of decimal places you want to round. Close your parenthesis.
*For example, your formula may appear as “=ROUND(A4, 2)” and it will round the number value currently in column A, row 4 to two decimal places.
*Press “Enter” to activate the formula.
*Use “zero” as the decimal place if you want to round to the nearest whole number.
if you know you want to round up or round down to a certain number of decimal points.
7)Use a negative number to round by multiples of 10.
For example, “=ROUND(A4,-1)” will round the number into the next multiple of 10.
*Similarly, the formula “MROUND” will round to the nearest multiple of any specified number. If you want to round to the nearest multiple of two or five, this is a great formula to use.
8)Consider using the same formula on a whole column.
Select the cell where you just made your first round formula. Click and drag your cursor on the small box in the lower right hand corner. Drag it down to copy your formula to cells in the same column.
*Excel should automatically adjust the location based on the relative row, but it should not change your specified number of decimal places.
Post a Comment