Rounding in libreoffice calc. Rounding numbers up and down. Rounding functions round(), roundup(), rounddown()

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 bit 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:

Program Microsoft Excel works, including, and with numerical data. When performing division or working with fractional numbers, the program performs rounding. This is primarily due to the fact that absolutely exact fractional numbers are rarely needed, but it is not very convenient to operate with a cumbersome expression with several decimal places. In addition, there are numbers that, in principle, do not exactly round off. But, at the same time, insufficiently accurate rounding can lead to gross errors in situations where precision is required. Fortunately, in Microsoft Excel, it is possible for users to set how numbers will be rounded.

All numbers with which it works Microsoft program Excel, are divided into exact and approximate. Numbers up to 15 digits are stored in memory, and are displayed up to the digit that the user himself indicates. But, at the same time, all calculations are performed according to the data stored in memory, and not displayed on the monitor.

With the rounding operation, Microsoft Excel discards a number of decimal places. Excel uses the conventional rounding method where a number less than 5 is rounded down, and a number greater than or equal to 5 is rounded up.

Rounding with Ribbon Buttons

by the most in a simple way to change the rounding of a number is to select a cell or a group of cells, and being in the "Home" tab, click on the button "Increase bit depth" or "Decrease bit depth" on the ribbon. Both buttons are located in the "Number" toolbox. In this case, only the displayed number will be rounded, but for calculations, if necessary, up to 15 digits of numbers will be involved.

When you click on the "Increase bit depth" button, the number of entered decimal places is increased by one.

When you click on the "Decrease bit depth" button, the number of digits after the decimal point is reduced by one.

Rounding Through Cell Format

You can also set rounding using the cell format settings. To do this, you need to select a range of cells on the sheet, right-click, and select "Format Cells" from the menu that appears.

In the cell format settings window that opens, go to the "Number" tab. If the data format is not numeric, then you need to select the numeric format, otherwise you will not be able to adjust the rounding. In the central part of the window near the inscription "Number of decimal places" simply indicate the number of characters that we want to see when rounding. After that, click on the "OK" button.

Set calculation accuracy

If in previous cases, the set parameters only affected the external display of data, and more accurate indicators (up to 15 digits) were used in the calculations, now we will tell you how to change the very accuracy of the calculations.

The Excel Options window opens. In this window, go to the "Advanced" subsection. We are looking for a block of settings called "When recalculating this book." The settings in this section apply not to a single sheet, but to the entire book as a whole, that is, to the entire file. Put a check next to the "Set accuracy as on screen" option. Click on the "OK" button located in the lower left corner of the window.

Now, when calculating the data, the displayed value of the number on the screen will be taken into account, and not the one that is stored in Excel's memory. Setting the displayed number can be done in any of the two ways that we talked about above.

Application of functions

If you want to change the amount of rounding when calculating relative to one or more cells, but do not want to reduce the accuracy of calculations for the document as a whole, then in this case, it is best to use the opportunities provided by the ROUND function and its various variations, as well as some other features.

Among the main functions that regulate rounding, the following should be highlighted:

ROUND - rounds to the specified number of decimal places, according to generally accepted rounding rules;
ROUNDUP - rounds up to the nearest number up by the modulo;
ROUNDDOWN - rounds down to the nearest number in modulo;
ROUND - rounds a number with a given precision;
ROUNDUP - rounds a number with a given precision up in modulus;
ROUNDDOWN - rounds the number down modulo with the specified precision;
OTBR - rounds the data to an integer;
EVEN - rounds data to the nearest even number;
ODD - rounds the data to the nearest odd number.

For the ROUND, ROUNDUP, and ROUNDDOWN functions, the following input format is: “Function name (number;number_digits). That is, if you, for example, want to round the number 2.56896 to three digits, then use the ROUND(2.56896; 3) function. The output is 2.569.

For the ROUND, ROUNDUP, and ROUNDUP functions, the following rounding formula is used: "Function name (number, precision)". For example, to round the number 11 to the nearest multiple of 2, enter the function ROUND(11;2). The output is 12.

The FIND, EVEN, and ODD functions use the following format: "Function name (number)". In order to round the number 17 to the nearest even number, use the EVEN(17) function. We get the number 18.

A function can be entered both in a cell and in a line of functions, having previously selected the cell in which it will be located. Each function must be preceded by an "=" sign.

There is a slightly different way to introduce rounding functions. It is especially useful when you have a table with values that need to be converted to rounded numbers in a separate column.

To do this, go to the Formulas tab. Click on the "Math" button. Next, in the list that opens, select desired function, for example ROUND.

After that, the function arguments window opens. In the "Number" field, you can enter a number manually, but if we want to automatically round the data of the entire table, then click on the button to the right of the data entry window.

The function arguments window is minimized. Now we need to click on the topmost cell of the column whose data we are going to round. After the value is entered in the window, click on the button to the right of this value.

