Fabulous Examples of Excel Functions:
the ROUND Function
Microsoft Excel has many built-in functions to perform calculations. This tutorial covers the ROUND function in detail. A sample worksheet helps explain how to use the ROUND function.
Don't forget that every Excel function begins with an equal = sign unless the function is imbedded inside of another function.
Author: Patricia Lynn
If you are new to Excel math, we recommend you begin by reading our tutorial Excel Math Basics: A Beginner's Guide.
The ROUND Function
The ROUND function rounds the value in a cell following normal rounding rules: round down if the decimal portion is < 5, and round up if the decimal portion is ≥ 5. The syntax of the ROUND function is:
ROUND(number,number-of-digits)
… where number is the number to be rounded, and number-of-digits is the number of decimal places that Excel should round to.
To determine the "number-of-digits" properly, it is important to understand decimal places and place value. The image below shows three digits to the left of a decimal point, and three digits to the right. The place value of each position is listed below. Don't get "tens" confused with "tenths" and "hundreds" confused with "hundredths" - the place values to the right of the decimal point end in "ths."
The numbers shown in the digit boxes are the "number-of-digits" you would specify in the function to have Excel round to that decimal place or place value.
Examples of the ROUND Function
Let's suppose the value of cell A3 is 7283.518:
=ROUND(A3,2) This Excel function rounds the value in A3 to the nearest hundredth, or to 2 decimal places. Excel looks at the number to the immediate right of the hundredths position (8) to determine whether to round up or round down. Excel will round up and display 7283.52 in the cell because 8 is greater than 5.
=ROUND(A3,1) This Excel function rounds the value in A3 to the nearest tenth, or to 1 decimal place. Excel looks at the number to the immediate right of the tenths position (1) and will round down and return a value of 7283.5 - as the number 1 is less than 5.
=ROUND(A3,0) This Excel function rounds the value in A3 to the nearest integer (no decimal places). Excel will round up and display 7284 in the cell as the number 5, in the tenths position, is greater than or equal to 5.
=ROUND(A3,-1) This Excel function rounds the value in A3 to the nearest tens position. Excel will round down and display 7280 in the cell because 3, in the ones position, is less than 5.
=ROUND(A3,-2) This Excel function rounds the value in A3 to the nearest hundreds position. Excel will round up and display 7300 in the cell since 8, in the tens position, is greater than 5.
=ROUND(A3,-3) This Excel function rounds the value in A3 to the nearest thousands position. Excel will round down and display 7000 in the cell since 2 is less than 5.
Extra credit: If the value of B2 is 197.2, and we want to round to the "tens" position, what would the answer be? The formula would be =ROUND(B2,-1). The number in the tens position is 9. We inspect the number to the immediate right and it is 7 - so we should round up, right? But how do you round up 9? You "carry the one" like in addition. The answer would be 200.
Don't forget that the number portion of the ROUND function can be a formula or function. An example is shown below. And the ROUND function can be used inside of other functions.
Why the ROUND Function is Important
When you format a cell in Microsoft Excel, you specify how Excel should display the cell contents. The actual value of the cell may be quite different.
For instance, if a formula in a cell results in a value of 23.8, but you formatted the cell with 0 (zero) decimal points, Excel rounds the number and displays 24 in the cell. However, the real value of that cell is still 23.8.
When worksheets contain a lot of math formulas, and especially when results of one formula are used in subsequent formulas, the worksheet can appear to have calculation errors. Consider this example, where the cells are formatted for currency:
- In cell B3 we have the formula: =.25*A3. The actual value of this formula is $3.2125, but Excel displays $3.21 in cell B3.
- Likewise, in cell B4 we have the formula: =.25*A4. Excel calculates an answer of $4.3625, but displays $4.36 in cell B4.
- Excel adds these two numbers together and displays $7.58 in cell B5. Is this correct? No! $3.21 + 4.36 = $7.57! What went wrong?
Well, Excel added the actual values together, as shown in cells H3 through H5. Then Excel rounded up the answer to $7.58. Excel behaved properly, but others may think your worksheet contains errors.
This potential problem can be eliminated using the Excel ROUND function to calculate the percentages. This is shown in columns D and E. The formula in cell E3 becomes =ROUND(.25*D3,2) and the formula in E4 becomes =ROUND(.25*D4,2). Now the sum in E5 is correct because Excel is adding the "rounded" numbers together as the rounded values are the actual values in the cells.
In Conclusion
Don't forget that each Excel function begins with an equal = sign unless the function is imbedded inside of another function. In addition, it is advisable to lock any cell containing a formula or function, and then protect the worksheet to prevent accidental erasure. Step-by-step directions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide - "Locking Cells and Protecting Worksheets."
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions-round.shtml">Excel Math Functions: ROUND</a>
