Rounding in Excel. Rounding number in Excel

The article discusses how to round the number in Excel With the help of various functions, such as rounded, rounded, rounded, and other rounding methods. Also are examples of formulas, how to round up to an integer, up to tenths, up to thousands, to 5, 10 or 100, how to round down a multiple number, as well as many other examples.

Round the number in by changing the cell format

If you want to rounded Numbers in Excel Exceptionally for visual presentation, you can change the cell format by following the following steps:

Select the cell with the numbers you want to roundate.
Open the cell format dialog box by pressing Ctrl + 1 or right-click on the cell and select "Cell Format" in the context menu.

How to round down the number in Excel - cell format

In the "Number" tab, select "Numeric" or "Money" format and enter the number of decimal signs you want to display in the field " The number of decimal signs». Previewas will be rounded Number Displayed in the "Sample" section.
Press the "OK" button to save the changes and close the dialog box.

How to round the number in Excel - rounded the number, change of cell format

Note! This method changes display format Without a change in the actual value stored in the cell. If you refer to this cell in any formulas, then in all calculations will be used in the original number without rounding. If you need actually round out the number In the cell, use Excel rounding functions.

How to round the number of rounded function

The rounded is the main function of rounding numbers in Excel, which rounds the number to the specified number of characters.

Syntax:

The number is any valid number you want to round. It may be a number or link to the cell.

The number of units is the number of signs for rounding the number. You can specify a positive or negative value in this argument:

If the number_ is greater than 0, the number is rounded to the specified number of decimal signs. For example, \u003d rounded (17.25; 1) rounded the number 17.25 to 17.3.

To round up , Specify in the argument the number_nuality value equal to 1.

How to round the number in Excel - how to round the number up to the tenths

If necessary round the number to hundredths , Set the argument number of units equal to 2.

How to round the number in Excel - how to round the number to the hundredths

In order to round up the number to thousandth , Enter 3 in the number of units.

How to round the number in Excel - how to round the number to thousandth

If the number_s are less than 0, all decimal discharges are removed, and the number is rounded to the left of the decimal point (up to the tenths, to hundreds, to thousands, etc.). For example, \u003d rounded (17.25; -1) rounds 17.25 to the nearest multiple 10 number and returns as a result of 20.
If the number of units is 0, the number is rounded to the nearest integer (without decimal signs). For example, \u003d rounded (17.25; 0) rounds 17.25 to 17.

The following image shows a few examples, how to round the number in Excel In the formula rounded:

How to round the number in Excel - examples of formulas, how to round the number, the function of the round

How to round the number in the big side function

The roundfield function rounds the number up (from 0) to a given number of numbers.

Syntax:

The number is the number for rounding.

The number of units is the number of characters to which you want to round the number. You can specify both positive and negative numbers in this argument, and it works as the number of digit functions described above, except that the number is always rounded into the biggest.

How to round the number in Excel - examples of formulas, how to round the number in the big face, the function is rounded

How to round the number in a smaller side of the function rounded

The function of rounded in Excel makes the opposite of what does roundlower, i.e., rounds the number down.

Syntax:

The number is the number to be rounded.

The number of units is the number of characters to which you want to round the number. It works as an argument number of differential function of a rounded function, except that the number is always rounded in a smaller side.

The following image demonstrates how to round the number in Excel In a smaller side, rounded function in action.

How to round down the number in Excel - examples of formulas, how to round the number in a smaller side, the function of rounded

So running rounding numbers in Excel . I hope now you know, as among all these ways, how to round the number in Excel, choose the most suitable for your needs.

Rounding in Excel, first of all, it is necessary for convenient formatting numbers.

This can be done both to the integer value and to certain number decimal places.

Round the number to a certain number of semicolons

It is worth noting that it is necessary to round the numbers correctly. It is not enough to simply take and remove several characters after the comma in a long meaning.

Otherwise, the final calculations in the document will not comply.

You can round down using the built-in program functionality. In fact, the figure is updated only visually, and in the cell there remains real value.