The function arguments window opens again. In the field "Number of digits" we write the bit depth to which we need to reduce fractions. After that, click on the “OK” button.

As you can see, the number has been rounded. In order to round all other data of the desired column in the same way, hover over the lower right corner of the cell with the rounded value, click on the left mouse button, and drag it down to the end of the table.

After that, all values in the desired column will be rounded.

As you can see, there are two main ways to round the visible display of a number: using the button on the ribbon, and by changing the cell format options. In addition, you can change the rounding of actually calculated data. This can also be done in two ways: by changing the settings of the book as a whole, or by using special functions. The choice of a particular method depends on whether you are going to apply this kind of rounding to all data in the file, or only to a certain range of cells.

When working with tables, it is often necessary to round a number in Excel, for which a number of available mathematical functions are provided. But you need to understand the difference between rounding and formatting a cell value. Let's consider all the nuances in more detail ...

Any numeric value entered in a cell is displayed in the General format (Main menu or cell format). In the case of formatting a number, it displays a certain number of decimal places that can be configured (, cell format). Those. you can set any number of decimal places using formatting (in this case, the number itself in the cell will not change - the display will change).

Rounding functions ROUND(), ROUNDUP(), ROUNDDOWN()

When the data in the cells is used by formulas, the program works with its actual value, which may differ from what we see on the monitor (for example, as in cell B1 in the first picture). The number is rounded using the functions (formulas) ROUND(), ROUNDUP(), ROUNDDOWN().

An interesting function \u003d ROUND (128; 6), in order to round the number "127" to a multiple of "6" in the formula bar, you must write: \u003d ROUND (128; 6), in the final cell we will get the number "126".

Rounding monetary values

Very often, when calculating monetary values in Excel, which use additional calculations, we get numbers with a large number of decimal places. Currency formats provide only two decimal places, so the value must be corrected, rounded in Excel.

To do this, if cell B1 contains a numerical indicator of 10.561 p. (this format can be set by clicking the icon with money in the second picture), to bring the value to the desired one (2 decimal places), it is enough to write in the formula bar: \u003d ROUND (B1; 2), we get the result 10.56 p.

There are times when the value needs to be rounded up or down, for this the following formulas are used:

1. Rounding up, i.e. up: \u003d OKRUP (B1; 0.01), cell B1 will receive the value of 10.57 rubles, rounded up to the next penny (0.01) up
2. Rounding down, down: \u003d OKRVDOWN (B1; 0.01), the cell will receive the value of 10.56 rubles, rounded down to the next penny
3. And if, for example, to round the indicator to 10 kopecks, use the formula: \u003d OKRUP (B2; 0.10)

Convert to integer

In order to get an integer in Excel, use the formulas =INTEG() and =SELECT() . At first glance, they may seem similar, but they are not, especially in negative numbers. When using a formula with the FIND function, only the fractional part of the number is removed.

For example, we have a number - 16.3543, the formula: \u003d OTBR (-16.3543) converts the value to -16, and the formula: \u003d INTEGER (-16.3543) gives an indicator -17, because the integer next number going for "-16.3543" - this is exactly "-17".

Sometimes the OTBR function is used, to truncate decimal places, the formula: = OTBR (16.3555555; 2) gives the indicator "16.35".

How to round a number in Excel up or down

It happens that large numeric values need to be rounded up or down to a certain number of some significant digits. To do this, we use formulas with the function ROUNDUP and ROUNDUP. For example, we have the number 164 358 located in cells B1, the formula: \u003d ROUNDUP (B2; 3-LONG (B1)), converts it to the indicator "165000", the Three in this formula is just the value that is responsible for the number of characters in the conversion. If we replace, for example, it with "2" and write the formula =ROUNDDOWN(B2;2-LONG(A1)), we get the value "160000".

It should be noted that all these formulas work only with positive numbers.

Bank rounding

