How to round numbers up and down in Excel

From time to time there may be a situation where you need to round numbers. This can be the determination of the closest price in the store, the calculation of the cost of goods after the promotion, payments on a deposit with the function of accumulating small change, and more.

There are several methods to accomplish this task. The first is editing the cell value display form. The second is the use of a function. The difference between these methods is enormous. The cell display type is required in situations where you need to display a small number of characters or print a table. Then it is enough to change the appearance of the cell. It does not change what is actually contained in it.

The second option allows you to use the rounded value in the calculations. It is enough just to enter the appropriate formula, and then this indicator can be applied for a variety of purposes. In this matter, the main thing is not to make mistakes. So let’s look into it in more detail.

How to round a number by setting the cell format?

Let’s open the table and then move the cursor to cell A1. Next, write down the fractional number 76,575 there. After that, right-click with the mouse, and then select the “Format Cells” option. A window will appear. It can also be invoked by pressing Ctrl+1 or from the Home tab (Number tool).

In the window that appears, we are interested in the number format in which we select the number of decimal places that is now required. Let’s imagine that they interfere at all now. Here you can set this value to 0.

After we confirm the changes made, we will have a final value in the cell – 77.

Everything, as we see, is enough to press just a few mouse buttons, and, as if by magic, a rounded number begins to be displayed. But we must remember that it cannot be applied in mathematical calculations. 

How to correctly round a number in Excel

In our case, rounding was carried out in the direction of increase. It depends on the number that is being removed. If there is 5 or more in front of the desired value, then rounding is carried out in the direction of increase, and if it is less, it is rounded down. Everything is as it should be done in mathematics, there are no changes in the rules.

The accuracy of the result depends on how many characters in the fractional part the person decided to leave. The larger it is, the higher the accuracy. Therefore, it is highly recommended that you only round values ​​when there is a real practical need to do so.. Sometimes even the slightest rounding can absolutely skew the calculations. This, by the way, is one of the most common reasons why forecasters are so often wrong. Even the butterfly effect was discovered when, due to minor differences between the rounded value and the present, a rainy season was predicted.

How to round a number up and down?

The most competent way to round in Excel is to use a mathematical function. With its help, you can get real rounding, not visual. The advantage of this approach is that a person can decide for himself in which direction to round off. But until we reveal all the cards, we keep the intrigue. Just a little more, and you will know what actions you need to take in order to achieve this goal.

How to round up to a whole number

As shown in the previous example, it is enough to simply remove the numbers in the fractional part from the formula, as the number immediately becomes an integer. That’s how rounding works! But with the help of a formula, you can get a real integer, and the method described above is a visual one. But the logic does not change depending on whether the real or visual result will be displayed. You still need to put zero characters.

It is also possible to use functions KRUGLVVERH и ROUND DOWNto keep only a round number. Accordingly, the first rounds up, and the second rounds in the opposite direction to the first. In the case of negative values, the opposite is true, because the rounding is carried out modulo 

Why does Excel round large numbers?

In almost any calculator or program, if you enter too large numbers, they are rounded up to the form E + and so on. Excel is no exception. Why is this happening?

If the number contains more than 11 digits, then it is automatically converted to 1,111E+11. This representation of a number is called exponential. It is quite difficult to form such a representation method manually. To do this, you need to calculate the logarithm of the number and perform a few more operations.

If we don’t want Excel to round huge numbers, we need to precede the corresponding value with a ‘. First you need to set the text format. But it will no longer be possible to perform mathematical operations without using special formulas. 

It is also acceptable to enter values ​​as a number with spaces. Excel will automatically convert the cell to text format. It is impossible to implement directly so that the spreadsheet program does not do this. Only through the installation of an apostrophe. 

How to round with Excel function?

And now let’s go directly to practice. What needs to be done to round numbers using a function? There is a special function for this. ROUNDWOOD. It can be called in different ways: through the ribbon in Excel 2007 versions and newer.

The second way is to write by hand. It is more advanced because you need to know the syntax at least.

The easiest method for a beginner is to use the function wizard. To do this, you need to find a button next to the formula input line, on which a combination of small letters fx is written. You can find this function in the “Math” section, and after selecting it, you will be prompted to enter arguments. Each of them is signed, so it’s easy to understand.

ROUND Function Syntax

