Rounding numbers in excel to tens. How to round numbers up and down with Excel functions

The article considered how to round number in excel using various functions such as ROUND, ROUNDDOWN, ROUNDUP and other rounding methods. There are also examples of formulas, how to round to a whole number, to tenths, to thousands, to 5, 10 or 100, how to round to a multiple, and many other examples.

Round a number in by changing the cell format

If you want to round numbers in excel purely for visual presentation, you can change the format of a cell by following these steps:

1. Select the cell with the numbers you want to round.
2. Open the Format Cells dialog box by pressing Ctrl+1 or right-click on a cell and select Format Cells from the context menu.

How to round a number in Excel - Format Cells

1. On the Number tab, select Numeric or Currency Format and enter the number of decimal places you want to display in the field. Number of decimal places". preview how it will be rounded number will be displayed in the Sample section.
2. Click the OK button to save your changes and close the dialog box.

How to round a number in Excel - Round a number by changing the cell format

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

How to round a number with the ROUND function

ROUND is the basic number rounding function in Excel that rounds a number to a specified number of decimal places.

Syntax :

Number - any real number you want to round. It can be a number or a cell reference.

Number_digits - the number of digits to round the number. You can specify a positive or negative value for this argument:

• If num_digits is greater than 0, the number is rounded to the specified number of decimal places. For example, =ROUND(17.25, 1) rounds 17.25 to 17.3.

To round number to tenths , set the number_digits argument to 1.

How to round a number in Excel - How to round a number to tenths

If it is needed round up to hundredths , set the number_digits argument to 2.

How to round a number in Excel - How to round a number to hundredths

In order to round number to thousandths , enter 3 in num_digits.

How to round a number in Excel - How to round a number to thousandths

• If num_digits is less than 0, all decimal places are removed and the number is rounded to the left of the decimal point (to tenths, to hundreds, to thousands, and so on). For example, =ROUND(17.25, -1) rounds 17.25 to the nearest multiple of 10 and returns 20.
• If num_digits is 0, the number is rounded to the nearest whole number (no decimal places). For example, =ROUND(17.25, 0) rounds 17.25 to 17.

The following image shows some examples, how to round number in excel in the ROUND formula:

How to round a number in Excel - Examples of formulas, how to round a number using the ROUND function

How to round a number up with the ROUNDUP function

The ROUNDUP function rounds a number up (from 0) to the specified number of digits.

Syntax :

Number - number to round.

num_digits - the number of digits to which you want to round the number. You can specify either positive or negative numbers in this argument, and it works like the number_digits of the ROUND function above, except that the number is always rounded up.

How to round a number in Excel - Examples of formulas, how to round a number up using the ROUNDUP function

How to round a number down with the ROUNDDOWN function

The ROUNDDOWN function in Excel does the opposite of what ROUNDUP does, i.e. it rounds the number down.

Syntax :

Number - the number to be rounded.

num_digits - the number of digits to which you want to round the number. Works like the number_digits argument of the ROUND function, except that the number is always rounded down.

The following image demonstrates, how to round number in excel down with the ROUNDDOWN function in action.

How to round a number in Excel - Examples of formulas, how to round a number down using the ROUNDDOWN function

This is how it works rounding numbers in excel . I hope now you know how among all these ways, how to round number in excel, choose the most suitable for your needs.

Question: how to round numbers in Excel is of interest to many users. It is about him that we will talk.

There are several ways to round numbers in Excel: using the cell format, or using a rounding formula. These two methods should be distinguished. Using the cell format for rounding is more suitable if you just need to display the values ​​in the cell or print the data. Formulas allow you to select the appropriate rounding for a number in a cell, so that it can then be used for other calculations.

Numbers can be rounded up or down, to a specified number of decimal places, to tens, hundreds, and so on. You can round up to an even or odd number, or even discard all digits after the decimal point.

Rounding a Number with Cell Format

The cell format will allow you to select the required number of digits that should be displayed in the number after the decimal point.

We will round the number 23.5168 in cell A1. We click on it with the right mouse button and select "Format cells" from the context menu.

Further, on the "Number" tab from the list "Number Formats" select Numeric . In field "Number of Decimals" set the desired value. Click OK.

The number of decimal places has been reduced to 2. As you can see, the number is rounded according to mathematical laws: if the discarded figure is less than five, the number is rounded down, if it is more than five or equal, it is rounded up.

The value in the cell has changed only visually. The formula bar still contains the number 23.5168, and it will be taken for any calculations in which cell A1 is indicated.

To decrease or increase the number of decimal places, you can use the following method. On the "Home" tab in the "Number" group you will find two buttons "Increase bit depth" And "Reduce bit depth".

Select cell A1 and click once on the button "Reduce bit depth". We get this result.

Functions for rounding in Excel

Rounding a number in a cell using special functions will allow you to use it in the future to calculate formulas.

