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

     


    Comments:

     

    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:

     

     

     

    Further Reading/Examples: