Round Off Formula in Excel - Syntax and Examples

Excel offers various rounding functions that enable you to round numbers to meet different needs. In this blog, we will explore the key rounding formulas in Excel. You will learn the specific usage of each formula, along with practical examples, so you can understand when and why to apply a particular rounding function.

Table of Contents:

• What is a Round Formula in Excel?
• ROUNDUP Function
• ROUNDDOWN Function
• MROUND Function
• CEILING Function
• FLOOR Function
• INT Function
• TRUNC Function
• EVEN / ODD Function
• FAQs 

Want to learn Excel from the basic to the expert level? Check out the Excel course provided by Intellipaat!

What is a Round Formula in Excel?

The round formula in Excel allows you to round a number to a specified level of precision. Here is a basic explanation of how to use the round formula:

The round formula takes two arguments: the number you wish to round and the number of decimal places to round to. For example, = ROUND(2.1555, 2) would round the number 2.1555 to two decimal places, resulting in 2.16. The syntax works as follows:

= ROUND(number, num_digits)

Where “number” is the value you want to round, and “num_digits” specifies the number of digits to round to. If num_digits is greater than 0, then the number is rounded to the specified number of decimal places. If num_digits is 0, then the number is rounded to the nearest whole integer. If num_digits is less than 0, then the number is rounded to the left of the decimal point.

Intellipaat provides an Advanced Certification in Microsoft Excel. Enroll now to get a professional certificate!

ROUNDUP Function

The ROUNDUP function in Excel is used to round a number up to the nearest integer or multiple. Here is an explanation of the ROUNDUP function and how to use it:

Syntax: =ROUNDUP(number, num_digits)

Where,

 number = The number you want to round up, or it can be a cell reference.

 num_digits = The number of digits you want to round up to.

How to use the ROUNDUP Function?

• Step 1 – Enter the number you want to round up into a cell.
• Step 2 – In the other cell, enter the formula “=ROUNDUP()”.
• Step 3 – After the open parenthesis, select the cell containing the number you entered earlier in “Step 1” or you can directly enter the actual number too.
• Step 4 – Enter the comma and specify the multiple you want to round up to after the comma. For example:
  • If you enter “0” it will round up to the nearest integer
  • If you enter “1” it will round up to 1 decimal place
  • If you enter “-1” it will round up to the nearest ten
  • If you enter “-2” it will round up to the nearest hundred

ROUNDDOWN Function

The ROUNDDOWN function in Excel is quite opposite to ROUNDUP; it rounds a number down to a specified decimal place or multiple.

Syntax: =ROUNDDOWN(number, num_digits)

Where, 

 number = The number you want to round down, or it can be a cell reference.

 num_digits = The number of digits you want to round down to. 

How to use the ROUNDDOWN Function?

• Step 1 – Enter the number you want to round down into a cell.
• Step 2 – In the other cell, enter the formula “=ROUNDDOWN()”.
• Step 3 – In the parenthesis, select the cell containing the number you entered earlier in “Step 1” or you can directly enter the actual number too.
• Step 4 – Enter the comma and specify the multiple you want to round down to after the comma. For example:
  • If you enter “0”, it will round down to the nearest integer
  • If you enter “1”, it will round down to 1 decimal place
  • If you enter “-1”, it will round down to the nearest ten
  • If you enter “-2”, it will round down to the nearest hundred

MROUND Function

The MROUND function rounds a number to the nearest multiple that you specify. For example, you can use it to round numbers to the nearest 5, 10, 100, etc. It works similarly to ROUNDUP and ROUNDDOWN but allows more control over the rounding increment.

Syntax: =MROUND(number,multiple)

Where,

 number = The number you want to round.

 multiple = The multiple you want to round to, such as 10 to round to the nearest 

The function rounds the number (first argument) to the nearest multiple (second argument). For example, “=MROUND(23.6, 10)”,  it rounds 23.6 to the nearest 10, which is 20.

How to use the MROUND Function?

• Step 1 – Choose a cell and input the number that needs to be rounded. This will be the input number.
• Step 2 – Select another cell for the rounded output and type the formula “=MROUND(number, multiple)” where MROUND rounds to a given multiple.
• Step 3 – Between the parentheses next to “number”, enter the cell reference for the input number from Step 1 that you want to be rounded. For example, if it’s in B1, put “B1”.
• Step 4 – After the cell reference, add a comma and then specify the multiple you want to round to as the second argument. This will round the input number to a multiple of the number entered here.

CEILING Function

The CEILING function in Excel rounds a number up to the nearest specified multiple. It takes two arguments: the number to be rounded and the multiple to which it should be rounded. This function is helpful for tasks like financial modeling or when dealing with discrete quantities where values need to be rounded up to a certain interval. 

