Home-Internet-How to round numbers in excel. How to round numbers up and down using Excel functions

How to round numbers in excel. How to round numbers up and down using Excel functions

Good day, dear reader!

In this article I want to talk about the possibility rounding numbers intoExcel. Very often you come across the fact that when downloading data from other sources or doing your own calculations you get numbers with a huge amount values ​​after the decimal point, which in turn can negatively affect the final result of the calculation, because you see a number with two decimal places (for example, 98.12), but for the computer it is already a different number (for example, 98.1248247...... ) and, accordingly, he works with what he sees himself, and not what you see. But this option reduces the accuracy of the resulting data, so use the rounding functions in specific and necessary calculations. This feature will become very useful in times when every discrepancy of 1 kopeck already disrupts the balance and harmony in calculations.

How it happens rounding numbers intoExcel Let's look at the four main existing functions:

  1. ROUND function– will allow you to produce rounding numbers intoExcel up to the specified criterion.
  • = ROUND(A1;2) – rounding to the second digit;
  • = ROUND(A1;1) — rounding to the first digit;
  • = ROUND(A1;0) — rounding to an integer value;
  • = ROUND(A1,-1) – rounding to the nearest ten;
  • = ROUND(A1,-2) — round to the nearest hundred;

Reminder! Rounding numbers inExcel is produced, for numbers from 1 to 4 in a smaller direction, and from 5 to 9 in big side. (Example, the number 98.1268... when rounded it will be 98.13, and when rounded to one decimal place it will be 98.1)

2. ROUNDUP function– will allow you to force rounding numbers intoExcel more if it is positive and less if it is negative. The main criterion is how far along the number axis your number is from zero:

  • = ROUNDUP(A1;1) -98.1268 = -98.2
  • = ROUNDUP(A1;1) 98.1268 = 98.2

3. ROUND functionDOWN)- it is forced to produce rounding numbers intoExcel to a lesser extent if it is positive and to a greater extent if it is negative. Here, as in the previous function, the criterion is the same, the value tends to zero along the numerical axis.

  • = ROUNDBOTTOM(A1;1) -98.1268 = -98
  • = ROUNDBOTTOM(A1;1) 98.1268 = 98

4. ODD function– allows you to produce rounding numbers intoExcel to the nearest odd number. Regardless of the signs of the number, rounding will be done excessively. If the number is already odd, no action will be taken.

  • = ODD (A1) 98.1268 = 99

When working with large ranges and using autocopy, you need to use .

The main functions that produce rounding numbers intoExcel We've covered it, now you can apply the knowledge you've gained in practice. You can find other functions in

The article discusses how to round a number in Excel using various functions such as ROUND, ROUNDDOWN, ROUNDUP and other rounding methods. Also given are examples of formulas on how to round to a whole number, to tenths, to thousands, to 5, 10 or 100, how to round to a multiple of a number, as well as many other examples.

Round a number into by changing the cell format

If you want round numbers in Excel For visual presentation only, you can change the cell format by following these steps:

  1. Select the cell with the numbers you want to round.
  2. Open the Format Cells dialog box by pressing Ctrl+1 or click right click mouse over the cell and select “Format Cells” in the context menu.
How to round a number in Excel - Format Cells
  1. In the "Number" tab, select "Numeric" or "Currency" format and enter the number of decimal places you want to display in the "field" Number of decimal places». Preview how will it be rounded number will appear in the “Sample” section.
  2. Click the "OK" button to save your changes and close the dialog box.

How to round a number in Excel - Round a number by changing the cell format

Pay attention! This method changes display format without changing the actual value stored in the cell. If you refer to this cell in any formulas, all calculations will use the original number without rounding. If you actually need round the number in a cell, then use Excel's rounding functions.

How to round a number with the ROUND function

ROUND is the basic number rounding function in Excel which rounds a number to specified quantity signs.

Syntax:

Number is any real number that you want to round. This can be a number or a cell reference.

Number_digits - the number of digits to round the number. You can specify a positive or negative value in this argument:

  • If number_digits is greater than 0, the number is rounded to the specified number of decimal places. For example, =ROUND(17.25, 1) rounds the number 17.25 to 17.3.

To round the number to tenths , specify a value of 1 in the number_of_bits argument.

How to round a number in Excel - How to round a number to tenths

If necessary round the number to hundredths , set the number_bits argument to 2.

How to round a number in Excel - How to round a number to hundredths

In order to round the number to thousandths , enter 3 in number_digits.

How to round a number in Excel - How to round a number to thousandths
  • If num_places is less than 0, all decimal places are removed and the number is rounded to the left of the decimal point (to tenths, to hundreds, to thousands, etc.). For example, =ROUND(17.25, -1) rounds 17.25 to the nearest multiple of 10 and returns the result as 20.
  • If num_digits is 0, the number is rounded to the nearest whole number (no decimal places). For example, =ROUND(17.25, 0) rounds 17.25 to 17.

The following image shows a few examples, how to round a number in Excel in the ROUND formula:

How to round a number in Excel - Examples of formulas on how to round a number using the ROUND function

How to round a number up using the ROUNDUP function

The ROUNDUP function rounds a number up (from 0) to a specified number of digits.

Syntax:

Number - number to round.

Number_digits - the number of digits to which you want to round the number. You can specify either positive or negative numbers in this argument, and it works like the number_digits of the ROUND function described above, except that the number is always rounded up.

How to round a number in Excel - Examples of formulas on how to round a number up using the ROUNDUP function

How to round a number down using the ROUNDDOWN function

The ROUNDUP function in Excel does the opposite of what ROUNDUP does, i.e., it rounds a number down.

Syntax:

Number - the number to be rounded.

Number_digits - the number of digits to which you want to round the number. Works like the number_digits argument to the ROUND function, except that the number is always rounded down.

The following image demonstrates, how to round a number in Excel downwards with the ROUND DOWN function in action.

How to round a number in Excel - Examples of formulas on how to round a number down using the ROUNDDOWN function

This is how it works rounding numbers in Excel . I hope now you know how, among all these ways, how to round a number in Excel, choose the most suitable one for your needs.

When working with tables, there is often a need to round a number in Excel; for this purpose, there are a number of available mathematical functions. 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 into a cell is displayed in the General format (Main Menu or Cell Format). When a number is formatted, it displays a certain number of decimal places that can be customized (cell format). Those. you can specify any number of decimal places using formatting (the number itself in the cell will not change - the display will change).

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

When data in 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). Numbers are rounded using the functions (formulas) ROUND(), ROUNDUP(), ROUNDDOWN().

An interesting function =ROUND (128;6), to round the number “127” to a multiple of “6” in the formula bar you need to write: =ROUND (128;6), in the final cell we get the number “126”.

Rounding monetary values

Very often, when calculating monetary values ​​in Excel, which uses additional calculations, we get numbers with a large number decimal places. Currency formats provide only two decimal places, so the value must be brought into proper form by rounding the number in Excel.

To do this, if cell B1 contains the numerical indicator 10.561 rubles. (this format can be set by clicking the money icon in the second picture), to bring the value to the desired value (2 decimal places), just write in the formula bar: =ROUND (B1;2), we get the result 10.56 rubles.

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

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

Convert to integer

In order to get an integer in Excel, use the formulas =INTEGER() and =RESTRICTION(). At first glance they may seem similar, but this is not the case, this is especially evident in negative numbers. When using a formula with the REMOVE function, only fractional part numbers.

For example, we have the number - 16.3543, the formula: = SELECT (-16.3543) converts the value to the number -16, and the formula: = INTEGER (-16.3543) gives the indicator -17, because the next integer number is for “-16.3543” is exactly “-17”.

Sometimes the TRUN function is used; to truncate decimal places, the formula: = TRIN (16.3555555;2) gives the indicator “16.35”.

How to round a number up or down in Excel

It happens that large digital values ​​need to be rounded to a certain number of some significant figures up or down. To do this, we use formulas with the functions OKRUP and OKRVBOTT. For example, we have the number 164,358 located in cell B1, the formula: =ROUNDUP (B2;3-LENGTH (B1)), converts it to the indicator “165000”. Three in this formula is exactly the value that is responsible for the number of characters in the transformation. If we replace it with “2” for example and write the formula =ROUNDBOTTOM (B2;2-LENGTH(A1)), we get the value “160000”.

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

Bank rounding

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

Round to even/odd

The =EVEN() function rounds 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, negative numbers are rounded down

Share our article on your social networks:

Rounding in Excel is primarily necessary for convenient formatting of numbers.

This can be done either up to an integer value or up to a certain amount decimal places.

Round a number to a certain number of decimal places

It is worth noting that numbers must be rounded correctly. It's not enough to simply remove a few decimal places from a long value.

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

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

In this way, it is possible to carry out calculations without losing data, while the user will be comfortable working with large amounts and drawing up the final report.

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

To commit simple procedure rounding numbers to several decimal places (in in this case– up to 2 characters), 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 the ones that are meant to be rounded as shown in the picture below;

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

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

Rounding to a whole number

To round up decimal number to the whole, you can use several functions at once, namely:

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

Using the above functions, you can create an integer number as per 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 ways to round, such as rounding to a whole number, rounding up, rounding down, etc. In Excel, we can round numbers using functions. We will look at them in this article.

Round numbers in Excel by changing 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 using 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 the section “ Number formats ” click on the item “ Numerical “;
  • In the window menu, find the item “ Number of decimal places ” and indicate exact quantity decimal places you want to leave;
  • Click on the “ OK” to save formatting settings.

Important! This method only visually rounds the number, without “cutting off” the numbers after the decimal point.

Rounding in Excel using the ROUND function

The function is often used in Excel to round numbers. With its help 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 is “0”, the number is rounded 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 based on a mathematical rule by which it determines whether a number should be rounded up or down:

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

The screenshot below shows examples of using the function:


Round up in Excel using the ROUNDUP function

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

Syntax

=ROUNDUP(number,number_of_digits)

Function Arguments

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

Examples of using the function below:

Round down in Excel using the ROUNDDOWN function

Function ROUND BOTTOM rounds a number down, regardless of the number after the decimal point.

Syntax

=ROUNDBOTTOM(number,number_of_digits)

Function Arguments

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

The screenshot below shows examples of using the function:




Related publications: