excelExcel 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

ParametersDetails
numbernumber to be rounded. Could be a cell like B2 or a constant like 3.14159
num_digitswhich place to be rounded 2. Omitted or 0 means round to a whole number. 1 or 2 means round to tenths or hundredths. -1 or -3 means round to tens or thousands.
multipleThe multiple to which you want to round number.
significanceThe multiple to which you want to round number.
decimalsThe number of digits to the right of the decimal point. (Optional - defaults to 2)
no_commasA logical value that, if TRUE, prevents FIXED from including commas in the returned text. (Optional - defaults to FALSE)
[ ... ]Parameters in [square brackets] are optional.

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 Cell Formatting for details.