Sheet Mode / Features Overview / Field Types / Formula
In This Topic
    In This Topic

    The formula is a field format specific to krewSheet.

    In krewSheet, you can perform operations using formulas. Build-in functions and typical mathematical operators can be used to define and perform various operations.

    Using Formula

    To use formulas, select Formula in the field area of the plug-in setting screen and drag it on the sheet area. Any number of formulas can be placed in krewSheet.

    Difference with Kintone Calculation Fields

    Precautions in the Use of Formulas

    Formula Application and Range

    Open the column setting dialog for the formula field and then click on the Edit button to set a formula.

    Double click on a function name or a column name to insert it into the formula text box.

    How Formulas Work

    Basically, formulas work per record.

    Example: When a formula "Unit Price * Quantity" is set to the "Amount" column

    Display Column Name for Range

    Some formulas work on more than one record. For example, the RANK function has the following arguments:


    The second argument expects a range of more than one record. When specifying the name of a column for an argument named "array" , check "Display column names for range" and use a column name appended with "_Array."

    Example: RANK (Amount, Amount_Array)

    Setting this formula lets you achieve the result as shown below:

    Formulas target only values currently shown on the screen. In this example, any amounts not shown on the screen due to paging can't be used in the RANK function.

    Saving Calculation Result of Formula

    By default, the calculation result of a formula is displayed only on the screen. Specifying a destination field for saving the calculation result separately from the formula column lets you save the calculation result in Kintone.

    Specifying Destination Field

    Specify the destination field in the formula column setting dialog on the plug-in setting screen.
    * If there is no particular reason, it's recommended that the destination field not be specified or otherwise specified as "String (single-row)," which enables any errors in the calculation result to be saved as they are.

    How to Save Formulas

    There are several methods to save a formula in the destination filed as shown below:

    Normally, a formula is saved using the Save button on the toolbar. If you want to save only the formula field due to a special formula or setting, a formula can be saved with the context menu displayed by right-clicking on the formula column header.

    1. Edit and save records with the Save button on the toolbar, as with normal fields

    Save the formula for records after editing.

     2. Save with the context menu displayed by right-clicking on the formula column

    When the header is selected to display the context menu, all target records are saved. The corresponding record is saved when the selection is made.

    * When the value of the formula column differs from the one in the destination field, a cell comment is displayed on the cell.

    If a field in which a string can't be saved has been specified as a destination field (such as number, date, and the like), the error information can't be saved in the destination field when any error has occurred in the formula. In this case, even after performing the save operation, the record remains unsaved, and a pencil icon indicating the editing status will be displayed in the row header.

    This function is restricted by the number of records in the app.

     3. Save with the Save Formula button on command bar

    When saving a formula using the Save Formula button, all records in the application, including records that have been filtered out, will be saved.

    The Save Formula button will be displayed only when "Allow Formula Batch Update" is checked under General Settings on the Sheet tab of the ribbon area (see "Disable Features " for more information) and there is a formula field specifying the Kintone field in which the calculation result will be saved.

    If the pencil mark is displayed on the header when a record is edited, please save the record by clicking on the Save button on the command bar first and then click on the Save Formula button.

    • The number of API requests increases as the number of Kintone app records increases (the number of records ÷ 500 (acquiring data) + the number of records ÷ 100 (saving data)).
      Example: When updating an app having 10,000 records: 10,000 ÷ 500 + 10,000 ÷ 100 = 120 requests. Furthermore, if the event handler of the event (app.record.index.edit.submit.success) has been registered after a successful save operation, there will be following API requests: the number of saved records ÷ 500. Particular caution is required, because if this operation is repeated on apps with a large volume of records many times, the number of API requests will increase. With Kintone, the maximum number of API requests per day is 10,000.
    • Formulas in the main sheet and those in the corresponding sheet must be saved separately. If the Save Formula button is clicked when main sheet is active, only the formulas in the main sheet will be saved.

    The table shown below shows various methods to save calculation results of formulas in the destination field.

    Method Where to Right-click Target Row Target Column
    Save Formula button - All records (every record that isn't subject to filtering*) all fields
    Save formula context menu (right-click to save formula) Column All records that have been filtered (the maximum number of records that can be displayed on krewSheet is up to top 10,000 records that fulfill the filtering and sorting conditions) selected fields
    Save formula context menu (right-click to save formula) Selected cell The selected rows selected fields
    Save button on the command bar - Target records for saving (edit records) all fields

    *1. If the number of records in the app exceeds 10,000, the Save Formula button lets you save all of the formulas at once, targeting the top 10,000 records that can be displayed with krewSheet. (This behavior is the same as when executing Save Formula from the context menu of the column header.)

    Other Details


    Some functions let you specify more than one column using the colon symbol (:) in the Range field.

    SUM (unit price: amount)

    After specifying columns in the above mentioned method, when columns are moved by drag and drop during runtime, the desired result can't be achieved because the order of the columns get changed. In order to resolve this, you can take the following measures.


    The following table shows operators that can be used in formulas:

    Binary Operators
    + Add
    - Subtract
    * Multiply
    / Divide
    ^ Exponential
    & Concatenate
    = Equal
    < > Not Equal
    < Smaller Than
    > Greater than
    <= Smaller than or equal
    >= Greater than or equal
    Unary Operators
    - Negative
    + Positive
    % Percentage


    Wildcard Characters

    Wildcard characters can be used in formulas when searching values. They can be used in a formula that has an argument to specify a condition. They can be used only to extract characters and strings that fulfill the specified condition.

    Character To search on Example
    ? (Question mark) Any one character A result by finding with "sm?th" is "smith" or "smyth"
    * (asterisk) Any number of characters A result by searching with "*east" is "Northeast" or "Southeast"
    ~ (tilde) followed by ?, *, or ~ Question mark, asterisk, or tilde The search result with "fy91~?" is "fy91?"

    Functions that Support Wildcards

    Error Value of Result of Operation

    The following error values may be displayed in a cell due to invalid entry or invalid formula:

    Value Description
    #DIV/0! This is displayed when the formula includes a division by 0 or uses a reference to a cell whose value is 0 or an empty cell as a divisor.
    #N/A This is displayed when an entered value is invalid to the function or formula.
    #NAME? This is displayed when text in the formula isn't recognized, the function name is misspelled, or text isn't enclosed in double quotes ("). Also, displayed when colon (:) is missing from the reference to a range of cells.
    #NULL! This is displayed when two ranges of cells do not intersect as expected. A possible cause of this is an error in the typing of a reference operator or cell reference.
    #NUM! This is displayed when a number in the formula or function isn't operable, the result of the formula is too large or too small to express, or an unacceptable argument is passed to the function that needs a figure. This error is also displayed when a solution can't be found when an iterative function like IRR or RATE is used.
    #REF! This is displayed when a cell reference is invalid, or a cell referenced by the formula is deleted.
    #VALUE! This is displayed when the type of an argument or operand is incorrect. For example, when text is passed to the function that needs a figure or a logical value, or when an array is passed to the function that accepts only one argument.

    • krewSheet's function treats the surrogate pair characters as 4-byte and 2-letter characters.
    • The combination of FieldCode_Array and binary operator (e.g., =) doesn't return the array. The operation is done in the same record.
    See Also