Thus, it is possible to calculate without loss of data, while the user will work comfortably with large sums and make the final report.

The figure shows a list of basic functions rounding and the result of their use:

To make a simple rounding procedure of numbers to several marks after the comma (in this case, up to 2 characters), follow the instructions:

Open the previously used document or create a new one and fill it with the necessary data;
In the Formulas tab, open the drop-down list with mathematical functions and find those that are designed for rounding, as shown in the figure below;

Enter the function arguments and fill in all fields of the dialog box, as shown in the figure;

The resulting function will be written in the box for the cell formulas. To apply it to all the other cells, copy it.

Rounding to an integer

In order to round the decimal number to the whole, you can use several functions at once, namely:

OKRWP - Using this feature, you can round down to an integer value to the whole.
District - rounds the selected number on all canons of mathematical rules;
Okrvin - This is a function that is intended for rounding decimal to an integer value at a smaller side of the whole.
Otbr. - the function drops all the numbers after the comma, leaving only an integer value;
Current - rounding to an integer value until it turns out even;
Odd - the function is opposite to even;
District - rounding with the accuracy that the user itself will indicate in the program dialog box.

Using the above functions, you can create an integer number according to the user's requirement.

After selecting a function, in the dialog box that opens, specify accuracy equal to zero.

Thus, the decimal signs will not be taken into account.

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

Round the number in by changing the cell format

If you want to rounded Numbers in Excel Exceptionally for visual presentation, you can change the cell format by following the following steps:

Select the cell with the numbers you want to roundate.
Open the cell format dialog box by pressing Ctrl + 1 or right-click on the cell and select "Cell Format" in the context menu.

How to round down the number in Excel - cell format

In the "Number" tab, select "Numeric" or "Money" format and enter the number of decimal signs you want to display in the field " The number of decimal signs" Preview how will rounded Number Displayed in the "Sample" section.
Press the "OK" button to save the changes and close the dialog box.

How to round the number in Excel - rounded the number, change of cell format

How to round the number of rounded function

The rounded is the main function of rounding numbers in Excel, which rounds the number to the specified number of characters.

Syntax:

The number is any valid number you want to round. It may be a number or link to the cell.

The number of units is the number of signs for rounding the number. You can specify a positive or negative value in this argument:

If the number_ is greater than 0, the number is rounded to the specified number of decimal signs. For example, \u003d rounded (17.25; 1) rounded the number 17.25 to 17.3.

To round up , Specify in the argument the number_nuality value equal to 1.

How to round the number in Excel - how to round the number up to the tenths

If necessary round the number to hundredths , Set the argument number of units equal to 2.

How to round the number in Excel - how to round the number to the hundredths

In order to round up the number to thousandth , Enter 3 in the number of units.

How to round the number in Excel - how to round the number to thousandth

If the number_s are less than 0, all decimal discharges are removed, and the number is rounded to the left of the decimal point (up to the tenths, to hundreds, to thousands, etc.). For example, \u003d rounded (17.25; -1) rounds 17.25 to the nearest multiple 10 number and returns as a result of 20.
If the number of units is 0, the number is rounded to the nearest integer (without decimal signs). For example, \u003d rounded (17.25; 0) rounds 17.25 to 17.

The following image shows a few examples, how to round the number in Excel In the formula rounded:

How to round the number in Excel - examples of formulas, how to round the number, the function of the round

How to round the number in the big side function

The roundfield function rounds the number up (from 0) to a given number of numbers.

Syntax:

The number is the number for rounding.

How to round the number in Excel - examples of formulas, how to round the number in the big face, the function is rounded

How to round the number in a smaller side of the function rounded

The function of rounded in Excel makes the opposite of what does roundlower, i.e., rounds the number down.

Syntax:

The number is the number to be rounded.

The following image demonstrates how to round the number in Excel In a smaller side, rounded function in action.

How to round down the number in Excel - examples of formulas, how to round the number in a smaller side, the function of rounded

So running rounding numbers in Excel . I hope now you know, as among all these ways, how to round the number in Excel, choose the most suitable for your needs.

- 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.