Rounding in spreadsheet programs, such as Excel, means to shorten or reduce the length of a number without greatly changing its value.
However, a rounded number is always less accurate than the original number. If this is the case why do we round numbers?
Rounding is done for a variety of reasons including:
- making numbers easier to read and comprehend
- making numbers easier to work with in calculations
Examples of rounding numbers would be:
- reducing the number of whole digits in a very large number - such as rounding 1,547,896 up to 1,550,000 or 1.55 million.
- reducing the decimal portion of a number - such as rounding 5.15421265 down to 5.1542
Significant vs. Insignificant Digits
The decision on where to round a number involves determining how many digits in the number are considered to be important or significant.
In the example above when 5.15428265 was rounded down to 5.1542 only the first four decimal places (1542) where taken to be significant.
When rounding a number the significant digits are kept and the insignificant ones removed. What is considered significant or insignificant depends upon the intended uses for the rounded number.
According to the rules for rounding followed by Excel the rounding digit is the digit that gets changed depending on whether the value of the digit next to it is greater or less than five.
It is also the last significant digit retained once the number is rounded.
Briefly the rules for rounding are, If the value of the digit to the right of the rounding digit is:
- 5 or more - the rounding digit is increased by one - it is said to have been rounded up
- less than 5 - the rounding digit does not change - it is said to have been rounded down
Steps to Rounding
- Determine the number of significant digits
- Determine if the rounding digit needs to be altered depending on the value of the digit to its right
- Round the number
A rounding error is an error caused by using a rounded number in calculations. For manual calculations involving only a few digits these errors are seldom significant. In the case of computer computations involving data with a large number of decimal places a rounding error can be quite significant.
Rounding vs. Truncating Numbers
In some cases, truncating a number can yield the same result as rounding a number.
Using the examples above, truncating the number 5.15421265 to four decimal places will leave a result of 5.1542
This is the same result obtained by rounding the number to four decimal places since the fifth digit is less than five and therefore the rounding digit is rounded down (the last digit doesn't change).
Excel's Rounding Functions
There are a number of functions in Excel that can be used for rounding numbers. Each of these functions does a different job - such as always rounding a number up, always rounding it down, or rounding it up or down by multiples of a specified value.
Which function you use, therefore, depends on how you want the number rounded.