Categories
Interior

How to set the rounding precision in excel

You can frequently prevent floating point rounding errors from affecting your work by setting the Precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

Note: Using the Precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.

Click File > Options.

In Excel 2007: Click the Microsoft Office Button , and then click Excel Options.

Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.

In the worksheet, select the cells that you want to format.

On the Home tab, click the Dialog Box Launcher next to Number.

In the Category box, click Number.

In the Decimal places box, enter the number of decimal places that you want to display.

Tip: To minimize any effects of floating point arithmetic storage inaccuracy, you can also use the ROUND function to round numbers to the number of decimal places that is required by your calculation.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to round values before summing in Excel?

The ROUND function and SUM function are usually used in Excel, but have you ever tried to combine these two functions, such as to round values before summing or round value after summing?

Round values before sum

Select a blank cell that you want to place the formula, and choose and paste one of below formulas as you need, press Shift + Ctrl +Enter key to get to result:

Then the values will be rounded/rounded up/rounded down firstly, and then sum.

Round values after sum

If you want to round values after summing, you can do as this:

Select a blank cell that you want to place the formula, and choose and paste one of below formulas as you need, press Enter key to get to result:

How to set the rounding precision in excel

How to use rounding percison in Microsoft Excel ?

While using Microsoft Excel, if you want to save your precious time, you may set rounding precision. By settings rounding precision, you may frequently prevent floating point rounding errors from affecting your work. Besides saving your time, this option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet. When you set your Excel sheet to round numbers, it deletes the extra data following the precision point you set, permanently affecting the accuracy of the numbers in your workbook. So it is suggested to use this feature only if you are certain that the displayed precision will maintain the accuracy of your data in Excel. You may set it on the workbook basis. If you are wondering how to set rounding precision in Microsoft Excel, here we will provide you some easy steps to do that.

Follow the below given steps to set rounding precision in Microsoft Excel:

  • Open your work sheet in Microsoft Excel on your computer.
  • Click File>Options. If you are using Microsoft Excel 2007, click the “Microsoft Office Button” and then click “Excel Options.”
  • Click “Advanced” and then under “When calculating this workbook” section, check the box in front of “Set precision as displayed” and then click “OK.”

How to set the rounding precision in excel

  • Click “OK” and then select the cells that you want to format in the worksheet.
  • Switch to the “Home” tab, select the “Dialog Box Launcher” next to “Number.”
  • Select “Number” in the Category
  • In the “Decimal places” box, enter the number of decimal places that you want to display in your excel sheet.

This is how you may easily configure rounding percison and use it to save your precious time while handling Microsoft Excel Sheets.

By: Waseem Patwegar

The Rounding Precision feature in Excel can be used to fix the issue of rounding or floating point errors in Microsoft Excel. You will find below the steps to set Rounding Precision in Excel.

How to set the rounding precision in excel

Set Rounding Precision in Excel

As you must be aware, Microsoft Excel displays rounded numbers (for example 4.56) and not the actual numbers (4.557321) in Excel spreadsheets.

However, when it comes to calculations, Excel uses the actual numbers (which can be up to 15 decimal points) and not the rounded numbers as displayed in the worksheet.

This results in Excel rounding errors, which can lead to cases of clients/customers claiming errors in Excel spreadsheets, based on their manual spot checks using calculators.

A solution to such rounding or floating point errors in Excel is to force Microsoft Excel to use displayed numbers in all calculations, instead of using the actual numbers.

This can be achieved using the “Rounding Precision” feature as available in “Advanced options” in Microsoft Excel.

Dangers of Using Rounding Precision in Excel

Before going ahead with the steps to set Rounding Precision in Excel, you need to be aware that setting Rounding Precision on an already built spreadsheet can permanently impact its accuracy.

Hence, use this feature only if you are trying to match numbers and you are OK with Excel spreadsheet losing its default (15 decimal places) accuracy.

Steps to Set Rounding Precision in Excel

Follow the steps below to Set Rounding Precision in Excel.

1. Open Microsoft Excel and click on the File tab located in the top-left corner of your screen.

How to set the rounding precision in excel

2. On the next screen, scroll down to the bottom in side-menu and click on the Options tab.

How to set the rounding precision in excel

3. On Excel Options screen, click on the Advanced tab in the left-pane. In the right-pane, scroll all the way down and select Set Precision As Displayed option located under “When Calculating This Workbook” section.

How to set the rounding precision in excel

Note: You can select current or different Excel Workbook by using the down arrow located next to “When calculating this workbook” entry.

4. Once you check “Set precision as displayed” box, you will see a warning pop-up. Click on OK to confirm.