Very often in accounting programs, such as 1C, banking rounding is used, which Wikipedia says: Bank rounding(eng. banker's rounding) or accounting rounding - rounding here occurs to the nearest even number (if the number ends with 5), that is, 2.5 → 2, 3.5 → 4. To do this, you can use the following functions:

Rounding to even/odd

The =EVEN() function rounds up to the nearest even integer. In this case, positive numbers are rounded up, and negative numbers are rounded down.

The =ODD() function rounds a number to the nearest odd integer. Positive numbers are rounded up, and negative numbers are rounded down.

Share our article on your social networks:

Numeric formatis the most general way of representing numbers and therefore the most common. In the number format, you can use a thousands separator, choose how negative numbers are displayed, and set the number of decimal places.

Types of numbers

In practice, one has to work with numbers of two types, with exact and approximate ones.

Accurate

The data storage and formula conversion supported by Excel is 15 digits. Numeric values are stored with 15-digit precision, and may be displayed differently on the screen, depending on the selected format. Calculations use stored values, not displayed values. When working with different numerical values, you may need different degrees of precision in calculations.

Approximate

There are many problems, the solution of which does not require a large number of decimal places in numerical values. For example, when working with monetary units, two decimal places are enough, and when working with the average number of people, decimal places are not needed at all. Thus, when calculating in Excel, it becomes necessary to round certain calculation results.

What is rounding?

Rounding is a mathematical operation that allows you to reduce the number of decimal places in a number by replacing this number with its approximate value with a certain accuracy.

Exist various ways roundings such as round up, round down, round up modulo, round down modulo, random rounding, alternating rounding, non-zero rounding, banking rounding.

The most common method is mathematical rounding, when a number is rounded down if the "discarded" digit in the number is less than five and rounded up if the "discarded" digit in the number is greater than or equal to five.

Let's dwell on the ways of rounding and displaying numeric values used in Excel.

Changing the number of decimal places displayed without changing the numeric value

This kind of action is performed using the numerical format settings, by reducing the number of decimal places, while the numerical value itself is not rounded, only its display on the monitor screen is rounded. You can make changes to the number format settings using the "Increase bit depth" and "Decrease bit depth" buttons on the Excel 2007 ribbon in the "Home" tab, in the "Number" group.

In other versions of Excel, these buttons look similar. Changes to the number format settings, namely increasing or decreasing the number of displayed decimal places, can be made in the cell format settings. I won't repeat how call the "Format Cells" window different ways since this issue has already been discussed before. Using the rounding method described above can lead to some undesirable consequences, for example, this:

As you can see from the example, incorrect setting number format resulted in 2+3=6, which is incorrect.

Set precision as on screen

It should be noted one more Excel capability, laid down in the Excel options, in the "Advanced" section, in the "When recalculating this workbook" group - this is the item "Set accuracy as on the screen."

*Using the "Set precision as on screen" option changes the numerical values and the calculation precision.

Rounding Numeric Values with Functions

You can use one of several rounding functions to perform rounding of numeric values, which rounds the numeric value itself rather than its display.

ROUND function(number, number_digits) rounds a number to the specified number of decimal places according to the classical rules of mathematical rounding;

ROUNDUP(number,number_digits) function rounds a number with an excess to the nearest higher modulo;

ROUNDDOWN(number,number_digits) function rounds a number with a disadvantage to the nearest smaller modulo;

ROUND function(number, precision) rounds a number up to a multiple of a given number, in the ROUND formula, the "precision" parameter is a multiple to which the result should be rounded.

UP(number,precision) function rounds up a number to the nearest integer or to the nearest multiple of the specified value

Function FLOOR(number,precision) rounds down a number to the nearest integer or to the nearest multiple of the specified value.

*Some versions of Excel do not have the ROUND function, for its appearance, you need to install a standard Excel add-in - "Analysis Pack". If the ROUND function exists in math functions but returns the #NAME? error, try installing the standard Excel add-in, Analysis ToolPak.

In addition to the above functions, standard means Excel has other functions related to rounding, such as

Function EVEN(number) rounds a number to the nearest even integer. In this case, positive numbers are rounded up, and negative numbers are rounded down;

Function ODD(number) rounds a number to the nearest odd integer. Positive numbers are rounded up, and negative numbers are rounded down;

FIND function(number) rounds a number to an integer, discarding the fractional part.

The cell format can predetermine the displayed result of the function, for example, if the number is rounded by the ROUND function to three decimal places, and the cell format specifies a number format with two decimal places, then the cell format will have a determining effect when the result is displayed on the screen.

For those who use standard functions Excel when writing macros in VBA, the site can be helpful http://www.excelfunctions.eu/, where there is a comparison between Russian and English function names with their Russian description.

Examples of using rounding functions

ROUND(1.475,2) Rounds 1.475 to two decimal places, resulting in 1.48;

ROUNDUP(3.14159,3) Rounds 3.14159 up to three decimal places, resulting in 3.142;

ROUNDDOWN(3.14159) Rounds down 3.14159 to three decimal places, resulting in 3.141;

ROUND(10,3) Rounds 10 to the nearest multiple of 3, that is, 9;

UP(2.5,1) Rounds 2.5 up to the nearest higher multiple of 1, that is, up to 3;

FURDOWN(2.5,1) Rounds 2.5 down to the next lower multiple of 1, which is 2.

In order to avoid possible inaccuracies and errors, formats and rounding methods should be carefully selected when forming tables in Excel. If the tables are already formed not quite the way you would like, then the tabular data can be programmatically processed using VBA using macros.

Quickly round numeric values in Excel without using formulas

An add-on that combines all the main rounding methods will help replace numbers in cells with their rounded values, bypassing the step of entering formulas. The user can choose the range and rounding method at his own discretion, the program will do the rest for him.

Add-on features allow you to:

1. With one click of the mouse, call the dialog box directly from the Excel toolbar;

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. Right click on it and from context menu select "Format Cells".

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.

Instructions