How to round numbers in Excel. Number rounding functions in Excel

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

First, let's look at rounding a number to a certain amount characters using a comma.
Do not forget that numbers should be rounded the right way. Removing several characters after the decimal point will not give the desired answer, as a result of which your calculations will not converge with the final ones.

Rounding can be done using the built-in functionality of the program. The number after rounding changes only visually, and the cell retains the original value of the number. Thanks to this, calculations will occur without loss of information, in addition, the user will receive real comfort while working with huge amounts and ultimately receive a correct report.

The image below shows almost everything important functions for rounding and their final result.

In order to carry out the process of rounding with numbers to several decimal places, use the instructions below:
- Open any document with data in Excel;
- Find on top panel editor tab with formulas and follow the drop-down list with mathematical functions, then find those that are used for rounding.

The final result will be shown in the cell's formula box. In order to use the received data in other cells, simply copy them.

Rounding a whole number.
For rounding decimal numbers to integers, you can use several commands, which are described in more detail below.

OKRVVERH - this command round an integer value to big side from the whole;
- ROUND - rounds the specified number, based on all the rules of mathematics;
- OKRVNIZ - the command is used to round decimal values ​​to integer values ​​downward;
- DROP - using this function leads to discarding all digits located after the decimal point, leaving only integer values;
- EVEN - the command rounds to integer values ​​until the final result is even;
- ODD - performs the opposite functions of EVEN;
- ROUND - the function performs rounding using the precision specified by the user in the editor dialog box.

The above commands can be used to create integer numbers as specified by the user. After defining the command, in the window that opens you should enter the precision, which is equal to zero. After these steps, numbers located after the decimal point are not taken into account.

Round to the nearest multiple.
Apart from other rounding methods, Excel has the ability to round values ​​to close multiples. The OKRGLKRTN team is responsible for this process, which is available in the standard category mathematical functions editor.

In the command window that opens, enter the coordinates of the cell where the required number is located. In the "Accuracy" item, set the value to "1" or another option. “Accuracy” is responsible for the very multiple to which we need to bring the specified figure. The value entered in the cell will be shown below. It can only be edited in the dialog box.

Conclusion
Using the above methods of rounding values ​​in the Excel editor, you will quickly and also correctly adjust all the readings in the report, forgetting about eternal mistakes after finishing work with rounding.

The article discusses how to round a number in Excel using various functions such as ROUND, ROUNDDOWN, ROUNDUP and other rounding methods. Also given are examples of formulas on how to round to a whole number, to tenths, to thousands, to 5, 10 or 100, how to round to a multiple of a number, as well as many other examples.

Round a number into by changing the cell format

If you want round numbers in Excel For visual presentation only, you can change the cell format 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 click right click mouse over the cell and select “Format Cells” in the context menu.

How to round a number in Excel - Format Cells

1. In 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 will it be rounded number will appear in the “Sample” section.
2. Click OK to save your changes and close the dialog box.

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

Pay attention! This method changes display format without changing the actual value stored in the cell. If you refer to this cell in any formulas, all calculations will use the original number without rounding. If you actually need round the 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 is any real number that you want to round. This 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 in this argument:

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

To round the number to tenths , specify a value of 1 in the number_of_bits argument.

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

If necessary round the number to hundredths , set the number_bits argument to 2.

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

In order to round the number to thousandths , enter 3 in number_digits.

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

• If number_places 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, etc.). For example, =ROUND(17.25, -1) rounds 17.25 to the nearest multiple of 10 and returns the result as 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 a few examples, how to round a number in Excel in the ROUND formula:

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

How to round a number up using the ROUNDUP function

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

Syntax:

Number - number to round.

Number_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 described above, except that the number is always rounded up.

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

How to round a number down using the ROUNDDOWN function

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

Syntax:

Number - the number to be rounded.

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

The following image demonstrates, how to round a number in Excel downwards with the ROUND DOWN function in action.

How to round a number in Excel - Examples of formulas on 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 a number in Excel, choose the most suitable one for your needs.

When working with functions in Excel, you probably noticed a set of functions ROUND(), ROUNDUP(), ROUNDBOTTOM(), ROUNDUP(), ROUNDBOTTOM() category "Mathematical".What are the differences between these features and how do they work?

Earlier, in the article "", I described data presentation formats in Excel. You can use the view to add or reduce fractional part numbers, but the actual format of the number remains the same. Often, when dividing numbers, the fractional part turns out to be very long, which can later interfere with some calculations and produce a result that is not what was expected. In order to reduce the fractional part of a number (round), use the function ROUND(), in which you can specify the required number of decimal places. It is also possible to round the whole part.

Rounding occurs as follows:
-If the number is less than 5, then the next digit remains unchanged.
-If the number is equal to or greater than 5, then one is added to the next digit.

For example, the number 2553.5675643 after rounding to the nearest hundredth would be 2553.57
In general, rounding according to the school curriculum :)

Now let's look at how the function works ROUND() and what arguments it has.

The function syntax is simple - ROUND(Number; Number of digits) .

• Number– this argument specifies a reference to the cell with the number that needs to be rounded or enter a value. Allows the use of other functions that result in a number.
• Number of digits- the value of this argument indicates to what digit the number should be rounded. If you specify a positive number in this argument, rounding will occur after the decimal point. If zero, round to the nearest whole fraction. When specifying a negative argument, the integer part is rounded. At the same time, the number of digits in the whole part is preserved and filled with zeros.

Example:
Let's round the number 2553.5675643
The function entry is as follows:

"=ROUND( 2553,5675643 ;2 )" will return the result 2553,57
"=ROUND( 2553,5675643;0 )" will return 2554
"=ROUND( 2553,5675643;-2 )" will return 2600

Excel also has functions ROUNDUP(), ROUNDBOTTOM(), ROUNDUP(), ROUNDBOTTOM(). All these functions are almost identical to working with the function ROUND(), but each of them has a slightly different rounding algorithm.

Function:
OKRVER() - The function rounds the number to the nearest integer that is a multiple of the number specified in the argument " accuracy". Rounding occurs upward.

Examples:
"=OVERTOP( 2553,57;1 )", result 2554
"=OVERTOP( 2553,57;3 )", will return a result that is a multiple of 3 i.e. 2556

To round negative numbers, the precision argument must also be negative, otherwise the function will return an error " #NUMBER!"
"=OVERTOP( -2553,57;-2 )", result -2554

And another example of rounding, but this time for a fractional number
"=OVERTOP( 0,578;0,02 )" result 0,58

OKRVDOWN() - function is similar OKRVER(), the only difference is that rounding occurs downward.
"=OKRVDOWN( 2553,57;2 )" result 2552

ROUNDUP() , ROUNDBOTTOM() - the functions are similar in principle to the function ROUND(). The difference is that these functions round the number up (under).

Examples:
"=ROUNDUP( 2553,34;1 )", the result will be 2553,4
"=ROUNDUP( 2553,34;-1 )", result 2560
"=ROUNDBOTTOM( 2553,34;1 )", result 2553,3
"=ROUNDBOTTOM( 2553,34;-1 )", result 2550

Hello everyone, my dear friends and blog guests. It's been a long time since we picked the famous spreadsheet editor from Microsoft, so I decided to correct this injustice. Specifically, today we will learn how to round a number up or down to a whole number in Excel. In addition, we will be able to round, for example to 2 decimal places, to make it more convenient to count and discard unnecessary “tails”, as is the case with the PI number. Many people do not take this feature seriously, but nevertheless, I think it is very important.

If you don't need to constantly round numbers, then the function that works best for us is "Reduce bit depth". You don’t need to go far, since this button is located right in the feed in the “Home” tab. The button itself represents one and two zeros with a right arrow. Clicking on it will decrease 1 decimal place from the end, and the previous one will be automatically rounded up or down.

Let's look at simple example. Let's say I have a number 5,7777 . By clicking once on the above button, we will see that there are fewer decimal places and it has been rounded, that is, it has become 5,778 . When pressed again, the number will become 5,78 .

Well, a couple more clicks later we will get a rounded figure 6 . What if the number itself were represented as 5,49 , then Excel would round it up to five for us.

Cell Format

