Round Formula in excel is used to round off any values to its closer values, for example, if we have value as 10.2 then this function will round up the value to 10 or if the value is 10.7 then the rounded-up value will be 11, this is an inbuilt worksheet function which takes a single argument which is the value.
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 –