Skip to main content

Formula Writing Tips

Introduction

In the AITable, formulas can also be used to perform convenient operations and statistics. The difference from excel is that after setting the Field to "Formula" type, you need to enter the operation formula in the configuration list, not in the cell.

The implementation of the smart formula includes the following:

Field The created Field in the current datasheet can be used as a reference field for operations, such as {name}

function Predefined formulas for calculating input values to output values, such as SUM()

operator Arithmetic or logical operators, such as + - * /

parameter Fields or other static data for participating in operations, etc.

Data Sources

When writing smart formulas, there are three main sources of data:

Field Refer to the data in the corresponding cell in the Vigre field, such as SUM({Math score},{Chinese score},{English score})

Manually fill in parameters Enter parameters in the formula, such as "3" in {total score}/3 is a parameter filled in manually

Formula calculation results The result calculated by the formula is used as a parameter again, and the next step is calculated, such as SUM({math score},{Chinese score},{English score})/COUNTA({math score},{Chinese score},{English score })

Quoting Field

When referencing a Field, you need to enclose the field name with curly brackets "{ }", such as {name} , or you can click the field name in the formula configuration panel, and the system will automatically fill in the input box.


Formula editing skills.gif


The formula refers to the Field, and when the field name is updated, the reference name in the formula is automatically updated. But if the Field is removed, the formula will fail.

Writing specification

English parentheses

  • English parentheses "( )" can be used in formulas to change the order of operations, such as ({length} + {width}) / 2

  • The function in the formula must use English parentheses "( )" to wrap the parameters, such as AVERAGE({highest score}, {lowest score})

  • Chinese parentheses (half-width characters) and English parentheses (full-width characters) look very similar, but there are differences, please use them in a standard manner.

English quotation marks

  • To fill in a non-"number" type string in the formula, you need to wrap the "" "" string with English quotation marks. like:
IF({Score} > 60, c"Pass", "Fail")
FIND("Find", "Hello, I am a text to be searched for")
IS_SAME({Deadline}, {Completion Time}, "day")
  • English single quotation marks "' '" are also supported and used in the same way as double quotation marks
IF({Score} > 60, 'Pass', 'Fail')
  • Parameters of type "number" do not need to be enclosed in quotation marks
REPT("ha", 2) u003d> haha

English comma

In the formula, you need to separate parameters with commas ",", such as: AVERAGE(1, 2, 3, 4)

upper and lower case

When entering a function into a formula, letters support uppercase and lowercase, such as:

AVERAGE({highest score}, {lowest score})
average({highest score}, {lowest score})