TO this method It's worth coming if you want to round numbers to specific columns, rows or ranges of cells. To do this you just need to do a couple of simple steps.

Now, if you write any non-integer number, the program will automatically round it in this range. For example, even if I write 1,5 , then Excel will automatically insert 2 , although formally our real value will still be there.

And here's why:

• First, the real value will be written in the formula bar at the top;
• Secondly, if I sum these two numbers (2+2) using the formula, it will give us 3 , not 4 . And all because the numbers are rounded, and the system continues to count them as 1.5, not 2. But at first glance, it seems that Excel has gone crazy if it thinks that 2+2 will be 3.

How to do rounding in excel using a function?

And of course, the famous spreadsheet editor is famous for its formulas and functions. Therefore, you should not be surprised that with the help of them we can round numbers in Excel. Moreover, there are several such functions, for all occasions. Therefore, I propose to split them into several blocks.

By number of characters

First, they suggest starting with classic rounding. To do this, in one cell we write, for example 6,7836 . And now let's get to the fun part.

Now this cell will contain a ready-made number, namely seven.

By multiplicity

This function is designed to round any numbers that are multiples of the number you need. It doesn’t matter whether it is decimal or integer. Let's say we have the number 19. And now we need to find a value that is a multiple of three. To do this we do the following:

As you can see, now our cell contains 18, since it is the closest of those that are multiples of three. I hope that the essence of this function is clear.

All these things can be done both with single values ​​and with entire columns or rows. Simply select a range of cells by pressing left button mouse and dragging in the desired direction.

I hope that you liked the article and that the methods presented were useful to you. Therefore, be sure to subscribe to the public on VK and to my channel on YouTube. And of course, don’t forget to visit my blog without any reminders. I always try to post something useful, relevant and interesting for you. Good luck to you. Bye bye!

Best regards, Dmitry Kostin.

In the post, I pointed out the great importance of my upcoming article on rounding numbers. Now it's time to explain why. The fact is that many people confuse rounding a number and formatting a number in Excel. In the first case, the number actually changes in accordance with the selected rounding rule. In the second, the number remains the same, only its display on the sheet changes.

Failure to understand these basics can result in erroneous or inaccurate results, so it is critical to learn both rounding and rounding.

Functions for rounding numbers in Excel

The Excel developers have worked very well on the program when it comes to rounding numbers, so now we will look at 10 functions that perform various rounding:

1. Rounding according to the laws of mathematics can be done with the function ROUND( Number; Number of digits) . Moreover, if the first insignificant digit is less than five, the last digit is rounded down. Otherwise, it's upward. Specify a negative second argument to round the places to the left of the decimal point.

1. Round to the nearest multiple perform the function ROUND( Number; Multiplicity)

Round to given direction allow the following four special functions:

1. Function OKRVER( Number; Multiplicity) rounds up a number to a multiple of the second argument

1. OKRVNIZ( Number; Multiplicity) does a similar conversion, but to a smaller extent:

1. ROUNDUP( Number; Number of digits) – increases the number to the next higher one with specified quantity decimal places

1. ROUNDBOTTOM( Number; Number of digits) – reduces a number to the nearest smaller number with the specified number of decimal places

You can also round numbers, making them even or odd, which is implemented in two functions:

1. EVEN( Number) – rounding to the nearest larger even integer. Such rounding for a negative number - on the contrary, we get the nearest smaller even number:

1. ODD( Number) – works similar to the previous function, but makes the given number odd

1. Function WHOLE( Number) – rounds to the nearest lower integer:

1. To simply remove the decimal “tail” of a number, we use the REMOVE function( Number) :

Of course, these are not all the rounding functions that are presented in Microsoft Excel. But these are the most common and universal. In my practice, ten pieces are enough for quality work with numbers that need rounding. You are also unlikely to have to use other functions, but it is advisable to know the ones described here by heart!

This is all about rounding formulas, I will be happy to answer your questions in the comments.

By the way, in the following posts we will study. Come in and read, this functionality is rarely used by ordinary people, although its field of application is very wide, and its capabilities can significantly reduce time costs. You may realize that some tasks that you thought were impossible can still be solved in Excel, and easily!