Excel rounds large numbers. rounding in excel

Rounding in Excel, first of all, is necessary for convenient formatting of numbers.

This can be done both up to an integer value, and up to a certain number of decimal places.

Round a number to a certain number of decimal places

It is worth noting that the numbers must be rounded correctly. It is not enough to just take and remove a few decimal characters in a long value.

Otherwise, the final calculations in the document will not converge.

You can round off using the built-in functionality of the program. In fact, the number is updated only visually, and the actual value of the number remains in the cell.

Thus, it is possible to carry out calculations without losing data, while the user will be comfortable working with large amounts and compiling the final report.

The figure shows a list of the main rounding functions and the result of their use:

To perform a simple procedure for rounding numbers to several decimal places (in this case, up to 2 decimal places), follow the instructions:

• Open a previously used document or create a new one and fill it with the necessary data;
• In the tab for working with formulas, open the drop-down list with mathematical functions and find those that are designed for rounding, as shown in the figure below;

• Enter the function arguments and fill in all the fields of the dialog box, as shown in the figure;

• The resulting function will be written in the formula field of the cell. To apply it to all other cells, copy it.

Rounding to an integer

In order to round a decimal number to an integer, you can use several functions at once, namely:

• OKRUP- using this function, you can round up to an integer value up from the integer.
• ROUND- rounds the selected number according to all the canons of mathematical rules;
• OKRVNIZ is a function that is designed to round a decimal to an integer value down from the integer.
• OTBR– the function discards all digits after the decimal point, leaving only the integer value;
• EVEN– rounding to an integer value until it is even;
• ODD- function opposite to EVEN;
• ROUND– rounding with the accuracy specified by the user in the program dialog box.

With the help of the above functions, you can create an integer number according to the user's requirement.

After selecting the function, in the dialog box that opens, specify the precision equal to zero.

Thus, decimal places will not be taken into account.

Rounding numbers in Excel is necessary for convenient formatting, forecasting and communication.

There are many rounding methods, such as rounding up to a whole number, rounding up, rounding down, etc. In Excel, we can round numbers using functions. We will consider them in this article.

Rounding numbers in Excel by changing the cell format

If you want to round numbers purely for presentation purposes, you can change the cell format by following these steps:

• Click on a cell with a numeric value;
• Open cell formatting menu with keyboard shortcut CTRL+1 or right-click on the cell and select “ Cell Format“;
• In the pop-up window Cell Format” go to tab “ Number “;
• In chapter " Numeric formats ” click on item “ Numerical “;
• In the window menu, find the item “ Number of decimal places ” and specify the exact number of decimal places that you want to leave;
• Click on the button “ OK” to save formatting settings.

Important! This method only visually rounds the number, not "cutting off" the numbers after the decimal point.

Rounding in Excel with the ROUND function

The function is often used in Excel to round numbers. With it, you can set the number of numbers after the decimal point.

Syntax

=ROUND(number,number_digits)

Function Arguments

• number
• number_digits

Function Basics

• if argument number_digits greater than “0”, the number is rounded to the specified number of decimal places. For example, =ROUND(500,51,1) will return "500.5";
• if argument number_digits equals “0”, the number is rounded up to the nearest whole number. For example, =ROUND(500,51,0) will return "501";
• if argument number_digits less than “0”, the number is rounded to the left of the decimal value. For example, =ROUND(500.51,-1) will return "501".

The function works on the basis of a mathematical rule by which it determines whether a number should be rounded up or down:

• If the number after the decimal point is between 0 and 4, then rounding will be done down;
• If the number after the decimal point is between 5 and 9, then the function will round up.

The screenshot below shows examples of using the function:

Rounding up in Excel using the ROUNDUP function

Function ROUNDUP rounds a number up, regardless of what number comes after the decimal point.

Syntax

=ROUNDUP(number,number_digits)

Function Arguments

• number- the numeric value you want to round;
• number_digits is the decimal place value to which you want to round the first function argument.

