Round formula in Excel

Excel offers an in-built function to round the value. These values are numeric values. You can round the numbers to one decimal place, two decimal places, and more you want. A function named ROUND() is a built-in function of Excel that enables the users to round the numbers in Excel.

Sometimes, the users require a rounded value to perform the specific operations. In such case, they can use this ROUND() function. This chapter will cover the introduction, syntax, and examples in detail.

ROUND() function

Excel offers a ROUND() function to round the numbers to a specific number of digits you want. For example, A cell A2 contains a number 53.1243 and you want to round the value to three decimal places. This function is used like -

It will extract the three digits from the last and keep the remaining after the decimal point. The returned value will be 53.1.

Additionally, Excel also offers some more functions (ROUNDUP, ROUNDDOWN) to round the numbers in different ways. They used when they needed.

Syntax

The ROUND() function will take two parameters, and both are mandatory ones.

  • The number parameter will take the targeted value which you want to round.
  • The second parameter is num-digits that will take an integer number to how many numbers of digits you want to round.

Note: Instead of simple rounding of the value, it can also round the number from left and right.

Return Value

It returns a rounded number. Or say that - this function will return the remaining value after rounding the number.

Example

We will try to explain this function and its usage by using different examples with detailed Excel steps.

Example 1

We will show you the steps to round the number stored in an Excel worksheet. It is an example of rounding the number from the right of the decimal point. Here they are -

Step 1: We have some values with a decimal point in an Excel sheet.

Round formula in Excel

Step 2: Select an adjacent cell to keep the returned resultant value and write the following formula either here or in the formula bar.

=ROUND(A2, 2)

Round formula in Excel

Here, we are rounding the number to two decimal places.

Step 3: Press the Enter key and see the number after rounding. See the screenshot what value it has returned.

Round formula in Excel

Step 4: In the same way, we have rounded more values present here and now see the result for them.

Round formula in Excel

Similarly, you can round more numbers.

Rounding the numbers to left and right decimal place

Instead of simply rounding the number, you can also round the number from left and right to the decimal place using the ROUND() function. It is an additional feature of this function.

Generally, we round the value from the right by providing a positive number in num-digit parameter. But you can do more. We have the following conditions for different types of rounding.

Round to Right

If the num-digit parameter holds a positive number (>0), the number will be rounded to the specified decimal places to the right of decimal point.

For example, we have numeric data with a decimal point. Following are few formulas to round the number from the right.

FormulaDescriptionResult
=ROUND(12.4892, 3)Round to three decimal places12.489
=ROUND(48.7341, 1)Round to one decimal places48.7
=ROUND(13.482,2)Round to two decimal places13.48
=ROUND(19.437, 1)Round to one decimal place19.4

Round to Left

If the num-digit parameter holds a negative number (<0), the number will be rounded to the specified decimal places to the left of decimal point. Basically, nearest to the 10, 100, 1000, and more. For example, 27.3 will be rounded nearest 10 will be 30.

Suppose that we have data in A2 cell. Here we have few formulas for different values to round the number from the left of the decimal point.

FormulaDescriptionResult
=ROUND(12.4892, 0)Round to the nearest whole number or nearest to 112
=ROUND(48.7341, -1)Round to nearest 1050
=ROUND(313.482, -2)Round to nearest 100300
=ROUND(619.45, -3)Round to nearest 10001000
=ROUND(619.45, -2)Round to nearest 100600

For rounding the number from right, you have seen example 1 with detailed steps in the Excel worksheet and verify how this function works. Now, let's see for round the number from left.

Example 2

We will show you the steps to round the number stored in an Excel worksheet. It is an example of rounding the number from the left of the decimal point. For this, we have to provide a negative number in num-digits parameter. Here they are -

Step 1: We have some values with a decimal point in an Excel sheet.

Round formula in Excel

Step 2: Select an adjacent cell to keep the returned resultant value and write the following formula to round the number nearest to the whole number or 1.

=ROUND(A2, 0)

Round formula in Excel

Here, we are rounding the number nearest to the whole number.

Step 3: Press the Enter key and see the number after rounding. See that it has returned 12 after rounding.

Round formula in Excel

Step 4: In the same way, we will round more values given here to clear the concept for negative numbers. Where -1 for nearest 10, -2 for nearest 100, -3 for nearest 1000, and so on.

Round formula in Excel

Step 5: Now, see the result for all values present here.

Round formula in Excel

Similarly, you can round more numbers accordingly you needed.

Note: The ROUND() function is available under the Math & Trig section in the Formula tab.

Other rounding functions

Besides this simple ROUND() function, Excel also offers several other round functions for different purposes. They round the number in different ways. These functions are -

ROUND() - For simple round

MROUND() - For rounding the number to its nearest multiple.

ROUNDDOWN() - Round down the number to the nearest specified place.

FLOOR() - Round down the number to its nearest multiple.

ROUNDUP() - Round up the number to the nearest specified place.

CEILING() - Round up the number to its nearest multiple.

INT() - Round down the number and return an integer number only.

TRUNC() - Truncate the decimal place.

All these methods, we have discussed in separate chapters. You can learn them from there.

Things to remember

Few points to remember about the ROUND() function -

  1. The ROUND() function is a part of Math & Trig function.
  2. If you are not comfortable using the formula directly, you can find it inside the Formula tab under the Math & Trig dropdown list.
  3. The ROUND() function returns a rounded number to a specific number of digits.
  4. It rounds the numbers 1-4 down and 5-9 up.

Next TopicExcel formulas




Latest Courses