Excel rounding numbers. Rounding in Excel is just

Rounding the numbers is a common task, and Excel provides enough functions that round the values \u200b\u200bof different paths (Table 111.1). You must understand the difference between rounding and formatting the value.

When you format the number so that it displays a certain number of semicolons, the formulas that refer to this number are operating with its actual value that may differ from the displayed. When you are rounded the numbers, then the formulas that refer to these values \u200b\u200buse rounded numbers.

Table 111.1. EXCEL rounding functions

Function	Act
OKROV.	Rounds the number in the large side (from zero) to the nearest specified multiple
Ruble.Des.	Converts the price expressed in the form of a fraction, in a decimal number
Ruble. Draft	Converts a price pronounced decimal in fractional number
Thin	Rounds in the biggest (from zero) positive numbers to the nearest even integer; rounds in a smaller side (from zero) negative numbers to the nearest even
Okrvin	Rounds the number in a smaller side (to zero) to the nearest specified multiple
WHOLE	Rounded the number in a smaller side to the whole
District	Rounded the number to the specified multiple
Nothing	Rounds the numbers in the most side (from zero) to the nearest odd whole; rounds at a smaller side (from zero) negative numbers to the nearest odd whole
District	Rounded the number to the specified number of numbers
Districtlnoon	Rounds the number to a smaller side (to zero) to the specified number of numbers
District	Rounds the number in the large side (from zero) to the specified number of numbers
Otbr.	Cuts the number to the specified number of significant digits

The following sections provide examples of formulas that use different types rounding.

Rounding to the nearest multiple number

Function District It is useful for rounding values \u200b\u200bto the nearest multiple. For example, you can use it to round the number to the nearest, multiple 5. The following formula returns 135: \u003d rounded (133; 5).

Rounding monetary values

Often you have to round currency values. For example, the calculated price may be equal to 45.78923 p. In this case, you want to round it up to the nearest penny. This process may seem simple, but to round the values \u200b\u200bof this type you are offered to choose from one of the three following methods:

up to the nearest penny;
in a smaller side to the nearest penny;
before the nearest penny (rounding can be either in large, or at a smaller side).

The following formula suggests that the value of rubles and kopecks is contained in the A1 cell. The formula rounds the value to the nearest penny. For example, if the cell A1 contains 12,421 p., The formula returns 12.42 p.: \u003d Rounded (A1; 2).

If you need to round up the value to the most side to the nearest penny, use the function OKRWP. The following formula, for example, rounds the value in the cell A1 to the nearest penny (if the cell A1 contains a value of 12.421 p., The formula returns 12.43 p.): \u003d OKRWP (A1; 0.01).

To round the ruble value to the smaller side, use the function Okrvin. The following formula, for example, rounds such a value in the cell A1 to a smaller side to the nearest penny (if the cell A1 contains 12.421 p., Then the formula returns 12.42 p.): \u003d Ocline (A1; 0.01).

To round the ruble value up to five kopecks, use this formula: \u003d OKRWP (A1; 0.05).

Using functions whole and sampling

At first glance, functions WHOLE and Otbr. may seem similar. They both convert a value into an integer. Function Otbr. Just removes the fractional part of the number. Function WHOLE Rounds at a smaller side to the nearest integer, based on the value of the fractional part of the number.

On practice WHOLE and Otbr. Return different values \u200b\u200bonly when using negative numbers. For example, this formula returns -14.0: \u003d Otbr (-14,2).

The following formula returns -15.0, since the number -14,2 is rounded in a smaller side until the next integer: \u003d integer (-14,2).

Function Otbr. Accepts an additional (optional) argument that can be useful for truncating decimal values. For example, the following formula returns 54.33 (the value is truncated to two decimal signs): \u003d Otbr (54.3333333; 2).

Rounding to N meaningful numbers

In some situations, it may be necessary to round the value to a certain number of meaningful numbers. For example, you want to express the number 1432,187 in terms of two significant digits (that is, as 1,400,000). The number 84 356 is expressed in three significant numbers as 84,300.

If the value is a positive number without decimal signs, then the next formula will help you. It rounds the number in the cell A1 to two meaningful numbers. For rounding to various quantities Of meaningful digits, replace 2 in this formula you need a number: \u003d rounded area (A1; 2-DLSTR (A1)).