Examples of using the function below:

Rounding down in Excel using the ROUNDDOWN function

Function ROUNDDOWN rounds a number down, no matter what number comes after the decimal point.

Syntax

=ROUNDDOWN(number,number_digits)

Function Arguments

• number- the numeric value you want to round;
• number_digits is the decimal place value to which you want to round the first function argument.

The screenshot below shows examples of using the function:

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 initial. 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 amount 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:

When working with numbers in an Excel spreadsheet, it is often necessary to round a number. In most cases, the number is rounded to hundredths, tenths, or whole numbers. Sometimes there are more rare cases when you need to round a number to the nearest multiple.

But, whatever rounding method you need, it can be implemented in Excel. In this article, we will look at 2 main rounding methods, using cell formatting and using formulas. Excel 2010 will be used as an example, but the article will be relevant for users of Excel 2007, 2013 and 2016.

If you need to round the number that is displayed in a cell, but you do not need to change the number itself that is in that cell, then rounding using cell formatting is suitable for you.

This is done very simply, select the cells in which you want to round the numbers with the mouse, and go to the "Home" tab. Here, in the settings block called "Number", there will be two buttons: "Increase bit depth" and "Decrease bit depth". The first button increases the number of characters after the comma that are displayed in the cell, and the second button decreases the number of characters displayed accordingly.

A similar action can be done if you right-click on the cell whose value you want to round and go to "Format Cells".

In the "Cell Format" window, you need to select the number format of the cell, after which you can specify to which decimal place you want to round the number.

As you can see, this method of rounding numbers is very simple. But, you need to remember that in this case only the number displayed in the cell is rounded, while the number that is actually stored in this cell remains without rounding. If you need to round a number stored in a cell, then you need to use formulas to do this. Further we will consider just such a rounding method.

Rounding a number with a formula

To round numbers in Excel, there are the following formulas:

• ROUND - rounds to the nearest value. If you use this formula and at the same time round to a whole number, then 1.5 will be rounded up to 2, and 1.4 to 1.
• ROUNDUP - rounds up. If you use this formula and at the same time round to an integer, then both 1.5 and 1.4 will be rounded up to 2.
• ROUNDDOWN - rounds down. If you use this formula and at the same time round to a whole number, then both 1.5 and 1.4 will be rounded up to 1.
• ROUND - performs rounding to the target multiple. Using this formula, you can round a number so that it becomes a multiple of some given number.

Using these formulas is quite simple. To do this, select the cell in which the rounded number should be located and select this cell with the mouse. After that, you need to enter the formula in this cell as follows:

• First you need to write an equal sign (=) and the name of the formula (for example, ROUND).
• Next, open the parenthesis and specify the address of the cell whose value needs to be rounded (for example, G3).
• Next, put a semicolon (;).
• Next, enter the number of decimal places that you want to get after rounding. If you want to round to a whole number, enter 0.
• After that, close the parenthesis and press Enter to save the typed formula.

If everything was done correctly, then a rounded number will appear in the cell you selected. If you made mistakes when entering the formula, then Excel will inform you about this using a pop-up window.

The screenshot above shows the process of typing the formula. This formula rounds up the number in cell G3 and places the result in cell H3.

How to round a number to an integer in Excel

We will separately consider rounding to an integer, since this task occurs most often. In order to round the existing number to an integer, you can use the formulas ROUND, ROUNDUP or ROUNDDOWN . If you just need to round a number to the nearest whole number, then use the ROUND formula. To round up to a higher whole number, ROUNDUP, and to round down to a lower whole number, ROUNDDOWN.

For clarity, let's look at the whole process step by step. Select the cell where the rounded number should be, and select it with the mouse.

Enter the address of the cell containing the number you want to round to an integer value (in this case, cell G3) and insert a semicolon (;).

As a result, in cell H3 you will get the number from cell G3, but rounded to the nearest integer.

If you need to change the number of displayed zeros after the decimal point, then this can be done using formatting, as described at the beginning of the article.