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).
//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.