Rounding in Excel
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. We click on it with the right mouse button and select "Format cells" from the context menu.
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.