Rounding numbers in Excel: 4 working fashion

When performing division or work with fractional numbers, Excel produces rounding. This is due, first of all, with the fact that absolutely accurate fractional numbers are rare when necessary, but it is not very convenient to operate with a bulky expression with several signs after the comma. In addition, there are numbers that in principle are not accurately rounded. At the same time, an insufficiently accurate rounding can lead to rough errors in situations where preciseness is required. Fortunately, the program has the opportunity to install users on their own, how the numbers will be rounded.

Features of rounding numbers Excel

All the numbers with which Microsoft Excel works are divided into accurate and approximate. The memory is stored in memory of up to 15 discharge, and displayed before the discharge, which will indicate the user itself. All calculations are performed according to stored in memory, and not displayed on the data monitor.

Using the rounding operation, Excel discards some number of semicolons. It uses a generally accepted rounding method, when the number is less than 5 is rounded in a smaller side, and more than or equal to 5 - in the most side.

Rounding with buttons on the ribbon

The easiest way to change rounding is to highlight the cell or group of cells and, while on the Home tab, click on the tape to the "Enlarge Big" button or "Reduce Bigness". Both buttons are located in the "Number" toolbar. Only the displayed number will be rounded, but for computation, if necessary, up to 15 digits of numbers will be involved.

When you click on the "Enlarge Big" button, the number of characters made after the comma increases by one.

The "reduce bit" button, respectively, reduces one number of numbers after the comma.

Rounding through cell format

It is possible to also set rounding using cell format settings. To do this, select the range of cells on the sheet, click on the right mouse button and select the "Cell Format" item in the menu that appears.

In the Cell format settings window that opens, go to the "Number" tab. If the data format is not specified, it is necessary to set it out, otherwise you will not be able to regulate rounding. In the central part of the window near the inscription "The number of decimal signs" simply indicate the number of signs that you want to see when rounding. After that apply the changes.

Setting accuracy calculations

If in the previous cases, the set parameters affected only the external data display, and during the calculations, more accurate indicators were used (up to 15 characters), now we will tell you how to change the accuracy of the calculations.

1. Click the File tab from there to the "Parameters" section.



2. The Excel parameters window opens. In this window, go to subsection "Optional". Lay the settings block called "when recalculating this book". Settings in this block are not applied to one sheet, but to the book as a whole, that is, to the entire file. Put the checkbox in front of the "Set accuracy as on the screen" parameter and click OK.



3. Now, when calculating the data, the displayed number on the screen will be taken into account, and not the one that is stored in the Excel memory. The setting of the displayed number can be carried out by any of the two methods that we talked above.

Application of functions

If you want to change the value of rounding in the calculation of one or more cells, but do not want to reduce the accuracy of calculations in general for the document, in which case it is best to use the capabilities that the "rounded" function and its various variations, as well as some other functions .

Among the main functions that regulate rounding should be allocated as follows:

• "Rounded" - rounds to the specified number of decimal signs according to the generally accepted rounding rules;
• "District Top" - rounds up to the nearest number up the module;
• "Roundedlice" - rounds up to the nearest number down the module;
• "Rounded" - rounds the number with a given accuracy;
• "OKRWP" - rounds the number with a given accuracy up the module;
• "OKRVNIS" - rounds the number down module with a given accuracy;
• "Otbr" - rounds the data to an integer;
• "Court" - rounds the data to the nearest even number;
• "Challenge" - rounds the data to the nearest odd number.

For the functions of the "rounded", "roundlower" and "roundedlice" uses the following input format: the name of the function (number; number of units). That is, if you, for example, want to round the number 2.56896 to three digits, then apply the function "Rounded (2,56896; 3)". As a result, it turns out the number 2.569.

For the functions "Districtlt", "OKRWP" and "OKRVIS" This is used by such a rounding formula: the name of the function (number; accuracy). So, to round the number 11 to the nearest number, multiple 2, we enter the function "District (11; 2)". The output obtains the result 12.

The "Otbr" functions, "even" and "uniform" use the following format: the name of the function (number). In order to round the number 17 to the nearest even, we use the function "Court (17)". We get the result 18.

The function can be entered both in the cell and in the row of functions, after selecting the cell in which it will be. Before each function should be set "=".

There is a somewhat different way to introduce rounding functions. It is especially convenient to use when there is a table with the values ​​that you need to convert into rounded numbers in a separate column.

1. Go to the tab "Formulas" and click on the "mathematical" button. In the list that opens, select a suitable function, for example, "rounded".



2. After that, the function arguments window opens. In the "Number" field, you can enter a number manually, but if we want to automatically round the data of the entire table, then click on the button to the right of the data introduction window.



3. The argument window of the function is folded. Now click on the upper cell of the column whose data we are going rounded. After the value is entered in the window, click on the button to the right of this value.



4. The function arguments window opens again. In the "Number of Discharges" field, write the bit to which we need to cut the fractions and apply changes.



5. The number rounded. In order to round down and all other data of the desired column, bring the cursor to the lower right corner of the cell with a rounded value, click on the left mouse button, and stretch it down to the end of the table.



6. Now all values ​​in the column will be rounded.

As you can see, there are two main ways to round the visible display of the number: using the tape button and by changing the parameters of the cell format. In addition, you can change the rounding of actually calculated data. It can also be done in different ways: change the settings of the book as a whole or the use of special functions. The choice of a particular method depends on whether you are going to apply a similar type of rounding for all data in the file or only for a specific range of cells.