In This Topic

Formula

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.

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.

- Formulas are recalculated when the screen is displayed. (Dynamic calculations such as age calculation are possible by recalculation without data editing)
- More than 300 functions are available.
- The available operators are equivalent to those for Excel.
- Separate fields are used to save calculation results of formulas in kintone.

- If paging is enabled, the values on the pages not displayed on the screen aren't subject to formulas.
- Sorting and filtering can't be applied.
- When a formula is saved in Kintone, a red mark is shown at the upper-right corner of the cell and only the formula can be saved if the value of the formula field isn't equal to the one in the destination field for saving the calculation result.
- The field code should be unique. It isn't possible to set the same field code as that of another field.
- The Kintone restrictions (Kintonen help: What is a field code?) must be observed for characters used as part of the field code.

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.

Basically, formulas work per record.

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

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

RANK(*number*,*array*,*order*)

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.

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.

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.

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

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.

- Specify columns using commas such as "SUM(unit price,quantity,amount)".
- Use the CHOOSE function like "MATCH(1,CHOOSE({1,2,3},unit price,quantity,amount),0)".
- If you do not want to move the columns, uncheck the "Allow Moving Column" option in the General Settings dialog.

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 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

- AVERAGEIF
- COUNTIF
- SUMIF

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