Rounding numbers

Rounding the numbers in Excel is done automatically. Nevertheless, there is sometimes a need to influence how the rounding is done. This is crucial when we create reports with data expressed in thousands or millions, when decimals represent significant amounts of money. We can do this by adjusting rounding based on how the number is displayed or using rounding numbers.

In Excel numbers are automatically rounded to 15 decimal places. By changing the number display mode, we can increase or decrease the number of decimals you see, but the rounding mode is not changed. For example, the sum of 2.33 and 2.33 is 4.66. If we decide to show a single decimal scale, we will see them as 2,3 and 2,3. Still, although the sum of these two numbers, after the change in the number of decimals, should have been 4.6, we will see 4.7. The reason is that Excel remembers them in their original form, so their collection is 4.66 – rounded up to 4.7!

We can change this by setting the rounding to the number of decimals that we have specified when formatting the number display. To do this, click on the File tab to enter the Backstage View, select Options/ Advanced, and in the When Calculating this workbook group of options select the Set Precision as displayed check box. After selecting this option, rounding is done based on visible decimals. That is, instead of the 4.7 score we would get 4.6, which corresponds to the sum of numbers 2.3 and 2.3. This setting only applies to rounding up in an active workbook.

Rounding can be done with the help of Excel functions. The first function to be called is the INT function, which is used to round down to the nearest integer. Its syntax is:

INT(<number>)

Next, for rounding, we can use the ROUND function. It is more sophisticated and offers us, besides the number that we round up, enter the desired number of decimals. Its syntax is:

ROUND (<number>,<number of decimals>)

There are two more functions similar to the ROUND function. They have the same syntax, and they differ in a way that ROUNDUP returns number being rounded to the first bigger number, for given number of decimals, and ROUNDDOWN to the first smaller number.

For rounding, we can use the TRUNC function, which has the same syntax as ROUND. However, this feature does not make the right round the number. It has the task of “cutting off” the decimal number to the whole part with the given number of decimals. If we do not specify the number of decimals, it is assumed that the result will be an integer numer. Also, ODD and EVEN functions can be used for rounding. They make rounding to the nearest odd (ODD) or the nearest even (EVEN) number.

Finally, let’s mention the functions of FLOOR and CEILING. Both functions have for arguments a number that is rounded up and a degree of significance, which represents a decimal number that we use as a rounding factor. The FLOOR function rounds the number to a lower, and CEILING to a higher number in relation to the rounding factor. At the same time, after the application of the function, the result gets the number of decimals that had a rounding factor. Syntax of these functions:

FLOOR (<number>,<rounding factor>)

CEILING (<number>,<rounding factor>)