ROUND Formula in Excel (Table of Contents)
Introduction to ROUND Formula in Excel
Round function in Excel returns the rounded value of a supplied number to a specified number of decimal places. In simple terms, this function rounds off a number to a given number of digits. This function is present in all the versions of MS Excel. The Round function in Excel falls under the “Math & Trigonometry” formulas. This function can be used as a worksheet function.
Syntax:
Arguments:
- Number: The number that we want to round.
- No. of digits: Is the number value. It is the number of digits of the decimal place we want our number to round off.
How to Use ROUND Formula in Excel?
We will learn how to use the function and how this will make the calculation of number or currency easier. Here are some examples.
Example #1 – Round Off a Number
Here we want to round off a number to 1 digit. So we use the ROUND Formula in cell C2.
After using this formula the answer is shown below.
Drag the same formula in cell C2 to cell C7.
Here the function will round off the number to one digit to the right of the decimal. The numbers above 5 will be rounded up and the number below 5 will be unchanged.
Example #2 – Finding the Rounded Number
To find out the rounded number, when the number of digits is 0. We will use the ROUND Formula in Cell C2.
After using this formula the answer is shown below.
Drag the same formula from cell C2 to cell C7.
We have some numbers below and we will see how the number is rounded off when we have 0 as no of digits. The numbers are rounded off to the nearest integer when the number of digits is 0.
As shown above, the number 33.25 is rounded to 33, 144.71 rounded to 145 as the first digit after the decimal is 7. And so other remaining numbers will be rounded to the nearest whole number.
Example #3 – Using the ROUND Formula
If the number of the digits is 2 the numbers will be rounded off as shown below. We are using ROUND Formula in Cell C2.
After using this formula the answer is as shown below.
We have a table of numbers that we want to round off up to 2 decimal places. Drag the same formula in cell C2 to cell C6.
Here the number is rounded of two decimal places. Like 12.539 is rounded off to 12.54 and the number 112.465 is rounded off to 112.47 as the last digit after the decimal is 5 or greater than 5 thus rounding off the previous digit.
Example #4 – Rounding Off to Nearest Value
When we have -1 as the number of digits, the number will be rounded off as below. We are using ROUND Formula in cell C2.
After using this formula the answer is as shown below.
Drag the same formula from cell C2 to cell C6.
The numbers will be rounded off to the nearest 10th value when it is -1 place of decimal. 17.536 is rounded off to 20, 83.893 is rounded off to 80 and so 124. 465, 215.793 and 311.368
Are rounded off to their nearest 10th respectively.
Example #5 – Rounding the Negative Number
How the negative number is rounded when the number of digits is 1? We will see this in the below table. We are using ROUND Formula in cell C2.
After using this formula the answer is shown below.
Drag the same formula in cell C2 to C6.
Here the numbers are rounded off to one place of decimal as it would have been in a positive number. These were some examples of ROUND function in Excel showing how the number is rounded off depending upon the number of digits. The rounded numbers are easy for calculation.
Here is an example showing how the rounding of number works in the calculation.
Example #6 – Calculate Commission Sales
Here we have a sample data table showing sales made by some sales executives and we have to calculate the commission on those sales.
After using this formula the answer is shown below.
In cell C7 we are using SUM Formula.
After using this formula the answer is shown below.
In cell C2 we are using ROUND Formula.
After using this formula the answer is shown below.
In the above example, the commission is calculated to 2 decimal places. When we rounded the values to 0, the values became rounded and are easy to understand and can be easily communicated as well.
Example #7 – Rounding off the Numbers by Cell Formatting
We can also round off a number by changing the cell format. You can do this in below steps:
- Select a cell containing the number that you want to round off.
- Press CTRL+1, to open the format cell dialog box, then go to the number or currency tab and then type in the number of decimal you want to see in the decimal box.
- Click Ok and close the dialog box.
Then,
Select the decimal places.
Press OK Key. You will see the number rounded off as below.
This is also an alternative method for rounding off numbers.
Also, there are two other forms of ROUND functions:
- ROUNDUP – This function rounds the number upward as per the specified number of digits.
- ROUNDDOWN – This function rounds the number downwards as per the specified number of digits.
Going through the above examples, you will be able to understand the ROUND function and its uses in Excel. It is a commonly used function in calculations and presentation of numeric data.
Things to Remember About ROUND Formula in Excel
- The ROUND function comes under Math and Trigonometry function.
- The function rounds off a number as per the number of digits specified.
- There are some cases where ROUNDUP and ROUNDDOWN functions suit better than the ROUND function.
- When the number of digits is from 1-4 after the decimal, the ROUND function will round the number down. And when the digit is from 5-9, the function will round up the number.
- If the number of digits is greater than 0, the number is rounded to a specified number of digits to the right of the decimal point.
- If the number of digits is less than 0, the number is rounded to a specified number of digits to the left of the decimal point.
- If the number of digits equals to 0, the number is rounded to the nearest whole number.
Recommended Articles
This is 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 can also go through our other suggested articles –