For the neuroze and negative numbers, the solution will be a little more complex. The following formula offers a more general solution, rounding the value in the cell A1 to the number of significant digits specified in the A2 cell. This formula works for positive and negative integers and non-target numbers: \u003d rounded (Al; A2-1-integer (LOG10 (ABS (A1)))).

For example, if the cell A1 contains the number 1.27845, and in the cell A2 it is stored 3, then the formula will return 1.28000 (value round to three significant digits).

In the repair of metal doors, the locks include the repair of door loops, boxes and cloths, which, over time, and due to improper use, can be overjugged and moved. It happens that they simply stop tightly kept in the array, because they are poorly pressing screws, which, in turn, can leave the array due to poorly sliced \u200b\u200bthreads or raw wood, where they were screwed. As a result, after a drying of the material, the holes were wider and the screws came out of them.

- this is working programmincluded in the package Microsoft Office.. It enjoys wide demand, since it is used for various mathematical calculations, drawing up diagrams, creating tables, etc. Today's conversation theme is rounding numerical values \u200b\u200bin the program in order to obtain or fractional part, or accurate. With this feature, the answers will be considered to the following questions:

What ways can be rounded in Excel number?
What formula is used to activate the function?
How to round in a big or smaller side?

Different coincidences

Working in exile with numeric data, sometimes the results of calculations are required to round up to the required number of characters after separation. The program has such an opportunity. Consider how you can do this in several ways:

Rounding in a cell value, but not numbers. This method does not affect the result of calculations, but significantly improves the perception of derived values \u200b\u200bin the Excel document. To do this, select the necessary cells and use the command in the "Home" menu - "number", where the possibilities for reducing / increasing the bit will be presented.
You can also round through the use of the "cell format" button with the transition to the corresponding icon in the "Number" field. Here you should click right-click on the colored cells, and then the desired window opens. You can select the number of decimal signs, that is, the number of decimal values. The procedure itself through formatting in Excel is made according to a conventional mathematical reduction.
Also can help achieve the desired result. The rounding in this way is carried out through the use of the "Formula" command in the field of the Library of Functions, where and the "mathematical" should be selected.

ABOUT last method Let's talk more.

How to use formulas?

IN open Document Create the desired size of the table and fill it. From the cells with numbers that need to be properly reduced, click on the empty cell, and then the cursor put on the "FX" line and click on the corresponding icon. In this line, we put the sign "\u003d" and the word "rounded", after which in parentheses put the address of the location of the desired cell, for example B4, and through a point with a semicolon, which denotes the required number of decimal places to which the data is required to be rounded. It turns out something like this: \u003d rounded (B4; 1). Activate the formula by pressing the "ENTER" key.

If you need to round up to the nearest integer, it is necessary to enter a number 0 after point with a comma, and to enter a negative number to the nearest multiple.

You can still use the aforementioned formula to make a reduction to a short number, which is multiple of the specified digit. To do this, select and click on the cell with the 1st formula, after dragging the small square located in the lower right corner, down. In this way, the specified formula will be copied to other cells.

The rounding function in the Excel editor is mainly used during the formatting of the numbers, which makes this process much easier. The rounding can also be used for integer value and for a certain number of characters.

To begin with, consider rounding the number to defined number Signs using comma.
Do not forget that rounding the numbers should right way. The removal of several characters, located after the comma, will not give the necessary answer, as a result of which your calculations will not fall with the final.

Rounding can be conducted using the built-in functionality of the program. The digit after rounding changes only visually, and the cell retains the original value of the number. Due to this, the calculations will occur without losing information, besides, the user will receive real comfort while working with huge amounts and ultimately will receive the right report.

In the image below, almost all important functions For rounding and the final result.

In order to carry out a rounding process with numbers to several decimal characters, use the instructions below:
- Open any document with data in Excel;
- Locate the tab with formulas on the top panel to the top panel and follow the drop-down list with mathematical functions, after finding out those used for rounding.

The end result will be shown in the field with cell formulas. In order to use the data obtained in the rest of the cells, simply copy them.

Rounding an integer.
To round decimal numbers to whole, you can use several commands that are described in more detail below.

OKRWP - this command roundate an integer value to the whole side of the whole;
- rounded - rounds the specified number, relying on all the rules of mathematics;
- Obrvinis - the command is used to round decimal to the integer values \u200b\u200bat a smaller side of the whole;
- Otbr - the use of this function, leads to the discard of all numbers arranged after the comma, leaving only integer values;
- even - the team rounds to the integer values, until the end result does not get even;
- odd - performs the opposite functions of the Court;
- District - the function carries out a rounding, using the accuracy specified by the user itself in the editor dialog box.

