Rounding in Excel 2 digits. How to round numbers up and down using Excel functions

Excel uses rounding numbers quite often. At the same time, practice sometimes poses such problems when simple rounding according to mathematical rules does not always help. In this post I will show how to solve such problems using special functions, which not everyone knows about. Moreover, there are very illiterate users who mistake the number format (the visible number of decimal places) for rounding. In general, this article is about correct rounding of numbers in Excel.

The first mistake I just mentioned is using the number format incorrectly to display the desired number of decimal places. This command is indeed often used to give data readable view, and is called either from the tape Home → Number, or through right button Cell Format(which is equivalent to pressing ).

Everything here is intuitive: indicate the number of decimal places and enjoy appearance. However, the type does not affect the accuracy of the number in the cell. Therefore, you should not rely on the format when real rounding is needed.

For real rounding of a number according to mathematical rules, there is a ROUND function. This masterpiece of design ideas rounds a number to a specified number of digits. The ROUND function has the following syntax (set of parameters to be filled):

It's hard to go wrong here. We indicate the number (or link) and set the required precision. However, this is not all that Excel provides in terms of rounding. What, for example, should be done if a number needs to be rounded to thousands (the last three digits are always zeros) or millions (six last digits– zeros)? When I was a completely stupid user, I did something like this. I divided the number to be rounded by 1,000, rounded to a whole number, and then multiplied by 1,000 again. Funny, right? Or do you do this too? If yes, then read on.

When working with large numbers sometimes you need to round up decimal, and digits, that is, make sure that a certain number of characters at the end of the number are always zeros. For example, the number 123,456 needs to be rounded to the nearest thousand, i.e. up to 123,000. Nothing is simpler. This is done using the same ROUND formula, only the number of digits to be rounded is written with a minus. Here's what it looks like for the number 123,456,789.

It's much faster, isn't it? It couldn't be simpler.

Now let's talk about some features of rounding that occur in everyday life.

ROUNDUP and ROUNDDOWN

Sometimes it is necessary to round up or down, regardless of the proximity to the number with the required number of digits (decimal places or zeros at the end of the number). For example, calculated prices are rounded up so as not to reduce the margin, a person's age is rounded down to the nearest whole number to find out the full number of years. In short, the ROUNDUP and ROUNDDOWN functions were invented for these purposes.

These functions have the same parameters as ROUND (referring to the number and number of decimal places).

ROUND

Enough interesting feature ROUND. With its help, you can achieve rounding not only to the desired digit (to the left or right of the decimal point), but generally to the required precision (multiplicity). For example, you need to calculate the order of products in pieces, but so that it is equal to the whole number of boxes. If there are 6 pieces in a box, then the quantity ordered from the supplier must be a multiple of six. Or another example. It is necessary that the settlement price ends in either 00 or 50, that is, it has a multiple of 50 rubles. In both cases, the ROUND function solves the problem in one step.

Very, very correct function. Allows you to avoid 3 intermediate calculations (divide by the multiple, round to a whole number and multiply by the multiple again, as I did myself until I learned about this formula).

OKRVUP.MAT and OKRVBOTTOM.MAT

Rounding with a given precision can also be forced up or down (as ROUNDUP and ROUNDDOWN). For this purpose, there are the functions OKRUP.MAT and OKRVBOTT.MAT. Such names in Excel versions 2013. B previous versions they were called a little differently, but I don’t remember what exactly. The function can be used, for example, when calculating an order, when it is impossible to order less than the required quantity, but at the same time it is necessary to preserve the multiplicity. That is, one “extra” item requires ordering an additional whole box. The syntax (parameters) is the same, so let's move on.

You can also round using other functions. For example, WHOLE, which simply discards the fractional part and thus rounds down to the nearest integer. Used, for example, when you need to calculate the integer number of years between dates (a person's age). Or even the EVEN and ODD functions, which round numbers to the nearest even or odd value, respectively. However, I personally have never used such rounding functions, so it’s difficult to give good advice, where to stick them to benefit. The interested public will have to take a look Excel Help, as everyone usually does.

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 - in big side. Specify the second argument as negative 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) – increments a number to the nearest higher number with the specified number of 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!

Rounding numbers is a common task, and Excel provides plenty of functions that round values ​​in a variety of ways (Table 111.1). You must understand the difference between rounding and formatting a value.

When you format a number to display a specific number of decimal places, formulas that refer to that number operate on its actual value, which may be different from what is displayed. When you round numbers, formulas that refer to those values ​​use the rounded numbers.

Table 111.1. Excel rounding functions

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

Round to the nearest multiple

Function ROUND useful for rounding values ​​to the nearest multiple. For example, you can use it to round a number to the nearest multiple of 5. The following formula returns 135: =ROUND(133,5) .

Rounding monetary values

It is often necessary to round currency values. For example, the calculated price may be equal to 45.78923 rubles. In that case, you'll want to round it up to the nearest penny. This process may seem simple, but to round values ​​of this type, you have the choice of one of the following three methods:

• up to the nearest penny;
• down to the nearest penny;
• to the nearest penny (rounding can be either up or down).