If manual input is used, then you need to understand how to write the formula correctly. The order in which values ​​are entered is called syntax. Any function has a universal general syntax. First, the equal sign is written, then the name of the function, then the arguments, which are written in brackets, and are separated by a comma. The number of arguments may differ from function to function. In some of them there are none at all, and in a number of them there are at least 5, at least more. 

In the case of the ROUND function, there are two. Let’s look at them in more detail.

ROUND function arguments

So the function has two arguments:

1. Number. This is a cell reference. Alternatively, you can enter the desired value into this argument manually.
2. The number of digits to which you are going to round.
   

   

To round an integer (that is, one that has no decimal places), simply write a minus sign in front of the number in the second parameter. To round to tens, you need to write -1, to hundreds – -2, and follow this logic further. The larger the module of this number, the more digits will be rounded. 

Function basics ROUNDWOOD

Let’s look at how this function can be used, using the example of rounding to thousands.

Imagine we have such a table. We have written the rounding formula in the second cell, and we see the result in this screenshot.

It is possible to round not only a number, but also any value. In the example, it looks like this. Let’s say we have three columns. In the first one, the price of the goods is recorded, in the second – how much it was bought. But in the third, respectively, the final cost is indicated. 

Imagine that our task is to show the amount in rubles, and ignore the penny. Then you get the following table.

By multiplicity

Excel makes it possible to round numbers not to the nearest one, but to one that is a multiple of a certain one. There is a special function for this called ROUND. With its help, you can achieve the required rounding accuracy. 

There are two main arguments. The first is directly the number that needs to be rounded. The second is a number that must be a multiple of the given one. Both arguments can be passed either manually or through a cell. 

By number of characters

All the examples described above are special cases of rounding by the number of characters. It is enough just to enter the required number of characters to be left in the corresponding function argument. Actually, that’s all. 

Rounding up in Excel using the ROUNDUP function

The user can independently set the direction for rounding. Using the function KRUGLVVERH you can remove extra digits or round the whole number up to the one that turns out to be higher.

An example of using this formula can be seen in this screenshot.

The main difference between this function and ROUNDWOOD is that the function always rounds up. If there are any digits of the number, rounding is carried out to a certain number of them.

ROUNDUP Function Syntax

This function takes two arguments. In general, the function looks like this.

=ROUNDLVVERH(76,9)

Now let’s take a closer look at her arguments.

Function Arguments ROUNDUP

The syntax of this function, as we see, is very simple. The arguments are as follows:

1. Number. This is any number that needs rounding.

2. Number of digits. The number of digits to be left after the rounding is done is entered here.

Thus, in syntax, this formula is no different from ROUNDWOOD. The modality of the number determines which numbers will be reduced. If the second argument is positive, then rounding is performed to the right of the decimal point. If it is negative, then on the left. 

Rounding down in Excel using a function ROUND DOWN

This function works in the same way as the previous one. It has the same arguments and syntax, as well as the same usage patterns. The only difference is that rounding is carried out in the downward direction (from a larger number to a smaller one, in other words). Hence the name.

All terms of use are also the same. So, if the second argument (we will give them a little later) is equal to zero, the number is rounded up to an integer. If less than 0, then the number of digits before the decimal point is reduced. If it is greater than zero, then – after. In this way, you can remove a certain number of decimal fractions.

ROUNDDOWN Function Syntax

So, the syntax is absolutely similar to the previous example. Accordingly, it is not particularly different. But if there is such a desire, Excel makes it possible to independently use this function.

First you need to go to the desired document, open the correct sheet and start writing the equal sign in the formula input line. After that, you must directly specify the name of the formula ROUNDDOWN, then enter two arguments.

In general, the formula looks like this.

=RoundString(3,2, 0)

Now let’s take a closer look at what arguments this function has.

Function arguments ROUND DOWN

In this case, the arguments are exactly the same as in the previous version. First you need to specify the numbers that need to be rounded (a single number or a whole range), after which, through a semicolon, specify the number of digits that will be reduced. All other rules are completely similar.

Thus, rounding in Excel is a very simple but useful feature that allows a person to greatly simplify calculations or perception. The main thing is to clearly understand which method and in what specific situations should be used. If we only need to visually display the data (printing is just one of the possible uses), then we need to use the cell format. 

If a person needs to perform full-fledged mathematical operations, then using a function or formula is the only possible option. True, such situations are quite rare. Much more often people, on the contrary, mentally circle.