Above the said commands can be used to create integer numbers, according to user guidelines. After determining the command, in the window that opens, you should dial accuracy that is zero. After these actions, the numbers arranged after the comma are not taken into account.

Round up to closest multiples.
Apart from other rounding methods, the Excel editor has the opportunity to round the values \u200b\u200bto the multiple numbers close to it. For this process, the OKRGLKRTN team is responsible, which is available in the category of standard mathematical functions of the editor.

In the command window that opens, we enter the coordinates of the cell, where the desired number is located. In the "Accuracy" paragraph set the value "1" or another option. "Accuracy" is responsible for the very stable, to which we need to bring the specified figure. Below will be shown the value entered into the cell. You can only edit it in the dialog box.

Conclusion
Using above said methods of rounding values \u200b\u200bin the Excel editor, you quickly and beso correctly set up all the testimony in the report, forgetting about eternal errors after the end of working with rounding.

When working with features in Excel, you probably noticed a set of functions Rounded (), OKRWP (), OKRVISNI (), Roundedlesp (), rounded Categories "Mathematical".What is the difference between these functions and how do they work?

Earlier, in the article ", I described the data presentation formats in Excel. Using the view, you can add or shorten the fractional part of the number, but the actual format of the number remains unchanged. Often, when dividing numbers, the fractional part is obtained very long, which can continue to interfere with some calculations and give out the result is not the one that was expected. In order to reduce the fractional part of the number (round), a function is used Rounded (), in which you can specify the required number of discharges after the comma. It is also possible rounding and a whole part.

The rounding occurs as follows:
- If the number is less than 5, the next discharge remains unchanged.
- If the number is equal to or more than 5, then the unit is added to the next discharge.

For example, the number 2553,5675643 after rounding to the hundredths will look 2553.57
In general, rounding in the school program :)

Now consider how the function works Rounded () And what arguments have.

Syntax features simple - Rounded (number; number of discharges) .

Number - This argument indicates a link to a cell with a number that must be rounded or enter a value. Adjusts the use of other functions, the result of which is the number.
Number of discharges - The value of this argument indicates to which discharge it is necessary to round the number. When specifying in this argument a positive number, rounding will occur after the comma. At zero, rounding to the whole part. When specifying a negative argument, a whole part is rounded. At the same time, the number of discharges in the whole part is preserved and filled with zeros.

Example:
Rounded Number 2553,5675643.
Recording feature as follows:

"\u003d Rounded ( 2553,5675643 ;2 ) "return the result 2553,57
"\u003d Rounded ( 2553,5675643;0 ) "Rowsnet 2554
"\u003d Rounded ( 2553,5675643;-2 ) "Rowsnet 2600

Excel also have functions OKRWP (), OKRVISNIS (), rounded (), rounded area (). All these features are almost similar to working with the function. Rounded (), but each of them has a slightly different rounding algorithm.

Function:
OKRWP () - the function rounds the number to the nearest whole, which is multiple of the number specified in the argument " accuracy". Rounding occurs in the biggest.

Examples:
"\u003d OKRWP ( 2553,57;1 ) ", result 2554
"\u003d OKRWP ( 2553,57;3 ) ", Return the result of multiple 3 Ie 2556

For rounding negative numbers, the "accuracy" argument must also be negative, otherwise the function will return the error " #NUMBER!"
"\u003d OKRWP ( -2553,57;-2 ) ", result -2554

And one more example rounding, but already fractional numbers
"\u003d OKRWP ( 0,578;0,02 ) "Result 0,58

OKRVNI () - the function is similar OKRWP (), with the only difference that rounding occurs in a smaller side.
"\u003d Occording ( 2553,57;2 ) "Result 2552

District Top () , Roundlock () - functions on the principle of operation similar to the function Rounded (). The difference is that these functions are rounded with an excess (disadvantage).

Examples:
"\u003d District Top ( 2553,34;1 ) ", the result will turn out 2553,4
"\u003d District Top ( 2553,34;-1 ) ", result 2560
"\u003d Rounded ( 2553,34;1 ) ", result 2553,3
"\u003d Rounded ( 2553,34;-1 ) ", result 2550

Excel is a very powerful editor to work with numeric data. As a rule, they can be both integer and fractional. Quite often for the convenience of the perception of information use rounding functions. In Excel it can be done various methods. Consider them in more detail.

