For rounding the numbers e.g. 45.135785 to 45.14, you may use the built-in functions in Excel.
The Excel has a number of functions that enable rounding the decimal numbers as per the need. These functions are:
Let us look at the syntax/examples of using these functions one by one.
The Excel ROUND function
The general syntax of ROUND function is:
- The number argument is the decimal number to be rounded. It can be a number, cell reference or decimal number from other function.
- num_digits specifies the numbers after the decimal point.
The example of ROUND in Excel
In the following example, the given number is rounded to 2 decimal points. The number contains five numbers after the decimal point.
The result of rounding 124.45712:
Rounding a given number to one decimal place
The formula of ROUND:
How to round a number nearest multiple of 1000 by ROUND function?
The following formulas will round the number to the nearest 1000 by using ROUND function. For that, I entered different numbers in A column and used the ROUND formula in B column:
The formula for nearest 1000:
The resultant sheet:
You can see various numbers rounded to nearest 1000 by using -3 value for the num_digits argument.
Nearest 100 formula using ROUND
The formula for getting the nearest hundred is:
The same formula is applied from C2 to C8 cells by changing cell reference in ROUND function.
Using ROUND for nearest 10
Similarly, you may use the -1 value in the ROUND function to get the nearest 10 value. Have a look:
The formula for nearest 10:
In the same way, you may get the nearest multiple of 10000 by using -4 e.g.:
and using -5 can be used for 100000.
Getting the nearest integer example
By using 0 value, you may get the nearest integer value of the given decimal number:
Result = 525
An example of using ROUNDUP function
For getting the round up number i.e. away from 0, use the ROUNDUP function. See an example with different numbers:
The formula used in B3 cell:
The result for 14.23 is 15. If we used ROUND function, the result would have been 14.
The example of ROUNDDOWN function
To round the number towards zero, use the ROUNDDOWN function. See the example formula and output below using the same numbers as in ROUNDUP function:
The formula used in B2 cell:
You can see the difference by comparing both result sheets. In ROUNDOWN case, the result for 15.52 is 15 while ROUND would have produced 16.
The MROUND function
Although, I showed you examples of rounding a number to a specific multiple by using ROUND function. For example, nearest multiple of 10, 100, 1000 etc.
The specific function for getting the nearest multiple is MROUND in Excel. The following example shows using MROUND for getting the nearest multiple of 5, 10, 100, 1000, and 10000. See the formulas and output sheet:
The nearest multiple of 5 in B2 cell formula:
10 in B3 cell:
100 in B4 cell:
1000 in B5:
10000 in B6:
You may check the formula and compare the numbers in above excel sheet.