5. Click on the OK button in the Advanced tab (see image in step#3) to save the above changes.

6. Next, click on the Home Tab and select your desired number of decimal places (2 or more) for this particular worksheet.

How to set the rounding precision in excel

After this, Microsoft Excel will set Rounding precision for this particular worksheet to 2 decimal places.

New to Excel? Looking for a tip? How about a tip so mind-blowingly useful as to qualify as a magic trick? You’re in luck. In this Excel tutorial from ExcelIsFun, the 237th installment in their series of digital spreadsheet magic tricks, you’ll learn how to
use the MROUND, CEILING, FLOOR, ROUND, ROUNDUP and ROUNDDOWN functions to round numbers exactly how you’d like. See how to round to a specific digit or to a specified amount or multiple.

The ROUND function follows the standard rounding rules and rounds to a specified digit. The ROUNDUP function always rounds up to a specified digit. The ROUNDDOWN function always rounds down to a specified digit.
The MROUND function follows the standard rounding rules and rounds to a specified amount (multiple).

If you are using Excel 2003 you must add the analysis toolpak to get the MROUND function (2007 has it). To add this, go to Tools, Addins, and check the Analysis Toolpak.
The CEILING function always rounds up to a specified amount. The FLOOR function always rounds down to a specified amount.

Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.

How to set the rounding precision in excel

When you set the rounding precision in Microsoft Excel, it will automatically round off the numeric value, this allows you to save time and become hassle-free. So this is how it works.

Setting up the excels rounding precision can definitely save you a lot of time, but before you set it up, be sure that you want all values to get rounded off.

After you set it up to round numbers, it will automatically delete all the extra data in your workbook permanently.

For example, if your value is “13.7851698” the 2 digits after the decimal point will permanently delete the “51698” and leave behind “13.76”.

This feature can be turned on and off depending on your needs, which means that you can enable it in some projects and turn it off in others.

So where can you find the “Set Precision as Displayed” Option

The First step is to navigate to the “File” menu, this is located at the top left corner.

How to set the rounding precision in excel

Then you select “Options” button

How to set the rounding precision in excel

Inside the options you will see the “Advanced” category, you need to click that.

How to set the rounding precision in excel

When you scroll through the bottom, you will see “Set Precision as Displayed”, this is found under the “When calculating this workbook” section.

How to set the rounding precision in excel

After selecting the check box, an warning will pop up that will let you know that the data in the workbook is going to lose the accuracy permanently. Then you will click the “OK” button below.

How to set the rounding precision in excel

After that, you have to press the “Ok” button that is located at the lower right part in the window, and return to the “File” menu.

How to set the rounding precision in excel

And finally, you can now set the number of digits that are to be kept, you will need to alter the numbers that are in display in the “Home” menu’s “Number” group.

Home » Geeky Duck Learned » Guide: How to Set the Rounding Precision in Microsoft Excel

How to set the rounding precision in excel

There are times where rounding values to the nearest decimal or whole number should be used to improve the look and clarity of your Excel presentation. When you round numbers, you remove the least significant digits. This results in more presentable values with your preferred level of accuracy.

Rounding is often used to give estimates and to make numbers easier to work with. For example, if I want to calculate the percentage discount and I get a number such as 17.3587563, rounding it to one decimal place will give me 17.4%, which is more presentable. In this tutorial we’ll show you how to round numbers using the ROUND function.

Excel ROUND Function

The ROUND function is the most popular and the most commonly used Excel function for rounding numbers. This function rounds numbers to the nearest decimal based on your specified number of digits. If the next digit to the right is 0, 1, 2, 3, or 4, it rounds down. If the next digit to the right is 5, 6, 7, 8, or 9, it rounds up. So “7.82564” rounded to two decimal places would become “7.83”. Here’s how to use the Excel ROUND function.

1. Open an existing Excel workbook with raw numbers or create a new worksheet. Then create a new column just next to the values you want to round up or dowm and give it a name.

How to set the rounding precision in excel

2. Click to highlight the cell where you want your rounded value to go.

How to set the rounding precision in excel

3. Navigate to the main ribbon and click on the “Formulas” menu.

How to set the rounding precision in excel

4. Under the Formula options, select and click the “Math & Trig” option as highlighted below.

How to set the rounding precision in excel

The Formulas dropdown menu will open. Select the “ROUND” function in the menu options.

How to set the rounding precision in excel

5. This will open up the function’s argument window where you’ll configure the function to your desired results. Enter the number you want to round in the “Number” field. The easiest way to do this is to enter the cell number to which you are referencing. In our case we’ll use B4 to specify the top cell in our values column.

Enter the number of digits to which the number should be rounded in the “Num_digits” field. This specifies the number of digits you want the resulting figure to have. For example, you can enter 2 to signify two decimal places.

How to set the rounding precision in excel

If you want to round numbers to the nearest 10 or 100, insert a negative number in the “Number” field. Doing so will round the number to the right side of the decimal. For example, if you insert “-1” in the number field and you’re rounding a number, such as 427.13, the resulting figure would be 430.

6. Click “OK” for the number to appear in the “Results” column.

7. Now you’ll want to apply the formula to all the other cells. To do so, click on the bottom right corner and drag the formula down to the rest of the cells.

How to set the rounding precision in excel

That’s it. You’ve just rounded all the numbers to your desired number of digits.

Using the ROUND syntax

Alternatively, you can use a function’s syntax which is a lot easier compared to the method above.

A function’s syntax refers to the formula breakdown of the function and includes the function’s name, bracket, and arguments. The arguments are the “Number” and the “Num_digits.”

The Syntax for the ROUND formula is:

where “Number” is the value to be rounded and “Num_digits” is the number of digits to which the number will be rounded.

To use the ROUND syntax formula, simply follow the steps below.

1. Select the cell where you want the results to go.

2. Move your cursor to the function’s bar, and click to activate it.

How to set the rounding precision in excel

3. Type in the syntax for the ROUND formula. Here’s how it would appear in our case.

How to set the rounding precision in excel

4. Press Enter and the rounded value will appear in the results column. You can then drag down the rounded figure for the formula to apply to the rest of the cells.

Wrapping Up

Rounding numbers is a great way to make your Excel worksheet neat and presentable. And since the Excel ROUND function follows the mathematical rules for rounding numbers, you get highly accurate results that are a better representation of the actual figures.

Was this article helpful? Feel free to comment and share.

Kenn is a tech enthusiast by passion, Windows blogger by choice, and a massive coffee imbiber. He likes watching sci-fi movies in his free time and tearing gadgets apart so he can fix them.

Let’s say you want to round a number to the nearest whole number because decimal values are not significant to you. Or, you want to round a number to multiples of 10 to simplify an approximation of amounts. There are several ways to round a number.

Change the number of decimal places displayed without changing the number

On a worksheet

Select the cells that you want to format.

To display more or fewer digits after the decimal point, on the Home tab, in the Number group, click Increase Decimal or Decrease Decimal .

In a built-in number format

On the Home tab, in the Number group, click the arrow next to the list of number formats, and then click More Number Formats.

In the Category list, depending on the data type of your numbers, click Currency, Accounting, Percentage, or Scientific.

In the Decimal places box, enter the number of decimal places that you want to display.

Round a number up

Use the ROUNDUP function. In some cases, you may want to use the EVEN and the ODD functions to round up to the nearest even or odd number.

Round a number down

Use the ROUNDDOWNfunction.

Round a number to the nearest number

Use the ROUND function.

Round a number to a near fraction

Use the ROUND function.

Round a number to a significant digit

Significant digits are digits that contribute to the accuracy of a number.

The examples in this section use the ROUND, ROUNDUP, and ROUNDDOWN functions. They cover rounding methods for positive, negative, whole, and fractional numbers, but the examples shown represent only a very small list of possible scenarios.

The following list contains some general rules to keep in mind when you round numbers to significant digits. You can experiment with the rounding functions and substitute your own numbers and parameters to return the number of significant digits that you want.

When rounding a negative number, that number is first converted to its absolute value (its value without the negative sign). The rounding operation then occurs, and then the negative sign is reapplied. Although this may seem to defy logic, it is the way rounding works. For example, using the ROUNDDOWN function to round -889 to two significant digits results in -880. First, -889 is converted to its absolute value of 889. Next, it is rounded down to two significant digits results (880). Finally, the negative sign is reapplied, for a result of -880.

Using the ROUNDDOWN function on a positive number always rounds a number down, and ROUNDUP always rounds a number up.

The ROUND function rounds a number containing a fraction as follows: If the fractional part is 0.5 or greater, the number is rounded up. If the fractional part is less than 0.5, the number is rounded down.

The ROUND function rounds a whole number up or down by following a similar rule to that for fractional numbers; substituting multiples of 5 for 0.5.

As a general rule, when you round a number that has no fractional part (a whole number), you subtract the length from the number of significant digits to which you want to round. For example, to round 2345678 down to 3 significant digits, you use the ROUNDDOWN function with the parameter -4, as follows: = ROUNDDOWN(2345678,-4). This rounds the number down to 2340000, with the “234” portion as the significant digits.

Round a number to a specified multiple

There may be times when you want to round to a multiple of a number that you specify. For example, suppose your company ships a product in crates of 18 items. You can use the MROUND function to find out how many crates you will need to ship 204 items. In this case, the answer is 12, because 204 divided by 18 is 11.333, and you will need to round up. The 12th crate will contain only 6 items.

There may also be times where you need to round a negative number to a negative multiple or a number that contains decimal places to a multiple that contains decimal places. You can also use the MROUND function in these cases.

New to Excel? Looking for a tip? How about a tip so mind-blowingly useful as to qualify as a magic trick? You’re in luck. In this Excel tutorial from ExcelIsFun, the 237th installment in their series of digital spreadsheet magic tricks, you’ll learn how to
use the MROUND, CEILING, FLOOR, ROUND, ROUNDUP and ROUNDDOWN functions to round numbers exactly how you’d like. See how to round to a specific digit or to a specified amount or multiple.

The ROUND function follows the standard rounding rules and rounds to a specified digit. The ROUNDUP function always rounds up to a specified digit. The ROUNDDOWN function always rounds down to a specified digit.
The MROUND function follows the standard rounding rules and rounds to a specified amount (multiple).

If you are using Excel 2003 you must add the analysis toolpak to get the MROUND function (2007 has it). To add this, go to Tools, Addins, and check the Analysis Toolpak.
The CEILING function always rounds up to a specified amount. The FLOOR function always rounds down to a specified amount.

Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.