Syntax: =CEILING(number, significance)

Where,

 number = The numeric value you want to round up.

 significance = The multiple to which you want to round the number.

Points to Remember:

• Confirm that both arguments (the number and the significance) are of the correct type. The number should be numeric, and the significance should be a positive number.
• If the significance and the number you are trying to round up are not of the same type, i.e.; positive or negative, then it will give you this error: “#NUM!”.

How to use the CEILING Function?

• Step 1 – Choose the cell where you want the rounded number to output.
• Step 2 – Type the formula “=CEILING(number, multiple)” in that cell, where CEILING means rounding up.
• Step 3 – Input the cell reference to the number you want to be rounded up as the “number” argument inside the parentheses.
• Step 4 – After the cell reference, add a comma, and then specify the multiple you want to round up to as the second argument. This will round the input number up to a multiple of the number entered here.

FLOOR Function

The FLOOR function in Excel rounds a number down to the nearest specified multiple. It also takes two arguments, like the CEILING function: one is the number that needs to be rounded, and the other is the multiple to which it should be rounded.

Syntax: =FLOOR(number, significance)

Where, 

number = The numeric value you want to round down.

Significance =  The multiple to which you want to round the number.

Points to Remember:

• Ensure the number and significance are of appropriate types: numeric for the number and positive number for the significance. 
• If the significance and the number you are trying to round down are not of the same type i.e.; positive or negative then it will give you this error “#NUM!”.

How to use the FLOOR Function?

• Step 1 – Choose a cell in your worksheet and input the number that needs to be rounded down. This will be the input number.
• Step 2 – In another cell, type out the formula “=FLOOR(number, multiple)” where FLOOR indicates rounding down.
• Step 3 – In place of “number”, input the cell reference of the input number from Step 1 that you want rounding down.
• Step 4 – After the cell reference, add a comma, and then specify the multiple you want to round down to as the second argument. This will round the input number down to a multiple of the number entered here.

INT Function

The INT function in Excel rounds a number down to the nearest integer. It simply removes the decimal part of the number, returning the integer portion. Unlike other functions, the INT function has only one argument.

Syntax: =INT(number)

Where, 

Number = It is the number that has to be rounded off.

Points to Remember:

• Negative numbers are rounded more negatively; for example, “INT(-3.8)” returns “-4”.
• If the input is already an integer, then the INT function has no effect on it and returns the same number.

How to use the INT Function?

• Step 1 – In the cell, enter the number that has to be rounded as an integer
• Step 2 – Select a cell where you want the output of the INT function and enter the formula = “INT()”
• Step 3 – Enter the cell reference or exact number inside the parentheses of the formula

Check out Intellipaat’s guide on MS Excel Interview Questions to crack good interviews for data analysis jobs.

TRUNC Function

The TRUNC function in Excel rounds a number down to the nearest integer or to a specified decimal place. This is different from the INT function, which removes the decimal portion of a number regardless of its value.

Syntax: =TRUNC(number,num_digits)

Where,

Number = number is the value to truncate

num_digits = specifies the number of decimal places

Points to Remember:

• The TRUNC function doesn’t round the number; it simply removes the decimal part.
• If the num_digits is negative or omitted. It truncates the integer portion to the left of the decimal point.

How to use the TRUNC Function

• Step 1 – In your worksheet cells, input the list of numbers that you need to remove the decimals from.
• Step 2 – Select another cell and type the formula “=TRUNC(number, num_digits)”. This will truncate the decimals.
• Step 3 – Between the brackets next to “number”, enter the cell reference or actual number value that you want to truncate.
• Step 4 – For “num_digits”, you can input 0 to remove all decimals or any number 1 and above if you want to keep that number of decimals in the truncated result.

EVEN / ODD Functions

The EVEN and ODD functions in Excel are used to round numbers mathematically based on whether they are even or odd. The EVEN function rounds a number up to the next whole even number. For example, 1.5 would round to 2, since 2 is the nearest even whole number. The ODD function rounds a number up to the next whole odd number. For instance, 2.5 would round to 3 using the ODD function, as 3 is the closest odd integer. 

Syntax: EVEN(number), ODD(number)

Where,

Number = It is the value that has to be rounded off as an even or odd number.

How to use the EVEN/ODD Functions 

• Step 1 – Choose a cell and input the numbers in that cell that you need to round to either an even or odd number.
• Step 2 – In another cell, input either the formula “EVEN(number)” or “ODD(number)” based on what you need.
• Step 3 – Inside the parentheses of the EVEN or ODD formula, enter the cell reference or actual number that you want, rounded to the nearest even or odd number.

FAQs