Excel Functions Tutorials
- Maths Functions in Excel
- ABS Function in Excel
- AGGREGATE Excel Function
- CEILING Excel Function
- COMBIN Excel Function
- COS Excel Function
- EVEN Function in Excel
- EXPONENTIAL Excel Function
- Excel Matrix Multiplication
- Excel Minus Formula
- FLOOR Function in Excel
- Histogram Formula
- INT Excel Function (Integer)
- LN Excel Function
- LOG Excel Function
- Mode in Excel
- ODD Function in Excel
- POWER Function in Excel
- PRODUCT Excel Function
- PRODUCT Formula in Excel
- PI in Excel
- Quotient in Excel
- Running Total in Excel
- RAND Excel Function
- Rand Excel Formula
- RANDBETWEEN in Excel
- MROUND in Excel
- ROUND in Excel
- ROUND Formula in Excel
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- SIGN Excel Function
- SIN Excel Function
- Square Root in Excel (SQRT)
- SUBTOTAL Excel Function
- SUM Function in Excel
- SUM Formula in Excel
- Excel SUM Shortcut
- SUMIF in Excel
- SUMIFS in Excel
- SUMIF Not Blank
- SUMIFS with Dates
- SUMIF Between Two Dates
- Sumif Text in Excel
- SumIf with Multiple Criteria
- SUMPRODUCT Function in Excel
- SUMPRODUCT Formula in Excel
- SUMPRODUCT with Multiple Criteria
- How to Sum Multiple Rows in Excel?
- TAN Excel Function
- TANH in Excel
- Average vs Weighted Average
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
Excel ROUND Formula
ROUND formula in Excel is used to return the number that is rounded as per the given number of digits. We can round the number to the left or right of the decimal point.
General Syntax for Round formula in excel is as follows:
Number – Number that we want to round and this is a mandatory field.
Num_digits – Number of digits to which the given number should be rounded.
- If num_digits > 0, the number will be rounded to the right of the decimal point.
- If num_digits < 0, the number will be rounded to left of the decimal point (i.e. nearest 10,100,1000 etc.).
- If num_digits = 0, the number will be rounded to nearest integer.
We have other important features in a ROUND formula in excel They are: –
#1 – ROUNDUP Formula
This function will return the number that is rounded up to a given number of decimal places. Usually, the excel Round formula, the number will be round up to where we have decimal value more than 5 and will round down where we have a decimal value less than 5 but in ROUNDUP function it will always round up irrespective of any value in the decimal places.
#2 – ROUNDDOWN Formula
This function will return the number that is rounded down to a given number of decimal places. Usually, in Round excel function, the number will be round up to where we have decimal value more than 5 and will round down where we have a decimal value less than 5 but in ROUNDDOWN function it will always round down irrespective of any value in the decimal places.
#3 – MROUND Formula
MROUND function will return a number that is rounded to a “multiple” which is specified by us. This will round the number either up or down depends on the nearest multiple.
The syntax for MROUND – MROUND(number, multiple)
How to Use the ROUND Formula in Excel? (with Examples)
Following are the ways with which excel ROUND formula can be used.
Example #1: Using ROUND Formula
Let’s assume that we have the data of money spent by 6 employees on a particular day as shown below:
Step 1 – If we want to round the amount spent with zero decimal, then we can use the excel ROUND formula as follows and you will get the output of rounded numbers with zero decimals.
Step 2 – The result is shown below.
Step 3 – On dragging this from cell C2: C7, we can get the result shown below.
By this way, we can round the given numbers. If we can observe from the above data we can see that number got round up or round down based on the decimal points (If more than 5 then round up and if less than 5 then round down)
Example #2: Using ROUNDUP Function
Let’s assume that we have the data on parking fees that need to be charged for the vehicles based on the hours. The data is as follows:
The above data have the parking hours of different vehicles and fees to be charged table. Now if we want to round the number of parking hours to charge fees we can use ROUNDUP function because if time is 2.2hours and we use round function to round it will give 2 hours as the rounded number which is already crossed more than 2 hours and fee should be charged as per the 3 hours’ fees.
Now use ROUNDUP function for the above data to get the hours’ details to charge the fees.
The result is shown below.
Here we can observe all given values has been rounded up to the whole number as we had given the num_digits as zero which is for decimals. And the result would be as follows:
Example #3: Using the ROUNDDOWN Function
Let’s assume that we have data of products completion status as below:
We have got 6 products with names A, B, C, D, E, F, and their completion status. But we have the completion status in terms of decimal points. When we have to pay wages to the employees based on their product completion we have to consider the whole number as this tell the number of total products that they fully completed the production and decimal number will tell that it is still under work in progress.
To get the whole number we can use the excel ROUND formula but in this case, it won’t give the actual completion products number because if the decimal number is more than 5 then it considers the upper value which is not correct to consider the product completed. For ex: Product D we got number 53.98 which tells the employees had completed 53 products but the 54th product is 0.98 or 98% only completed which we should not consider as completed.
To avoid this kind of issues we can use ROUNDDOWN function to get the actual number of completed products.
The results are shown below.
We can observe that number got rounded down to the whole number as we had given the num_digits as zero which is for decimals. The output would be as follows:
Example #4: Using the MROUND Function
Assume that we have a number with the decimal point but we need to convert it to a nearest whole number with specified multiple then we can use MROUND function.
Let’s assume the number as 5324.77 and to change the number into a whole with multiple would be as follows:
Here we can observe the number had got changed to a whole number with the nearest multiple of 10.
Here we can observe the number had got changed to a whole number with the nearest multiple of 25.
Similarly, we can do for other multiples.
Things to Remember about ROUND in Excel Formula
- ROUND() is the best used to round the floating numbers
- This will round right or left of the decimal point
- The negative number in num_digits: When we use a negative number in the place of num_digits we get the multiple of 10.
When we take negative numbers as the negative number as mentioned in the above data we get the result with multiple of 10 for -1, 100 for -2, 1000 for -3 and so on. The result is as follows:
- MROUND drawback when we try to change negative number into multiples:
When we try to change negative number into multiple by using MROUND we will get a #NUM error as follows:
In order to change the negative numbers into multiples, we can do it in another way by using the excel ROUND formula by diving the given number with the multiple and multiplying the result with the same multiple. This can be as follows:
In the above example, we had performed for the multiple of 25 so we multiplied the given number with 25 and multiply the result of ROUND() with 25 to get the final result. Similarly, for any negative value to change it into any kind of multiple we can use the above method.
This has been a guide to ROUND Formula in Excel. Here we discuss how to use ROUND Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –