Monday, March 15, 2010

Spreadsheet rounding functions

What's the difference between the rounding functions: round, rounddown, roundup, floor, ceiling? These functions are available in Excel, Gnumeric, Calc spreadsheets. To rapidly see the differences, we show a table which displays the results of the various functions.

In the column for Number, which was generated as a series, I have to rewrite the integer valued numbers 106, 107, -106, -107 and -105 as pure numbers, not as formula like prev_value + 0.10.



Number floor ceil rounddown roundup round round(.,0) round(.,-1)
105.00 105.00 105.00 105.00 105.00 105.00 105.00 110.00
105.10 105.00 106.00 105.00 106.00 105.00 105.00 110.00
105.20 105.00 106.00 105.00 106.00 105.00 105.00 110.00
105.30 105.00 106.00 105.00 106.00 105.00 105.00 110.00
105.40 105.00 106.00 105.00 106.00 105.00 105.00 110.00
105.50 105.00 106.00 105.00 106.00 106.00 106.00 110.00
105.60 105.00 106.00 105.00 106.00 106.00 106.00 110.00
105.70 105.00 106.00 105.00 106.00 106.00 106.00 110.00
105.80 105.00 106.00 105.00 106.00 106.00 106.00 110.00
105.90 105.00 106.00 105.00 106.00 106.00 106.00 110.00
106.00 106.00 106.00 106.00 106.00 106.00 106.00 110.00
106.10 106.00 107.00 106.00 107.00 106.00 106.00 110.00
106.20 106.00 107.00 106.00 107.00 106.00 106.00 110.00
106.30 106.00 107.00 106.00 107.00 106.00 106.00 110.00
106.40 106.00 107.00 106.00 107.00 106.00 106.00 110.00
106.50 106.00 107.00 106.00 107.00 107.00 107.00 110.00
106.60 106.00 107.00 106.00 107.00 107.00 107.00 110.00
106.70 106.00 107.00 106.00 107.00 107.00 107.00 110.00
106.80 106.00 107.00 106.00 107.00 107.00 107.00 110.00
106.90 106.00 107.00 106.00 107.00 107.00 107.00 110.00
107.00 107.00 107.00 107.00 107.00 107.00 107.00 110.00
-107.00 -107.00 -107.00 -107.00 -107.00 -107.00 -107.00 -110.00
-106.90 -106.00 -106.00 -106.00 -107.00 -107.00 -107.00 -110.00
-106.80 -106.00 -106.00 -106.00 -107.00 -107.00 -107.00 -110.00
-106.70 -106.00 -106.00 -106.00 -107.00 -107.00 -107.00 -110.00
-106.60 -106.00 -106.00 -106.00 -107.00 -107.00 -107.00 -110.00
-106.50 -106.00 -106.00 -106.00 -107.00 -107.00 -107.00 -110.00
-106.40 -106.00 -106.00 -106.00 -107.00 -106.00 -106.00 -110.00
-106.30 -106.00 -106.00 -106.00 -107.00 -106.00 -106.00 -110.00
-106.20 -106.00 -106.00 -106.00 -107.00 -106.00 -106.00 -110.00
-106.10 -106.00 -106.00 -106.00 -107.00 -106.00 -106.00 -110.00
-106.00 -106.00 -106.00 -106.00 -106.00 -106.00 -106.00 -110.00
-105.90 -105.00 -105.00 -105.00 -106.00 -106.00 -106.00 -110.00
-105.80 -105.00 -105.00 -105.00 -106.00 -106.00 -106.00 -110.00
-105.70 -105.00 -105.00 -105.00 -106.00 -106.00 -106.00 -110.00
-105.60 -105.00 -105.00 -105.00 -106.00 -106.00 -106.00 -110.00
-105.50 -105.00 -105.00 -105.00 -106.00 -106.00 -106.00 -110.00
-105.40 -105.00 -105.00 -105.00 -106.00 -105.00 -105.00 -110.00
-105.30 -105.00 -105.00 -105.00 -106.00 -105.00 -105.00 -110.00
-105.20 -105.00 -105.00 -105.00 -106.00 -105.00 -105.00 -110.00
-105.10 -105.00 -105.00 -105.00 -106.00 -105.00 -105.00 -110.00
-105.00 -105.00 -105.00 -105.00 -105.00 -105.00 -105.00 -110.00


We ask the reader to add the INT function to the mix above. What does it do?


  • floor of x function: The greatest integer smaller than or equal to x. round to the left.
  • ceiling of x function: the smallest integer greater than or equal to x.round to the right.
  • rounddown of x function: truncation of x (dropping decimal digits). round towards zero.
  • roundup of x function: round away from zero.
  • round(x,#of digits from decimal point)-if the second argument is zero, will round to the nearest integer. if negative, it will round to the left of the decimal point.


Here is the effect of the round function on various values:
round(2.5) = 3
round(2.4) = 2
round(2.0) = 2
round(-2.5) = -3
round(-2.4) = -2

IN rounding, ignore the sign, then,
a. if the trailing digits are greater than or equal to .5, we add 1 to the integer part.
b. if the trailing digits are smaller than .5, we subtract 1 from the integer part.
Afterwards we put back the sign.

This looks ok now.

No comments:

Post a Comment