Round Off Formula in Excel: Round Functions

The ROUND function in Excel is a mathematical function used to round a number to a specified number of decimal places or digits. The ROUND Off formula in Excel is a valuable tool and is particularly useful when working with large datasets. It helps to simplify numbers for presentation, calculation, or formatting tasks. This blog will discuss different functions to round off in Excel with examples.

• ROUND Function in Excel
• Different Round Functions in Excel
• Example 1: ROUND Function in Excel
• Example 2: ROUNDUP Function in Excel
• Example 3: ROUNDDOWN Function in Excel
• Example 4: MROUND function in Excel
• Example 5: Rounding Off using the Formatting Option in Excel
• Example 6: Rounding Off using the Decrease Decimal Method in Excel
• Points to Remember

ROUND Function in Excel

The round function allows us to round a number to a specified number of decimal places. The number of decimals can be a positive, negative or zero number.

Purpose: The ROUND function seeks to approximate a value by determining it to a number of decimal places  

Features: The ROUND function allows you to simplify the numbers to facilitate calculations and have a more general perception of them. In addition, it allows rounding to the right and left of the comma.

Syntax: =ROUND(number; num_decimals)

Arguments:

• number: Value that will be rounded.
• num_decimals: The number of decimals to which you want to round.

Different Round Functions in Excel

Function	Description
ROUND	Rounds to a specific number of decimal places (or multiples of 10)
ROUNDUP	Round up a number
ROUNDDOWN	Round down a number
MROUND	Rounds a number to a specified multiple

Example 1: ROUND Function in Excel

The ROUND function in Excel rounds a number to a specified number of decimal places. In this case, we want to round 123.45678 to two decimal places.

Here's how the formula works:

=ROUND(A2,2)

• 123.45678 is the number that we want to round, located in A2.

• ,2 specifies that we want to round it to two decimal places.

The ROUND function takes the number 123.45678 and rounds it to the nearest number with two decimal places. In this example, the result is 123.46.

To make your number free of any decimals and completely rounded, you can use 0 as num_decimal.

This formula would return the rounded-off number. 

You can then drag and drop the desired formula to complete the series.

Example 2: ROUNDUP Function in Excel

The ROUNDUP function in Excel rounds a number up to a specified number of decimal places. In this case, we want to round the number 123.45678 to two decimal places.

Here's how the formula works:

=ROUNDUP(A2,3)

• A2 is the cell where the number 123.45678 we want to round up is located.

• ,3 specifies that we want to round it to two decimal places.

The ROUNDUP function takes the number 123.45678 and rounds it up to the nearest number with two decimal places. In this example, the result is 123.457.

To completely round the number up, use 0.

Drag down to apply to the series.

Example 3: ROUNDDOWN Function in Excel

With the “ ROUNDDOWN” function, we specify that we want the rounding to occur towards the previous number, in the direction towards zero.

• The ROUNDDOWN function truncates the number to the specified number of decimal places by removing any digits beyond that point. 
• It always rounds the number down, even if the decimal portion is greater than or equal to 5.

In this case, we want to round down the number 123.45678 to 2 decimal places.

Here's how the formula works:

• The first argument in the formula is the number you want to round down, which is 123.45678 in this case.
• The second argument is the number of decimal places to which you want to round down, which is 2 in this example.

The ROUNDDOWN function takes 123.45678 and rounds it down to 2 decimal places. Since the original number has five decimal places, it rounds down the decimal portion to two decimal places, resulting in the value 123.45.

In Excel, when you use the ROUNDDOWN function with a second argument of 0, it rounds down the number to the nearest integer. Here's an example:

Let's say you have the number 123.45678 and want to round it down to the nearest integer (whole number). You can use the ROUNDDOWN function like this:

=ROUNDDOWN(A2,0)

In this formula:

• The first argument is the number you want to round down, which is 123.45678, in this case, sitting in the cell A2.
• The second argument is the number of decimal places to which you want to round. When you specify 0, it means you want to round down to the nearest whole number.

So, when you enter this formula in Excel, the result will be 123, as it rounds down 123.45678 to the nearest integer, which is 123.

If you want to apply RUNDOWN with 0 to the rest of the series, place your cursor at the bottom-right corner of the cell where you entered the formula and drag it down.

Example 4: MROUND function in Excel 

The MROUND function in Excel rounds a number to the nearest multiple of another number. In this case, we want to round the number 123.45678 in cell A2 to the nearest multiple of 5.

Here's how you can calculate it using the MROUND function:

=MROUND(A2, 5)

In this formula:

• The first argument is the number you want to round, which is 123.45678.
• The second argument is the multiple to which you want to round the number, which is 5.

The MROUND function takes the number 123.45678 and rounds it to the nearest multiple of 5. In this case, the nearest multiple of 5 is 125, so the calculation result is 125.

Example 5: Rounding Off using the Formatting Option in Excel

To round off numbers in an entire Excel sheet, you can use Excel's formatting options. Here's how you can do it:

Step 1 - Select the cells or columns with the numbers you want to round off.

Step 2 - Right-click and choose "Format Cells."

Step 3 - Select formatting in the "Number" tab.

Step 4 - Set the decimal places (e.g., 2 for two decimal places).

Step 5 - Click "OK" to apply rounded formatting.

Example 6: Rounding Off using the Decrease Decimal Method in Excel

To round off numbers using the "Decrease Decimal" method in Excel:

Step 1 - Select the cell or cells with numbers.

Step 2 - Locate the "Decrease Decimal" button in the Ribbon

Step 3 - Click the "Decrease Decimal" button to reduce the number of decimal places. Each click decreases by one decimal place.

Step 4 - Repeat until you achieve the desired level of rounding.

Difference Between ROUND and ROUNDOFF Functions

ROUND Function
The ROUND function in Excel is specifically designed to round numbers to a specified number of decimal places based on standard rounding rules.
Rounding Off
The term rounding off refers to the broader concept of simplifying a number by reducing its digits while keeping it close to its original value. This can include various rounding methods, such as rounding up or down.

Points to Remember

• If num_decimals is greater than 0 (zero), the number is rounded to the decimal places specified.
• If num_decimals is 0, the number is rounded to the nearest whole number.
• If num_decimals is less than 0, the number is rounded to the left of the decimal separator.
• To round up (away from zero), you can use the ROUND.UP function in Excel. It is equivalent to using a positive num_decimals in the ROUND function.
• To round down (towards zero), you can use the ROUNDDOWN function in Excel, equivalent to using a negative num_decimals in the ROUND function.
• To round a number to a specific multiple (e.g., round to the nearest 0.5), you can use the MROUND function, which allows you to specify the desired multiple for rounding.