Exploring Excel's Functions, Part 7: Rounding with Excel
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Excel performs numerical calculations with great accuracy. For example, the simple act of dividing 2 by 3 gets you an answer with nine decimal places—0.666666667, to be exact. Depending on your formatting settings, you may get even more decimal places! This degree of accuracy is unnecessary for many of the things we do with Excel. It’s often desirable to get rid of some degree of accuracy. Note that I’m not talking about the display of numbers, where you can control the number of decimal places displayed without affecting the actual number. Rather, I’m talking about the actual value itself—for example, changing 0.666666667 to 0.667. Excel has several relevant functions, and you need to know the differences between them in order to select the right one for your calculation.
Basic Rounding with ROUND()
Most of us know the rules of basic rounding:
- A result of five or above rounds up.
- A result below five rounds down.
Thus, to round the value 12.3456 to two decimal places, we look at the third decimal and see that it’s 5. We therefore round up to 12.35. On the other hand, 12.3445 would round down to 12.34.
Excel’s ROUND() function is designed for this kind of rounding. It takes two arguments: the value to be rounded and the desired number of decimal places. Thus, =ROUND(12.3456, 2) evaluates to 12.35.
You can also pass a negative value for the number of decimal places, to round the part of the number to the left of the decimal. The negative value passed specifies the power of 10 to which you want to round. Here are some examples:
- =ROUND(1234.45, -1) evaluates to 1230.
- =ROUND(1234.45, -2) evaluates to 1200.
- =ROUND(1234.45, -3) evaluates to 1000.