How To Apply Round Off Formula In Excel

H

The Round Off Formula in Microsoft Excel is a simple and sophisticated method that is used to minimize all kinds of complications in the decimal values in your data. It is also used to round off the lengthy decimal values to the nearest whole. Let us now know how to round off in Excel.

Round-off Formula in Excel – Round-Up

The round-off formula in Excel for roundup will help the user round off the decimal value to the nearest higher whole number. For example, 2.8930 will be rounded up to the nearest highest value 2.9.

How To Apply Round Off Formula In Excel

It makes data presentable and easier to read and understand. There are 4 different variations of the round formula.

  • ROUND Function
  • ROUNDUP Function
  • ROUNDDOWN Function
  • MROUND Function

Now let us look at each one of them closely.

ROUND Function

What does it do?

Rounds a number to the nearest decimal based on your specified number of digits

Formula breakdown:

=ROUND(number, num_digits)

What it means:

=ROUND(the number, number of decimal places to round off)

Let’s look at each one of them closely and learn how to apply the round-off formula in Excel.

Step to Step Guide to Round Off Numbers

STEP 1: Enter the ROUND function:

=ROUND(

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 2: The ROUND arguments:

number

Add the number you want to round off

Reference the cell that contains the number:

=ROUND(B9,

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

num_digits

Round off to how many digits?

Reference the cell that contains the number of digits:

=ROUND(B9, C9)

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 3: Do the same for the rest of the cells by dragging the ROUND formula all the way down.

You will be now able to round off the formula with a different number of places!

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

Note: A negative sign before the rounding digit indicates how many places to the left of the decimal point should the number be rounded.

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

Also Read
How to enable Macros in Excel
What is Pivot Table in Excel

ROUNDUP Function

This function is used to round the number up to the number of decimal points written.

For Example –

=ROUNDUP(104.1,0)

The round-up of 104.1 to zero decimal places, will be 105!

If the argument for the number of decimal points is

  • Positive numbers – Excel will round to the right of the decimal point
  • Negative numbers – Excel will round to the left of the decimal point
  • Zero – Excel will round to the nearest 1.

ROUNDUP Formula

STEP 1: Enter the roundup formula

=ROUNDUP(

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 2: Select the cell you want rounded

=ROUNDUP(B9,

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 3: Select the cell consisting of the digits up to which the number needs to be rounded.

=ROUNDUP(B9,C9)

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 4: Copy-Paste the formula to the remaining cells.

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

The result will be given with the rounded-up amount to the given decimal points

ROUNDDOWN Function

Another variation is ROUNDDOWN which rounds down the number to the desired number of decimal places.

The steps are the same except that you select the ROUNDDOWN function instead of the ROUNDUP function.

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

For the amount of 1584.66, the ROUNDUP formula gives the result as 1585 but ROUNDDOWN provides the result as 1584!

Also Read
How to calculate age in Excel
How to change date format in Excel

MROUND Function

The MROUND function rounds the number to the nearest multiple of a number given. It will round the number up or down depending on the nearest multiple provided in the formula.

For example, this function can be used to;

  • Round a price to the nearest multiple of 3
  • Round a price to the nearest 0.05
  • Round a time to the nearest 30 minutes.

For example

STEP 1: Enter the MROUND formula

=MROUND(

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 2: Enter the first argument i.e. the number that should be rounded.

=MROUND(B9,

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 3: Enter the second argument i.e. the multiple that the provided number should be rounded to.

=MROUND(B9,C9)

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

Excel will round the number 157.764 to the nearest multiple of 5 i.e. 160!

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

STEP 4: Copy-paste the formula below.

Round Off Numbers with Excel's ROUND Formula | MyExcelOnline

And done. You have your answer with you.

Conclusion

Microsoft Excel is one of the oldest spreadsheet software programs, and it has undergone numerous upgrades and fixes. This powerful spreadsheet program allows us to perform a variety of tasks in Excel, from simple ones like calculating subtraction to more complex ones like concatenate functions

There are many round-off formulas in Excel that will present the value in the best possible way. So based on your needs and requirements, you can use the formula to calculate.

EasySheet is a tech blog where you can find amazing Excel hacks, tips, and tricks. Our goal is to make Excel more accessible to beginners and to provide them with the EasySheet as a solution for all of their questions.

We are the best resource for anyone in need of assistance with anything concerning Microsoft Office, including MS Word, Excel, PowerPoint, Publisher, and more.

About the author

Gaurav Singh Rawat

Add comment