The following formula assumes that the value of rubles and kopecks is contained in cell A1. The formula rounds the value to the nearest penny. For example, if cell A1 contains $12.421, the formula returns $12.42: =ROUND(A1,2) .

If you need to round a value up to the nearest penny, use the function OKRVVERH. The following formula, for example, rounds the value in cell A1 to the nearest penny (if cell A1 contains the value 12.421 rubles, the formula returns 12.43 rubles): =ROUNDUP(A1,0.01) .

To round a ruble value down, use the function OKRVNIZ. The following formula, for example, rounds such a value in cell A1 down to the nearest penny (if cell A1 contains 12.421 rubles, then the formula returns 12.42 rubles): =OCRVDOWN(A1,0.01) .

To round a ruble value to five kopecks, use this formula: =ROADUP(A1,0.05) .

Using the INTEGER and RESTRICTION functions

At first glance, the functions WHOLE And OTBR may seem similar. They both convert the value to an integer. Function OTBR simply removes the fractional part of a number. Function WHOLE Rounds down to the nearest whole number based on the value of the fractional part of the number.

In practice WHOLE And OTBR return different meanings only when using negative numbers. For example, this formula returns -14.0: =RESULT(-14,2) .

The following formula returns -15.0 because -14.2 is rounded down to the next integer: =INTEGER(-14.2) .

Function OTBR takes 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 places): =RESTR(54.3333333,2) .

Rounding to N significant figures

In some situations you may need to round the value to a certain amount significant figures. For example, you want to express the number 1432.187 in terms of two significant figures (that is, as 1400.000). The number 84,356 is expressed to three significant figures as 84,300.

If the value is a positive number without decimal places, then the following formula will help you. It rounds the number in cell A1 to two significant figures. To round to different numbers of significant figures, replace the 2 in this formula with the number you need: =ROUNDBOTTOM(A1,2-LENGTH(A1)) .

For non-integer and negative numbers the solution will be a little more complicated. The following formula offers more general solution, which rounds the value in cell A1 to the number of significant digits specified in cell A2. This formula works for positive and negative integers and non-integers: =ROUND(Al;A2-1-INTEGER(LOG10(ABS(A1)))) .

For example, if cell A1 contains the number 1.27845 and cell A2 stores 3, then the formula will return 1.28000 (the value rounded to three significant figures).

Repairing metal lock doors includes repairing door hinges, frames and panels, which over time and due to improper use can become unstuck and warp. It happens that they simply no longer adhere tightly to the array, because they are poorly pressed by the screws, which, in turn, can come out of the array due to poorly cut threads or damp wood into which they were screwed. As a result, after the material dried, the holes became wider and the screws came out of them.

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 representation to add or reduce the fractional part of a number, but the actual format of the number remains the same. Often, when dividing numbers, fractional part turns out to be very long, which in the future can interfere with some calculations and produce a result that is not the one 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 downwards.
"=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

Let's say you want to round a number to the nearest integer because you don't care about decimal values, or express the number as a power of 10 to make approximate calculations easier. There are several ways to round numbers.

Changing the number of decimal places without changing the value

On a sheet

In built-in number format

Rounding a number up

Round a number to the nearest value

Round a number to the nearest fraction

Rounding a number to a specified number of significant digits

Significant digits are digits that affect the precision of a number.

The examples in this section use the functions ROUND, ROUNDUP And ROUND BOTTOM. They show ways to round positive, negative, integers, and fractions, but the examples given only cover a small portion of the possible situations.

The list below contains general rules, which must be taken into account when rounding numbers to the specified number of significant digits. You can experiment with the rounding functions and substitute your own numbers and parameters to get a number with the desired number of significant digits.

Negative numbers that are rounded are first converted to absolute values ​​(values ​​without a minus sign). After rounding, the minus sign is reapplied. Although it may seem counterintuitive, this is how rounding is done. For example, when using the function ROUND BOTTOM To round -889 to two significant places, the result is -880. First -889 is converted to absolute value(889). This value is then rounded to two significant digits (880). The minus sign is then reapplied, resulting in -880.

When applied to a positive number, the function ROUND BOTTOM it is always rounded down, and when using the function ROUNDUP- up.

Function ROUND rounds up fractional numbers as follows: if the fractional part is greater than or equal to 0.5, the number is rounded up. If the fractional part is less than 0.5, the number is rounded down.

Function ROUND rounds whole numbers up or down in a similar manner, using 5 instead of 0.5 as a divisor.

In general, when rounding a number without a fractional part (a whole number), you need to subtract the length of the number from the required number of significant digits. For example, to round 2345678 down to 3 significant digits, use the function ROUND BOTTOM with parameter -4: =ROUNDBOTTOM(2345678,-4). This rounds the number to 2340000, where the "234" part represents the significant digits.

Round a number to a specified multiple

Sometimes you may need to round the value to a multiple given number. For example, let's say a company ships products in boxes of 18 units. You can use the ROUND function to determine how many boxes will be needed to supply 204 units of an item. IN in this case the answer is 12 because 204 when divided by 18 gives a value of 11.333, which must be rounded up. The 12th box will only contain 6 items.

You may also need to round a negative value to a multiple of a negative, or a fraction to a multiple of a fraction. You can also use the function for this ROUND.