The functions necessary for calculations are located on the "Formulas" tab in the section "Mathematical". Let's take a look at what each of them does.

Select cell C1 and call the desired function.

ROUND - rounds the number to the specified number of decimal places. In the field "Number" - specify the address of the desired cell, in the field "Number of digits"- Specify the number of decimal places. If you write "0", rounding will occur to an integer.

The following will be written in the formula bar: \u003d ROUND (A1, 3) . A1 - cell reference; 3 is the number of decimal places.

You can round a number in Excel to tens, hundreds, thousands. For this in the field "Number of digits" you need to write -1; -2; -3 respectively.

ROUNDUP - rounds the number up, that is, rounds the value to the next larger number. Recording: .

ROUNDDOWN - rounds down, that is, to the nearest smaller number. Recording: =ROUNDDOWN(number,number of digits).

To round in excel to whole number, you can use any of the above functions. In this case, in the field "Number of digits" you need to write "0". Or enter the given number in the formula bar.

INTEGER - will allow you to round the value to the nearest smaller integer. Record: =INTEGER(number) .

To round a number to the nearest higher integer, use rounding to tens with the ROUNDUP function. Recording: =ROUNDUP(number,number of digits).

ODD - rounds the value to the nearest whole odd number. Moreover, if the value in the cell is positive - up, negative - down. Record: =ODD(number) .

EVEN - rounds the value to the nearest even integer. Record: =EVEN(number) .

OTBR - allows you to discard the fractional part, leaving an integer. According to the laws of mathematics, 23.5168 should have been rounded up to 24, and we have the number 23 - the fractional part is simply discarded. Recording: \u003d REV (number, number of digits).

ROUND - Rounds a number to the specified precision. If you put "7" in the "Precision" field, the number 23.5168 will be rounded up to the nearest number that is a multiple of seven. Recording: =ROUND(number,precision).

Using the methods and various formulas described in this article, you can round a number in Excel the way it is needed in a document: with a certain number of decimal places, up or down, to a whole, to tens and thousands.

Fractional numbers in Excel spreadsheets can be displayed to varying degrees. accuracy:

• most simple method - on the tab " home» press the buttons « Increase bit depth" or " Decrease bit depth»;
• click right click by cell, in the drop-down menu, select " Cell Format...”, then the tab “ Number", select the format" Numerical”, determine how many decimal places there will be after the decimal point (2 decimal places are suggested by default);
• click the cell, on the tab " home» choose « Numerical", or go to " Other number formats...” and configure there.

Here's what the fraction 0.129 looks like if you change the number of decimal places in the cell format:

Please note that A1,A2,A3 have the same meaning, only the form of representation changes. In further calculations, not the value visible on the screen will be used, but original. For a novice spreadsheet user, this can be a little confusing. To really change the value, you need to use special functions, there are several of them in Excel.

Rounding formula

One of the commonly used rounding functions is ROUND. It works according to standard mathematical rules. Select a cell, click the " Insert function”, category “ Mathematical", we find ROUND

We define the arguments, there are two of them - herself fraction And quantity discharges. We click " OK' and see what happens.

For example, the expression =ROUND(0.129,1) will give a result of 0.1. The zero number of digits allows you to get rid of the fractional part. Choosing a negative number of digits allows you to round the integer part to tens, hundreds, and so on. For example, the expression =ROUND(5,129,-1) will give 10.

Round up or down

Excel provides other tools that allow you to work with decimals. One of them - ROUNDUP, gives the closest number, more modulo. For example, the expression =ROUNDUP(-10,2,0) will give -11. The number of digits here is 0, which means we get an integer value. nearest integer, greater in modulus, - just -11. Usage example:

ROUNDDOWN similar to the previous function, but returns the closest value that is smaller in absolute value. The difference in the work of the above means can be seen from examples:

The Microsoft Excel spreadsheet editor is widely used to perform various kinds of calculations. Depending on what kind of task the user faces, both the conditions for performing the task and the requirements for the result obtained change. As you know, when performing calculations, very often the result is fractional, non-integer values, which in some cases is good, while in others, on the contrary, it is inconvenient. In this article, we will take a detailed look at how to round or remove rounding numbers in Excel. Let's figure it out. Go!

To remove fractional values, special formulas are used

Not all users know about the existence of a special rounding function in Excel. It happens that some of them mistakenly take the value format, that is, the displayed number of decimal places, for rounding. Further in the article we will figure out what's what and how everything is done.

To begin with, we note that the "Number Format" function is used in cases where the form of a number needs to be made more readable. Right-click and select "Format Cells" from the list. On the "Numeric" tab, set the number of visible characters in the corresponding field.

But Excel has a separate function that allows you to perform real rounding according to mathematical rules. To do this, you will need to work with the formula field. For example, you need to round the value contained in cell A2 so that there is only one decimal place after the decimal point. In this case, the function will look like this (without quotes): "=ROUND(A2;1)".

The principle is simple and clear. Instead of the cell address, you can immediately specify the number itself. There are cases when it becomes necessary to round up to thousands, millions or more. For example, if you need to make from 233123 - 233000. How to be in this case? The principle here is the same as described above, with the difference that the figure responsible for the number of sections that need to be rounded must be written with a “-” (minus) sign. It looks like this: "=ROUND(233123;-3)". As a result, you will get the number 233000.

If you need to round a number up or down (regardless of which side is closer), then use the ROUNDDOWN and ROUNDUP functions. Call the "Insert function" window. Under "Category", select "Math" and in the list below you will find "ROUNDDOWN" and "ROUNDUP".

Excel also has a very useful ROUND function. Its idea is that it allows you to round up to the required digit and multiplicity. The principle is the same as in the previous cases, only instead of the number of sections, the number is indicated, which will end the resulting number.

In the latest versions of the program, the functions "OKRUP.MAT" and "RIGHT.BOTTOM.MAT" are implemented. They can come in handy if you need to force rounding in any direction with the specified precision.

Alternatively, you can solve the problem using the "INTEGER" function, the principle of which is that the fractional part is simply discarded, rounding down.

Today we are talking about how to properly use the rounding function in Microsoft Office Excel. There are several different ways in which this operation can be performed, however, not all rounding in Excel can lead to the desired result.

Formatting

The first thing you can use to round numbers is the cell format. Let's take a specific example. Let us be given two cells with small values. A1=2.3; A2=2.3. In a separate place, we write a formula that calculates their sum. If we do not use rounding in Excel, then we get that A3 \u003d 4.6. But what happens if we change the cell format?

Select all three cells with values ​​and click on the field with the right mouse button. In the drop-down menu, we are interested in the "Format Cells" item. In the window that opens, select the format - numeric, and the required number of decimal places - 0. Press the "OK" button.

What do we see? Now all three cells have acquired values ​​- 2;2;5. That is 2+2=5. In this case, if you place the cursor on the corresponding cells, you will see the original value of 2.3. This is where the logic of calculations breaks down, with which any first-grader is familiar. The fact is that rounding in Excel adheres to mathematical rules. That is, the number from 1 to 4 is rounded down, and from 5 to 9 - up. Therefore, this command must be used with great care.

Formulas

You can also round in Excel using special commands in formulas. This is very convenient in cases where it is necessary to round one specific value in the calculation, and not the entire cell.

There is a whole set of functions for rounding in Excel.

• ROUND(number; bit depth). In order to use this syntax, you will need to specify two parameters. Number - the value to be rounded. It can be either the value of a single cell or the whole formula. Bit depth - the number of characters after the decimal point.
• ROUNDUP(number; digits). The syntax is similar to the previous command, only it always rounds the number up (for example, 2.1 to 3).
• ROUNDDOWN(number; digits). Rounds down (3.9 to 3).

Thus, the rounding function in Excel allows you to reduce the number of decimal characters. If you've noticed, the ROUND command works exactly the same as formatting a cell into a numeric type, but it allows you to round only part of the expression. For example, B1=ROUND (A1+A2;2)*O3. Thus, the command will only round the sum of two cells to two decimal places, and then multiply the result by the value of cell O3.

whole

Separately, we will consider rounding to integers in Excel. To carry out this operation, you can use both the commands already considered, and some others.

• Format. In order for cells to be automatically rounded to integer values, specify in their format the value - numeric, and the number of decimal places - 0.
• All of the commands in the previous paragraph that use the bit depth parameter can be used as a rounding function in Excel to integers. To do this, you need to specify a bit equal to zero (0).
• UPPER(value; precision). Rounds a number up to a larger value. The precision determines the multiplicity of the result. For example, CIRCUP(12,753,1)=13. But, OCRUP (12,753;2)=14. That is, a number that is a multiple of 2 (divisible by 2 without a remainder).
• FLOOR(number; precision). Similar to the previous command, it only rounds down to a lower number (ROUNDDOWN(12,753;1)=12).
• otbr (number). Discards all decimal places. The mechanics is similar to the previous case with an accuracy of 1.
• ROUND (number; precision). Rounding formula in Excel that works according to mathematical rules. Rounds up to integers with a certain precision, but determines "down" or "up" on its own.
• EVEN (value). Works in the same way as the previous function when specifying the precision - 2, but rounding is done exclusively "up".
• ODD (value). Rounds up to the nearest higher odd number. Example: ODD (12.3231)=13 or ODD (13.7345)=15.

conclusions

As you can see, the rounding function in Excel can be used in a wide variety of variations and cases. If you are afraid to forget what you read in the article, don't worry. The built-in help of the Excel program will always tell you exactly how the command and its arguments should be written. You can always use the operator's insert subroutine.

In the pictures in the article you can see the action of the ROUND operator, which corresponds to the operator we are considering in the English version of the "Office".