Excel rounding and precision

Introduction#

Several Excel formulas deal with rounding and precision of non-integer numbers. This is separate from using cell formatting that affects the display of numeric data. In some cases just using cell formatting is sufficient, but in complex calculations, strict rules for rounding and precision are required to obtain consistent and correct results.

Syntax#

• =ROUND(number, num_digits)
• =ROUNDUP(number, num_digits)
• =ROUNDDOWN(number, num_digits)
• =MROUND(number, multiple)
• =TRUNC(number, [num_digits])
• =INT(number)
• =CEILING(number, significance)
• =FLOOR(number, significance)
• =EVEN(number)
• =ODD(number)
• =FIXED(number, [decimals], [no_commas])

Parameters#

Remarks#

The values displayed to the user can be presented with specific formatting that does not affect the actual data values. For example, displayed data could be formatted as a percentage. See https://stackoverflow.com/documentation/excel/9990/cell-formatting for details.

Using the ROUND function

The ROUND function rounds a value. The number of decimal places to round to is specified by a positive value in the num_digits parameter. A negative value for the num_digits will round the integer portion of the value left of the decimal point, e.g. to the nearest 10 (for -1) or to the nearest 1000 (for -3).

Here’s a table showing how round may be used.

Additional similar functions are also available to control the direction of rounding:

• ROUNDUP - Always rounds a number up, away from zero.
• ROUNDDOWN - Always rounds a number down, towards zero.

Using the TRUNC & INT functions

The excel formula TRUNC is used to truncate a number to a given number of decimal places, specified by the optional num_digits parameter. If this parameter is defined as a negative value it will truncate the integer portion of the value. If the parameter is omitted then the default value is 0 which removes the decimal portion of the number.

The INT function works in a smilar way to TRUNC in that it removes the decimal portion of a number by rounding it down to leave the integer portion. The difference between the two is when performing the operation on a negative number; TRUNC will strip the decimal, however INT will round the value down away from zero.

For example:

=TRUNC(123.456,2)
=TRUNC(123.4357,-1)
=TRUNC(-123.123)
=INT(567.89)
=INT(-567.89)

Will display:

123.45
120.00
-123.00
567.00
-568.00

Using the MROUND function

The Excel function MROUND is used to round a number to an interval other than a power of 10.

These examples show MROUND to the nearest quarter and to the nearest even number.

A similar result can be obtained using the EVEN and ODD functions which round a number up to the nearest even or odd number respectively.

Using the CEILING & FLOOR functions

The CEILING function rounds a number up, away from zero, to the nearest multiple of significance. The FLOOR function does the same by rounds the number down towards zero.

An example of when CEILING could be be used is if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

For example:

=CEILING(2.2, 1)
=FLOOR(2.2, 1)
=CEILING(-4.8, 2)
=FLOOR(-4.8, 2)
=CEILING(0.456, 0.01)
=FLOOR(0.456, 0.01)

Would return:

3
2
-4
-6
0.46
0.45

Using the FIXED Function

The FIXED function rounds a number to the specified number of decimals defined by the decimals parameter, formats the number in decimal format using a comma as a separator unless specified as not required defined by the parameter no_commas, and returns the result as text. The decimals parameter is optional and defaults to two decimal places. The no_commas parameter is also optional and defaults to FALSE.

For example:

=FIXED(1234.567, 1)
=FIXED(1234.567, -1)
=FIXED(1234.567, 1, TRUE)
=FIXED(1234.567)

Would return:

1,234.6
1,230
1234.6
1234.57