First you need to create a table in which there will be several values. Preferably with a large number of semicolons.

On the toolbar placed special buttonsThanks to which you can change the length of the fractional part. In order to use them, you need to do the following.

Highlight all the cells you want to edit.

Click the Home tab and press the icon specified by the arrow.

As a result, all cells remained only a tenth part. Everything else disappeared.

If you want to cancel the action or increase the number of signs to a hundredth or thousandth shares, you need to click on the next icon.

Thanks to these actions, after the comma will be three characters.

Note: The number of numbers after the comma has become the same for everyone, although it was originally different!

Data format

You can configure the display of values \u200b\u200band using the "Cell Format" window. To do this, do the following.

Select cells, appearance which you want to change.

Make the right mouse click on the selection. In the menu that appears, select "Format cells".

Here you can edit the number of decimal signs with arrows. Or you can immediately specify the desired value. After that, to save the information, it is enough to click on the "OK" button.

As an example, ordinary fractional numbers were shown. But it happens so that you need to work with another type of information.

All other formats use the same principle:

money (no matter what currency (ruble, dollar, etc.) - everything can be adjusted up to a penny);

financial;

interest (when calculating at the end, zeros will be administered);

exponential.

In this regard, in the Excel editor, everything is thought out to the smallest detail.

Setting accuracy calculations

Let us give an example so that you understand what is the difference if you enable / disable this setting.

To begin with, we consider the amount of values \u200b\u200bin all our cells.

To do this, add a new field and move to it. Then I click on the "FX" icon (call insertion window).

In the window that appears, select the category "Mathematical".

Find the "sums" feature. To insert, click on the "OK" button.

A window appears in which you need to insert the function arguments. To do this, it is enough to manually allocate all cells. As a result, they will automatically submit to the desired field.

During the selection process, you will see a dashed frame. At the same time, a large window will disappear. This is done for the convenience of selecting the range of cells, since the table can be huge sizes.

As soon as you release your finger, everything will come back. At the same time, in the "Number 1" field, the desired range will be indicated, and not something that stood by default. To save the data, click on the "OK" button.

Pay attention to the result. This is the sum of the real content of the cells, and not what you see. You can check using the calculator. You will have discrepancies.

You can make sure that the table stores other values \u200b\u200bcompared to the displayed. The fact is that we set up the display of information up to a thousandth share. And on the fact everything remained in place. The same result was if you left rounding to hundredths or tenths.

In order for the "amount" formula, there was a calculation of what is displayed, the following actions should be taken.

Click on the File menu item.

Go to the "Parameters" section.

Click on the item "Optional". After that, scroll through the window almost to the Niza itself.

Find a group of settings "when recalculating this book". Put a check mark near the item "Set the specified accuracy" (if this setting will be unnecessary to you in the future, it will be enough to remove this tick).

Click on the "OK" button. Immediately after that you will see a warning in which you will be reported that the data in the table will be changed in a smaller side. It is necessary to agree with this condition by pressing "OK".

Only after that you can save the changes made.

Look again the result of calculations. Now it has changed and corresponds to the sum of the displayed numbers.

In this case, the numbers in the cells will be changed. That is, the data after three characters will be lost. In the future, they will not be restored.

Therefore, if such accuracy in calculations will use one or several times, you will need not to forget to remove the checkbox from this item in the settings.

Using formulas

In the Excel editor, there is a set of functions, with which any values \u200b\u200bcan be rounded. These include:

Rounded - rounding in the school rules of mathematics to the specified number of decimal signs;
Districtlvops - rounding to the nearest larger number (not according to the rules of mathematics);
Roundalvoon - rounding to the nearest less (also not according to the generally accepted rules);
Round plot - rounding to the nearest number (up or down), which is multiple of the specified value;
OKRWP - analogue of a round plot, only the search for the nearest value to the most side;
Occording is an analogue of a round stream, only the search for the nearest value to a smaller side;
Otbr - this feature simply discardes all the numbers that are standing after the specified number of semicolons;
Even - rounding to the nearest even number;
An odd is rounding to the nearest odd number.

Consider them more and more carefully. Create a table and for each formula, leave a separate line.

So it will be much clearer.

Go to the first cell for this feature. Then click on the "FX" icon.

In the window that appears, select the category "Mathematical". Find there a "rounded" function. Then click on the "OK" button.

After that, an argument entry window will appear for the formula. Click on the first number. As a result, it will automatically substitute to the desired field.

Next, you must specify the number of discharges. For example, you can specify the number "3". To insert the formula, click on the "OK" button.

As a result, you will see a rounded value according to the rules of mathematics.

In order to copy this formula to neighboring cells, you need to hover the cursor to the lower right corner until it becomes in the form of a black cross. After that, without releasing the fingers from the mouse, drag the pointer to the end of the table.

Thanks to these actions, you will receive the following result.

Rules rounding on the school program are quite simple. Consider our first number "1.3548". If we round up to two signs after the comma, then you need to look at the third digit. If it is equal to 5 or more, then the second mark will be per unit anymore. If less than 5 is one sign less. That is, in our case, the result will be the number "1.34". If you have to round up to three characters (as in the case of a table), then the following number "8". And it is clearly more "5". Therefore, our four turned into a five. That is, we got "1.355".

Select the first cell of the second lines and click on the function icon.

Find in the list "Round House". To insert click on the "OK" button.

Then, in the window that appears, you will need to insert the function argument. To do this, click on the specified number in the table.

In the "Number of Suggested" field, specify the number 3. To insert the formula, click on the "OK" button.

We repeat the above-described actions to duplicate data into adjacent columns.

As a result, we get the following.

The results are the same everywhere, but the principle of calculation here is a bit different. To demonstrate in the third group, change one digit. For example, "8" on "2".

Now the number, from the point of view of school mathematics, is rounded in a smaller side. Therefore, the first line was "4.546". Although before that it was "4.547". And the second formula remained unchanged.

The fact is that the functions of the roundflinkle without a difference is the number there - more or less 5. If it is, it means that you need to round with increasing the last digit.

Do the same actions that were described above, only changing to the appropriate function. As a result, get the following data.

In this case, the result has never coincided anywhere, except with a number changed earlier in the third column. The fact is that roundlows simply removes all the numbers that come after the specified number of bit. It doesn't matter what is going on there - "1" or "9". Everything will be removed.

The result coincided with "4,546", as it was just lucky, since the function of the rounded in accordance with the rules of mathematics, seeing that the figure should be less than 5, rounded the value to the smaller side.

We repeat the same actions when adding a function. Only now the second parameter is called otherwise.

Your value will be rounded to the nearest number, which is more painted by what is indicated in the "Accuracy" field. For example, if you put "2", we will see the following results.

It does not matter, in a large or smaller side. The main thing is as close as possible and to be multiple. If you specify the accuracy in the column number "3", the result will be completely different.

Nothing coincided as we changed a multiple value, and the numbers "2" and "3" in this case do not intersect. Simple arithmetic.

We repeat the steps described earlier to add a function into a cell and subsequent duplication of the result. As a result, we obtain the following data.

In this case, for example, for the first value, it was no matter what "0" closer (as it was in the Optional version). This feature is looking for the nearest multiple to the biggest. But the next is the opposite, to a smaller one.

Slip everywhere this formula, as was done in the cases described above.

We see the following: with the indicated accuracy "3" we have everything that is less than this number turned into "0". That is, you can make such a conclusion;

If, when performing the function of the Ocline, the initial value is less than the specified accuracy, then we will always receive zeros, since only they are the least multiple in this situation.

By inserting this feature (as described above), we obtain the same result as with roundlows, since the principle of operation in both situations is the same.

For this function, only a number is used in the formula - without additional arguments. This greatly facilitates its understanding.

Repeat the actions described above and set this formula into all other cells of this string.

The result is quite expected. All numbers have become integers and even. Everything that happened after the comma, immediately disappeared.

Insert this formula in the same way as before. No additional arguments except for the initial number.

Copying the settings of this cell in all the others, we see the following result.

All decimal numbers disappeared again. Only a complete odd number remains.

Conclusion

This article described in detail the basic functions for rounding numbers in the Excel editor. As you can see, there are a lot of them. At the same time, the result is different everywhere, with the exception of the sampling and rounded. Choose what is needed, and not just like that - just to get rid of the numbers after the comma. Because of the wrong choice in the future, large problems may appear in the calculations.

If suddenly you don't succeed, you may use incorrect input values \u200b\u200bor simply confused in the formula description. It is very important for the presence of basic knowledge of mathematics to understand the work of the functions described above.

Video instruction

For those who have left any questions or all attempts when rounding the numbers ended in failure, a video is attached below with additional, more detailed explanations to the actions described above.