Question

Excel: how to round correctly

Answer 1

Excel's rounding function
ROUNDMULT: a more complex rounding function
Simple rounding: ROUND, EVEN and ROUND, ODD
Edit sheet layout: round only in format

---

Excel: how to round correctly

Not only does Microsoft's spreadsheet help you create easy-to-understand tables and diagrams, it can do tons of calculations for you. The program's own functions and formulas solve complicated operations quickly, if you know how to use them. Here we explain how to use the ROUND function in Excel in the best way..

Index

1. Excel's rounding function
2. ROUNDMULT: a more complex rounding function
3. Simple rounding: ROUND, EVEN and ROUND, ODD
4. Edit sheet layout: round only in format

Excel's rounding function

Excel offers you several functions to round values, but we will start with the simplest: the ROUND function , the way to use it is quite simple. First, you have to indicate the number you want to round. The second argument of the ROUND formula is the number that indicates the number of decimal places to which the value is to be rounded .

  =REDONDEAR(número;núm_decimales)  

The number argument refers to the value to be rounded, which can be inserted into the formula or indicate a reference to another cell ..

With the num_decimals parameter , it is determined from which position to round. If padded with positive values, the number is rounded to the specified number of decimal places . The 0 would not have any decimal places, so it is rounded to the nearest whole number. If instead it is padded with negative numbers, the number is rounded to the left of the separator.

  =REDONDEAR(22,5172;1) = 22,5 =REDONDEAR(22,5172;0) = 23 =REDONDEAR(22,5172;-1) = 20  

The ROUND function can be executed either through the option? Insert function () ?, or by typing it directly in the edit bar. If the second alternative is chosen, keep in mind that the functions start with an equal sign (=) to prevent Excel from processing them as plain text..

With a simple function, you can determine exactly how much you want to round in Excel.

Note

To round with this formula, Excel uses the traditional method: if the figure to be eliminated is 1, 2, 3, or 4, it is rounded down, and if it is 5 or greater, it is rounded up. You can also customize the rounding rule by using the ROUNDUP and ROUNDLESS functions.

To round a value, it is not necessary to use an extra cell, but Excel also allows you to nest functions . In this way, you can combine the ROUND function with other formulas.

  =REDONDEAR(PROMEDIO(17,251;3,4687;7,2513);2) = 9,32  

In this example, the average of multiple values ​​is determined and the result is rounded. Only the rounded result then appears in the cell.

ROUNDMULT: a more complex rounding function

With the ROUNDMULT function you can round a figure to a multiple (the nearest) of another figure. First of all, the figure to be rounded is indicated in the normal ROUND function formula. Again, you can also type the value directly, make a cell reference, or enter a new function in its place. As the second argument, write the value whose multiple you want to round to.

  =REDOND.MULT(número;múltiplo)  

If, for example, you want to present the results only in multiples of 25, you can use the ROUNDMULT function. What determines whether the figures are rounded up or down is the distance to the multiple , since Excel always chooses the shorter one. If there are two multiples at the same distance, it is rounded up .

  =REDOND.MULT(136;25) = 125 =REDOND.MULT(142;25) = 150  

Another example of using the ROUND.MULT function is rounding time values ​​in minutes to whole hours, either up or down.

  =REDOND.MULT(A1;60)/60  

The ROUNDMULT function in Excel allows you to perform more complex rounding tasks.

Simple rounding: ROUND, EVEN and ROUND, ODD

With two simple functions you can round figures to the nearest odd or even number , either with or without decimal places. In the syntax, the only argument is the number to be rounded.

  =REDONDEA.PAR(número) =REDONDEA.IMPAR(número)  

As a general rule, positive numbers are rounded up (3 becomes 4), while negative numbers are rounded down (-1 becomes -2).

Edit sheet layout: round only in format

In Excel you can also format cells to show only a certain number of decimal places. To do this, right-click on the appropriate selection and a context menu will open. Choose? Number format? and then the category ? Number ?, which will allow you to enter the desired number of decimal places. If you enter a 0 in this field, only whole numbers will be displayed. The difference between this process and the ROUND function is that the rounding only affects the format, that is, the presentation : the values ​​are rounded in the table, but behind the scenes? correct numbers are stored, including decimals.

The difference then becomes visible by continuing to trade the numbers . If, for example, the format of the cells has been edited with the values ​​2,4, 2,3 and 3,3 so that they do not show decimals, only the figures 2, 2 and 3 will be seen. When doing the sum of these values, however, Excel will show 8 as the result, since the program still takes into account the hidden decimals. If the ROUND function had been used, the result would be 7.

When formatting cells in Excel, you can also round only the view of values.

---