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

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

Precautions in the Use of Formulas

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

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:

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:

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.

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

Tips

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.

Operators

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

• AVERAGEIF
• COUNTIF
• SUMIF

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.