Documentation/Calc Functions

    From The Document Foundation Wiki


    Introduction

    The Calc Functions section of The Document Foundation Wiki is maintained by the LibreOffice Documentation Team and is a repository for technical information about the functions that are available to Calc users. These functions are usually accessed through the Function Wizard but may be accessed through other routes.

    The material presented in this section does not replace the corresponding information in the system’s Help files and the Calc Guide. The role of these wiki pages is to provide additional background, explanations, images, drawings, use cases, and data which together provide a more comprehensive technical description of the functions.

    Status

    At the time of writing this Introduction (January 2020), the Calc Functions wiki pages are under development. They will be populated as time allows. Initially each function’s page will include an information box near the top containing the text “Under construction. For now, see the system Help files.” When a page has been fully populated and reviewed, this information box will be removed.

    Unlike the system Help files and Calc Guide, the Calc Functions section of the wiki will not be targeted at a specific build of the LibreOffice software. Historically functions are not usually removed from the software or modified in functionality (other than by bug fixing) and new functions are added as they become available. The wiki pages will be developed to include new functions when volunteer effort is available to do so. At each update the pages will reflect the latest information available.

    Finding Functions

    Each function is described on a separate page of the wiki. To access the page for a specific function, use the dynamic table in the List of functions page. The table allows you to sort and filter by function name and category.

    Structure of function description pages

    The information for each function is presented in a common format under the following headings:

    • Function name
    • Category
    • Summary
    • Syntax
    • Returns
    • Arguments
    • Additional details
    • Examples
    • Related LibreOffice functions
    • ODF standard
    • Equivalent Excel functions

    In order to present a coherent description, a wiki page may repeat some information that is already included in the Help files and Calc Guide.

    Function syntax

    All functions have a similar structure. As a typical example, the structure of a function to find cells in a Calc database that match some search criteria is:

    =DCOUNT(Database; Database field; Search criteria)
    

    A function cannot exist on its own; it must always be part of a formula. Consequently, even if the function represents the entire formula, there must be an = sign at the start of the formula. Where pages within this section of the wiki define the syntax of a function, the = sign has been omitted for convenience. It is included in any example formulas that reference the function.

    Regardless of where a function reference appears in a formula, that reference will start with the function’s name, such as DCOUNT in the example above. By tradition, function names are entered entirely in upper case letters, although Calc will also read them correctly if they are in lower or mixed case. In this section of the wiki, function names are written in upper case for clarity.

    After the name of the function comes a list of its arguments, surrounded by round brackets. If a function has no arguments, then you must still include the round brackets when you use the function in a formula. All listed arguments are required, unless specifically identified as optional. When using a function, its arguments must be entered in the correct order and each argument must be of the expected type. If you put the arguments in a different order, or the arguments are not of the expected type, the function may return an error message or may return an unexpected answer.

    A specific character is used to separate function arguments — this often defaults to a comma or a semicolon (;) but can be changed to meet your own preferences through Tools ▸ Options ▸ LibreOffice Calc ▸ Formula ▸ Separators. In this section of the wiki, the semi-colon is used to separate function arguments.

    Optional arguments are indicated by square brackets around the argument’s name. When referencing a function in a formula, arguments marked as optional can be omitted only when no argument follows. For example, in a function with four arguments, where the last two arguments are marked as optional, you can leave out argument 4 or arguments 3 and 4, but you cannot leave out argument 3 alone. In many cases, if you omit an optional argument Calc will apply a default - this default will be specific to the function.

    Abbreviations

    A List of Abbreviations used within the Calc Functions wiki pages is available.

    Guidelines for updating Calc Functions wiki pages

    Please would those updating pages in this section of the wiki read our Guidelines and Tips before making any changes.