# Floor and Ceiling Functions

Version 2

Description:

Tableau does not have native floor and ceiling functions. A floor function maps a number onto the largest previous integer (the biggest integer to the left), a simple ceiling function maps a number onto the smallest following integer (the smallest integer to the right). A more complex ceiling function such as that used by Excel allows a rounding to a multiple of given number (the significance).

Example Calculations:

```//Floor Function
IF [Value]==INT([Value]) THEN
[Value]
ELSEIF [Value]<0 THEN
INT([Value]-1)
ELSE
INT([Value])
END```
```//Ceiling Function
IF [Value]==INT([Value]) THEN
[Value]
ELSEIF [Value]<0 THEN
INT([Value])
ELSE
INT([Value]+1)
END```
```//Ceiling function that returns value rounded away from 0 to the next multiple of Significance
IF SIGN([Value]) != SIGN([Significance]) THEN
//test for both having same sign
Null
ELSEIF [Value] % [Significance] == 0 THEN
[Value]
ELSEIF [Value] < 0 THEN
-(-INT([Value]/[Significance])-1) * [Significance]
ELSE
(INT([Value]/[Significance])+1) * [Significance]
END```

Inputs and Setup:

[Value]: A number.

[Significance]: The multiple that you want to round to

The original calculations were created by Alex Kerin in  http://community.tableau.com/thread/111530. Joe Mako contributed an additional calc based on the formulas in http://en.wikipedia.org/wiki/Floor_and_ceiling_functions, you can see examples in the attached workbook. Jonathan Drummey added the ceiling w/significance calculation that mirrors Excel's CEILING() function.

Related Functions: