Overview of Base functions

Help Center

AI Assistant

Help Center AI Assistant is now available

Got questions about Lark? Use our AI chat to find the answers.

Click and hold to drag

Got It

Try Now

Overview of Base functions

18 min read

Date and time functions

Function	Formula	Arguments	Example
DATE function	DATE(year, month, day) Converts integers representing the year, month, and day into a date	Year: Integer representing the year. This can be entered manually or as a field Month: Integer representing the month. This can be entered manually or as a field Day: Integer representing the day. This can be entered manually or as a field	DATE(2000,01,01)=2000/01/01
DATEDIF function	DATEDIF(start date, end date, unit) Returns the number of days, number of months, or number of years between the start date and end date	Start date: This can be a date field, or an integer or formula field formatted as a date type End date: This can be a date field, or an integer or formula field formatted as a date type Unit: An abbreviated string for a unit of time. For example, Y represents the year, M represents the month, and D represents the day	DATEDIF("2000-01-01","2000-01-08","D")=7
DAY function	DAY(date value) The day for a specified date, returned in the format of an integer	Date value: The date from which the specific day is to be extracted	DAY("2000-01-03")=3
DAYS function	DAYS(end date, start date) Returns the number of days between the start date and end date	End date: This can be a date field, or an integer or formula field formatted as a date type Start date: This can be a date field, or an integer or formula field formatted as a date type	DAYS("2000-01-08","2000-01-01")=7
EOMONTH function	EOMONTH(start date, number of months) Returns the number of the last day of the month that is a certain number of months away from the start date.	Start date: The date from which to calculate Number of months: Positive values generate future dates; negative values generate past dates	EOMONTH("2000-1-1", 1)=2000/2/28 EOMONTH("2000-3-1", -1)=2000/02/29
HOUR function	HOUR(time) The hour portion of a specified time, returned in the format of an integer	Time: The time from which the hour is to be extracted	HOUR("11:40:59")=11
MINUTE function	MINUTE(time) The minute portion of a specified time, returned in the format of an integer	Time: The time from which the minute is to be extracted	MINUTE("11:40:59")=40
MONTH function	MONTH(date value) The month of the specified date, returned in the format of an integer	Date value: The date from which the month is to be extracted	MONTH("2000-12-01")=12
NETWORKDAYS function	NETWORKDAYS(start date, end date, [holidays]) Returns the net number of working days between the start date and end date	Start date: This can be a date field, or an integer or formula field formatted as a date type End date: This can be a date field, or an integer or formula field formatted as a date type Holidays: Two-day weekends, by default; date ranges or date fields listed in this function can also be added For details, see Use the NETWORKDAYS function for Base.	NETWORKDAYS("2000-01-01","2000-01-12")=8
NOW function	NOW() Returns the current date and time Note: For bases that are performing calculations on the server, the NOW function is updated every 5 minutes. You can check whether your base is performing calculations on the server by checking the status in the lower-left corner of the Performance Diagnostics window.	/	NOW()=2000/01/01 00:00
SECOND function	SECOND(time) The second portion of a specified time, returned in the format of an integer	Time: The time from which the second is to be extracted	SECOND("11:40:59")=59
TODAY function	TODAY() Returns the current date	/	TODAY()=2000/01/01
WEEKDAY function	WEEKDAY(date value, [type]) Returns the day of the week for the specified date, returned in the format of an integer	Date value: The target date in the week that needs to be returned. This can be a date field or an integer or formula field formatted as a date type Type: The day of the week on which Day 1 of a week starts. If it is 1, then Day 1 of a given week starts on Sunday; if it is 2, then it starts on Monday. The default is 1	WEEKDAY("2000-01-01")=7
WEEKNUM function	WEEKNUM(date, [type]) Returns the week number for the specified date in the current year	Date: The target date for which the week number is returned. This can be a date field or an integer or formula field formatted as a date type Type: The day of the week on which Day 1 of a week starts. If it is 1, then Day 1 of a given week starts on Sunday; if it is 2, then it starts on Monday. The default is 1	WEEKNUM("2000-01-01")=1
WORKDAY function	WORKDAY(start date, number of days, [holidays]) Returns the end date based on the specified start date and the number of working days	Start date: The start date needed for the calculation. This can be formatted as a function with the date type or an integer field Number of days: The number of working days needed beginning on the start date Holidays: Two-day weekends, by default, along with ranges or array constants listed in this function	WORKDAY("2000/01/01",7)=2000/01/11
YEAR function	YEAR(date value) The year portion of a specified date, returned in the format of an integer	Date value: The date from which the year is to be extracted	YEAR("2000-01-01")=2000
DURATION function	DURATION(number_of_days, [number_of_hours], [number_of_minutes], [number_of_seconds]) Returns a date after adding or reducing the specified number of days, hours, minutes, or seconds	Number of days: The number of days in the duration Number of hours: The number of hours in the duration Number of minutes: The number of minutes in the duration Number of seconds: The number of seconds in the duration	Example: Return the date after adding 12 hours to the current time Formula: NOW()+DURATION(0, 12) =2023/06/05 22:00 Example: Return the date of the day before yesterday at 21:30 Formula: TODAY()-DURATION(1, 2, 30) =2023/06/03 21:30

Logical functions

Function	Formula	Arguments	Example
AND function	AND(logical expression 1, [logical expression 2, ...]) When all arguments are TRUE, TRUE is returned; if any one of the arguments is FALSE, FALSE is returned	Logical expression 1: A logical expression or a reference to a field containing an expression, representing a logical value (TRUE or FALSE) Logical expression 2: Additional expressions of logical values	AND(1=1,1=2)=false
CONTAIN function	CONTAIN(find range, [value to find, ...]) Assesses whether the find range contains the content to find Note: This function does not support contained text matching. For that, please use the CONTAINTEXT function	Find range: The range to look through. This can be a numerical value or an array Value to find: A numerical value or list to find For details, see CONTAIN function.	Example: Return Asia Pacific if the sales location is Japan or South Korea Formula: IF(CONTAIN([sales territory],"South Korea", "Japan"), "Asia-Pacific market", "Other")
FALSE function	FALSE() Returns the logic value FALSE	/	FALSE()=false
IF function	IF(evaluation condition, returned value when TRUE, [returned value when FALSE]) Returns one value when the evaluation condition is TRUE, and returns a different value when it is FALSE	Evaluation condition: The condition to be evaluated. You can manually enter a logical expression, and you can also reference a field containing an expression, representing a logical value (TRUE or FALSE) Returned value when TRUE: The value to return when the evaluation condition is TRUE Returned value when FALSE: The value to return when the evaluation condition is FALSE For details, see IF and IFS function.	IF(1=2, "Same", "Different")=Different
IFBLANK function	IFBLANK(value, returned value if blank) Checks whether a target value is blank. If it is not blank, then the value itself is returned; otherwise, the value of the second argument is returned	Value: The value to return if the "value" itself is not blank Returned value if blank: The value to return if the "value" is blank For details, see ISBLANK and IFBLANK functions.	IFBLANK([Member name], "Unfilled")=Member name Note: If the member name is blank, then Unfilled is returned
IFERROR function	IFERROR(value, returned value if error) Checks whether the target value has an error. If there is no error, then the value itself is returned; otherwise, the value of the second argument is returned	Value: The value to be evaluated and the value to return if there is no error Returned value if error: The value to return if there is an error For details, see ISERROR and IFERROR functions.	IFERROR(2/0, "Error")=Error Note: If value is not an error, the value itself will be returned. If the value is an error, the specified value will be returned.
IFS function	IFS(Condition 1, Value 1, [Condition 2, ...], [Value 2, ...]) Assesses whether one or multiple conditions are satisfied and returns the value corresponding to the first TRUE condition	Condition 1: The first condition to be evaluated. This can be a logical value, a numerical value, an array, or a reference to such values Value 1: The value to return when Condition 1 is TRUE Condition 2: Other condition(s) to be evaluated when the previous condition is FALSE Value 2: The other value to return when the corresponding condition is TRUE For details, see IF and IFS function.	IFS(A>=80,"Excellent",A>=60,"Pass",TRUE,"Fail") Note: If the value for A is greater than 80, then Excellent is returned; if it is greater than 60 , then the result is Pass for all other situations, the result is Fail.
ISBLANK function	ISBLANK(value) Checks whether the target value is blank. If it is blank, the result is true; otherwise, it is false	Value: The value to be evaluated. Support manual input	Example: Evaluate whether the member name is blank Formula: ISBLANK([Member name])
ISERROR function	ISERROR Checks whether a certain value is an error value	Value: The value to be evaluated	ISERROR(Value) Note: If value is an error, true is returned. If value is not an error, false is returned.
NOT function	NOT(logical function) Returns the opposite value of the specified logical value	Logical function: Any value or expression that returns a TRUE or FALSE value	NOT(TRUE)=false
OR function	OR(logical expression 1, [logical expression 2, ...]) When all arguments are TRUE, TRUE is returned; if any one of the arguments is FALSE, FALSE is returned Returns TRUE when any one of the specified arguments is true; returns FALSE when all arguments are false	Logical expression 1: An expression or a reference to a cell containing an expression. This expression represents a logical value (TRUE or FALSE) or can be coerced to a logical value Logical expression 2: Additional expressions for which the calculated result is a logical value	OR(1=2, 1=1)=true
RECORD_ID function	RECORD_ID() Accesses a unique ID number recorded in a base	/	RECORD_ID()
SWITCH function	SWITCH(expression, expression 1, value for expression 1, [expression 2 or default value, ...], [value for expression 2, ...]) Compares an expression against other expressions, returning specified values based on matching conditions	Expression: The expression to evaluate against the conditions. This can be a field in a Base Expression 1: The first situation that needs to be compared to the expression Value for expression 1: The result returned if expression 1 is matched Expression 2 or default value: Other expressions to evaluate when the previous expression has no match, or the default value returned when there are no matching conditions Value for expression 2: The result returned if expression 2 is matched	SWITCH([serial number],1,"Sunday",2,"Monday",3,"Tuesday","None") Note: If the serial number is 1, then the result returned is Sunday; if the serial number is 2, then the result returned is Monday; if the serial number is 3, then the result returned is Tuesday; for all other situations, the result is None
TRUE function	TRUE() Returns the logical value TRUE	/	TRUE()=true
CONTAINSALL function	CONTAINSALL(target_range, [target_value, ...]) Checks if the target range contains the target value	Target range: A value or a list of values in which to search Target value: Specified value or array	IF(Selected.CONTAINSALL("American Literature", "Linguistics", "British Culture"), "Yes", "No") Note: If "Selected" contains "American Literature", "Linguistics", and "British Culture", then "Yes" is returned. If not, "No" is returned.
CONTAINSONLY function	CONTAINSONLY(target_range, [target_value, ...]) Checks if the target range contains only the target value	Target range: A value or a list of values in which to search Target value: Specified value or array	Example: Check if a multi-select question is correct Formula: IF(Answers.CONTAINSONLY("A", "B", "C"), "Correct", "Incorrect")=Correct
ISNULL function	ISNULL(value) Checks whether the target value is empty. If it is empty, the result is true, otherwise it is false	Value: The cell to be evaluated	ISNULL(Field 1) Note: If field 1 is empty, then true is returned, otherwise false is returned.
RANDOMBETWEEN function	RANDOMBETWEEN(bottom, top, [update_continuously]) Returns a random integer between the numbers specified.	Bottom: The smallest integer the function will return Top: The largest integer the function will return Update continuously: If TRUE, updates the number returned each time a record changes; if FALSE, don't update. Default is TRUE	Example: Return a random number between 1 and 10 Formula: RANDOMBETWEEN(1, 10)
RANDOMITEM function	RANDOMITEM(list, [update_continuously]) Returns a random item from a list	List: A list or a set of data. It could be a field in a base Update continuously: If TRUE, updates the item returned each time a record changes; if FALSE, don't update. Default is TRUE	Example: Return a random member in a person field of a table Formula: Staff.Name.RANDOMITEM()=John Example: Return a random option for lunch Formula: LIST("Fried chicken", "Beef noodles", "Pizza", "Hot pot", "Burger").RANDOMITEM()=Pizza

Text functions

Function	Formula	Arguments	Example
CHAR function	CHAR(integer) Returns the Unicode character corresponding to the integer code	Integer: The integer that needs to be converted to Unicode	Example: Newline symbol Formula: CHAR(10)=\n
CONCATENATE function	CONCATENATE(character string 1, [character string 2, ...]) Concatenates multiple character strings	Character string 1: The initial character string Character string 2: The other character string(s) to be concatenated	CONCATENATE("Ba", "se")=Base
CONTAINTEXT function	CONTAINTEXT(text, find text) Evaluates whether text contains the the specified subset of text	Text: The text to look through. This can be text or a field Find text: The text to find	CONTAINTEXT("Base","se")=true
FIND function	FIND(find value, find range, [start position]) Finds the specified value starting from the specified position, and returns the position where it first appears; if the value does not exist, it returns -1	Find value: The value to find, which is case sensitive Find range: The range to be searched. This may be a value, an array of data, or a field within a table Start position: The position to start searching. By default, it starts from 1	FIND("A", "Base")=2
HYPERLINK function	HYPERLINK(link address, [text to display]) Creates a hyperlink in a cell that redirects users to the target address	Link address: The complete web address for the target page. This can be manually entered, and can also directly reference a field containing an address in a table Text to display: The text displayed in the cell. This can be manually entered, and can also directly reference a field in a table	HYPERLINK(" https://www.com/ ","Base")
LEFT function	LEFT(character string, [number of characters]) Returns a substring extracted from the beginning of the specified character string	Character string: The character string where the left portion is to be returned Number of characters: The number of characters to be returned from the left side of the character string	LEFT("Base", 2)=Ba
LEN function	LEN(text) Returns the length of the specified character string	Text: The character string for which the length is to be returned	LEN("Base")=4
LOWER function	LOWER(text) Converts the letters in the character string to lower case	Text: The character string to be converted to lower case	LOWER("ABC")=abc
MID function	MID(character string, start position, length to extract) Returns the specified number of characters starting from the specified position in the character string	Character string: The character string from which characters are to be extracted Start position: The position in the character string to start extracting. The index for the first character is 1 Length to extract: The length of the characters to be extracted	MID("Base", 1, 2)=Ba
REPLACE function	REPLACE(text, position, length, new text) Replaces part of a character string with another character string	Text: The text, part of which is to be replaced Position: The starting position (the starting index number is 1) Length: The number of characters to be replaced in the text New text: The text to be inserted into the original text	REPLACE("abcdefg", 1, 6, "xyz")=xyzg
RIGHT function	RIGHT(character string, [number of characters]) Returns a substring extracted from the end of the specified character string	Character string: The character string where the right portion is to be returned Number of characters: The number of characters to be returned from the right side of the character string	RIGHT("Base", 2)=se
SUBSTITUTE function	SUBSTITUTE(text to search, search for, replace with, [occurence number]) Replaces text with new text	Text to search: The text in which a part will be replaced Search for: The text to be replaced Replace with: The new text to replace the previous argument Occurence number: An integer specifying the instance of the old text to be replaced. When this is blank, all old text is replaced	SUBSTITUTE("abcdefg", "cde", "xyz", 1)=abxyzfg
SPLIT function	SPLIT(text, separator ) Splits the text with a separator	Text: The text to be split Separator : A single character or a sequence of characters used to split the text	Example: Split text with a single-character separator Formula: SPLIT("a,b,c", ",") will result in: ["a", "b", "c"] Example: Split text with a multi-character separator Formula: SPLIT("a--c", "--") will result in: ["a", "c"] Example: Split text with a line break Formula: SPLIT([field containing line breaks], char(10))
TEXT function	TEXT(value, format) Converts integers into text based on the specified format	Value: The value to be converted into text Format: A custom integer format. Common examples are: "YYYY/MM/DD" (year, month, day), "MM/DD HH:MM" (month, day, time), "DDDD" (day of the week full name), "DDD" (day of the week abbreviated name), "0.0%" (percentage)	TEXT("2021-01-01", "ddd")=Friday
TODATE function	TODATE(text) Convert text to date format	Text: The text value to be converted, such as: "2021/06/30"	Example: Convert a date in YYYY-M-D text format to a date format Formula: TODATE("2023-6-5")=2023/06/05 00:00
TRIM function	TRIM(text) Removes leading, trailing, and repeated blank spaces in text	Text: The text from which blank spaces are to be removed, or a reference to a cell containing text	TRIM(" abc abc ")=abc abc
UPPER function	UPPER(text) Converts the letters in the character string to upper case	Text: The character string to be converted to upper case	UPPER("abc")=ABC
ENCODEURL function	ENCODEURL(text) Return a URL-encoded string based on the given text, replacing certain non-alphanumeric characters with a percentage symbol (%) and hexadecimal numbers	Text: The text to be URL-encoded	ENCODEURL("Base formula")=Base%20formula
FORMAT function	FORMAT(Template string, [Value, ...]) Concatenate strings by specifying a template string and its corresponding replacement values.	Template string: Text that contains placeholders such as {1}, {2}, {3} Value: The content to replace the corresponding placeholders in the template string with values	Example: Replace placeholders with corresponding fields to return a complete sentence. Formula: FORMAT("{1} scored {2} in the {3} test", Name, Score, Subject)=Josh scored 95 for the English test
REGEXEXTRACT function	REGEXEXTRACT(text, regular_expression) Extracts the first match in a text string according to a regular expression.	Text: The input text Regular expression: The regular expression you want to match within the input text	Example: Extract an 11-digit mobile number from a text string Formula: REGEXEXTRACT("My phone number is 13812345678", "\d{11}")=13812345678
REGEXEXTRACTALL function	REGEXEXTRACTALL(text, regular_expression) Extracts all matches in a text string according to a regular expression.	Text: The input text Regular expression: The regular expression you want to match within the input text	Example: Extract all 11-digit mobile numbers in a text string Formula: REGEXEXTRACTALL("My phone number is 13812345678，Her phone number is 13987654321", "\d{11}")=13812345678,13987654321
REGEXMATCH function	REGEXMATCH(text, regular_expression) To determine whether a text string matches a regular expression.	Text: The input text Regular expression: The regular expression to evaluate the text against	Example: Check if a mobile number is 11-digit Formula: REGEXMATCH("13812345678", "^\d{11}$")=true
REGEXREPLACE function	REGEXREPLACE(text, regular_expression, replacement) Replaces all parts of a text string with other text based on the regular expression.	Text: The text to be replaced Regular expression: The regular expression to search for within the text Replacement: The text that will replace the original text	Example: Replace the mobile number in a text string with asterisks Formula: REGEXREPLACE("My phone number is 13812345678", "\d{11}", "*********")=My phone number is *********

Math functions

Function	Formula	Arguments	Example
ABS function	ABS(numerical value) Returns the absolute value for a numerical value	Numerical value: The numerical value for which the absolute value is to be returned	ABS(-2)=2
ACOS function	ACOS(numerical value) Returns the arc cosine of a value in radians	Numerical value: The numerical value to be calculated	ACOS(-0.5)*180/PI()=120
ACOSH function	ACOSH(numerical value) Returns the inverse hyperbolic cosine of a value	Numerical value: The numerical value to be calculated	ACOSH(1)=0
ASIN function	ASIN(numerical value) Returns the arcsine of a value in radians	Numerical value: The numerical value to be calculated	ASIN(-0.5)*180/PI()=-30
ASINH function	ASINH(numerical value) Returns the inverse hyperbolic sine of a value in radians	Numerical value: The numerical value to be calculated	ASINH(10)=2.99822295
ATAN function	ATAN(numerical value) Returns the arctangent of a value in radians.	Numerical value: The numerical value to be calculated	ATAN(1)*180/PI()=45
ATAN2 function	ATAN2(X-coordinate value, Y-coordinate value) Returns the arctangent of the quotient of the X and Y coordinates	X-coordinate value: The numerical value to be calculated Y-coordinate value: The numerical value to be calculated	ATAN2(-1, -1)*180/PI()=-135
ATANH function	ATANH(numerical value) Returns the inverse hyperbolic tangent of a value	Numerical value: The numerical value to be calculated	ATANH(0.76159416)=1.00000001
AVERAGE function	AVERAGE(value 1, [value 2, ...]) Returns the arithmetic average of an array	Value 1: The first numerical value or field for calculating the average Value 2: Other numerical values or fields for calculating the average	AVERAGE(2, 100)=51
COS function	COS(angle) Returns the cosine of an angle in radians	Angle: The angle for which to calculate the cosine in radians	COS(60*PI()/180)=0.5
COSH function	COSH(numerical value) Returns the hyperbolic cosine of a value	Numerical value: The numerical value to be calculated	COSH(4)=27.30823284
COUNTA function	COUNTA(value 1, [value 2, ...]) Counts the number of non-blank spaces in a data set	Value 1: The first value or referenced field to be counted Value 2: The other values or referenced fields to be counted	COUNTA([Attachment field])=Number of attachments in the attachment field
COUNTIF function	Data range.COUNTIF(evaluation condition) Calculates the number of data points corresponding to the evaluation condition in a target field or array	Data range: A list or array of data Evaluation condition: The condition for evaluating the data within the target range Note: When referencing data, use CurrentValue for individual instances For details, see COUNTIF function.	Example: Count the number of inventory categories with remaining inventory greater than 1000 in [Inventory summary table] Formula: [Inventory summary table].COUNTIF(CurrentValue.[remaining inventory]>1000)
INT function	INT(numerical value) Returns the integer part of a number by rounding it down to the nearest integer	Numerical value: The numerical value to be calculated	INT(8.9)=8
ISODD function	ISODD( numerical value ) Evaluates whether a number is odd, results in "ture" if it is, and "false" if it's not	Numerical value: The numerical value to be evaluated	ISODD(1)=true
MAX function	MAX(value 1, [value 2, ...]) Returns the maxpoint in a numeric array	Value 1: The first value or referenced field for calculating the maxpoint Value 2: Other values or referenced fields for use in calculating the maxpoint	MAX(2, 100)=100
MIN function	MIN(value 1, [value 2, ...]) Returns the minpoint in a numeric array	Value 1: The first value or referenced field for calculating the minpoint Value 2: Other values or referenced fields for use in calculating the minpoint	MIN(2, 100)=2
MOD function	MOD(dividend, divisor) Returns the remainder of a division	Dividend: The numerical value being divided Divisor: The numerical value dividing the dividend	MOD(3, 2)=1
PI function	PI() Returns the mathematical constant pi, accurate to 15 digits: 3.14159265358979	/	PI()=3.141592654
POWER function	POWER(base number, exponent) Returns a number raised to the specified power	Base number: The numerical value to apply the power to, which can be any real number Exponent: The exponent to apply to the base number	POWER(5,2)=25
QUOTIENT function	QUOTIENT(dividend, divisor) Returns the integer portion of a division result, discarding the remainder	Dividend: The numerical value being divided Divisor: The numerical value dividing the dividend	QUOTIENT(5, 2)=2
ROUND function	ROUND(numerical value, number of digits) Rounds a numerical value to the specified number of digits or fewer	Numerical value: The value to be rounded Number of digits: The number of digits to be rounded. 1 represents the first place after the decimal point; 0 represents a whole integer; and -1 represents the first place before the decimal point	ROUND(99.44, 1)=99.4
ROUNDDOWN function	ROUNDDOWN(numerical value, number of digits) Rounds a numerical value to the specified number of digits, rounding down toward 0	Numerical value: The value to be rounded. Number of digits: Represents the number of digits to be rounded. 1 represents the first place after the decimal point; 0 represents a whole integer; and -1 represents the first place before the decimal point	ROUNDDOWN(99.44, 1)=99.4
ROUNDUP function	ROUNDUP(numerical value, number of digits) Rounds a numerical value to the specified number of digits, rounding away from 0	Numerical value: The numerical value to be rounded Number of digits: Represents the number of digits to be rounded. 1 represents the first place after the decimal point; 0 represents a whole integer; and -1 represents the first place before the decimal point	ROUNDUP(99.45, 1)=99.5
SIN function	SIN(angle) Returns the sine of an angle in radians	Angle: The angle for which to calculate in radians	SIN(PI())=0
SINH function	SINH(numerical value) Returns the hyperbolic sine of a value	Numerical value: The numerical value to be calculated	2.868SINH(0.03421.03)=0.101049063
SUM function	SUM(value 1, [value 2, ...]) Returns the sum of all numerical values in an array	Value 1: The first numerical value or referenced list Value 2: Other numerical values or referenced lists	SUM(1, 2)=3
SUMIF function	SUMIF(data range, evaluation condition) Returns the sum of the values that meet the specified criterion in a range	Data range: The target field or array to sum, in the format [Target table].[target field] Evaluation condition: The condition for evaluating the data in the target range Note: When referencing data, use CurrentValue for individual instances For details, see SUMIF function.	Example: Tabulate the total sum for [sales volume] > 1000 in a [Sales summary table]. Formula: [Sales summary table].[sales volume].SUMIF(CurrentValue > 1000) Example: Tabulate the total sum of the [sales volume] for each [sales member] in [Sales summary table] (you will need to use FILTER) Formula: [Sales summary table].FILTER(CurrentValue.[sales member]= [sales member]).[sales volume].SUM()
TAN function	TAN(angle) Returns the tangent of an angle in radians	Angle: The angle for which to calculate the tangent in radians	TAN(45*PI()/180)=1
TANH function	TANH( numerical value ) Returns the hyperbolic tangent of a value	Numerical value: The numerical value to be calculated	TANH(0)=0
VALUE function	VALUE(text) Converts a text string expressing an integer (such as the currency, date, or time formats) into an integer	Text: The text that needs to be converted into an integer	VALUE("$50")=50
SEQUENCE function	SEQUENCE(start_number, end_number, [step]) Returns a list of sequential numbers in an array	Start number: The first number in the array End number: The last number in the array Step: The amount to increase or decrease each number in the sequence. The default is 1	SEQUENCE(1,10,2)=1,3,5,7,9

Position functions

Function	Formula	Arguments	Example
Distance function	DISTANCE([location field 1], [location field 2]) Calculates the distance between two locations. By default, the unit is kilometers	Location field 1: The first location from a Base location field Location field 2: The second location from a Base location field	Distance([company location],[attendance location])

List functions

Function	Formula	Arguments	Example
ARRAYJOIN function	ARRAYJOIN(list, [separator]) Concatenates the values into a string using a specified separator	List: The list of values to be concatenated Separator: The character to place between each concatenated value. The default character is the comma For details, see ARRAYJOIN function.	ARRAYJOIN(LIST("A","B","C"),"-")=A-B-C
FILTER function	range.FILTER(condition) Filters records based off provided conditions. Must specify a field for results	Range: A list or set of data. Enter [table] or [table].[field A] Condition: A condition applied to a range of data. With the condition, the CurrentValue function evaluates the data to be true or false Note: When referencing data, use CurrentValue for individual instances For details, see FILTER function.	Example: Filter [SalesReport] for each representatives' sales volume Formula: [SalesReport].FILTER(CurrentValue.[Name]=[Name]).[Sales]
FIRST function	FIRST(list) Returns the first value in a set or an array of data	List: An array of data or a field in a base.	LIST(1,3,5,7).FIRST()=1 LIST("B","A","S","E").FIRST()="B"
LAST function	LAST(list) Returns the value in a set or an array of data	List: An array of data or a field in a base.	LIST(1,3,5,7).LAST()=7 LIST("B","A","S","E").LAST()="E"
LIST function	LIST([value, ...]) Returns a list, which can contain values, and can also be a nested list	Value: This can be a specific value, and can also be multiple fields	LIST(1,2,3)=1,2,3
LISTCOMBINE function	LISTCOMBINE(field 1, [field 2, ...]) Combines multiple arrays of fields, and displays the result	Field 1: The first field to be combined, which can also be manually entered Field 2: The second field to be combined, which can also be manually entered	Example: Combine the two data arrays [1,2,3,4,5] and [2,3,4] Formula: LIST(1,2,3,4,5).ListCombine(2,3,4)=1,2,3,4,5,2,3,4
LOOKUP function	Lookup(value for lookup, field to match, field for returned result, [find model]) Looks up data based on the specified matching conditions	Value for lookup: A field in the current table. Supports manual input Field to match: A field in another table to look up against the first argument, formatted as [Target table].[Target field] Field for returned result: The field where you want the result to go [Find model]: Optional. The default is 1. When multiple fields to match are selected, you can specify a find model: 1: Split find, which splits the multiple selections into single selections 0: Integrated find, which integrates the multiple selections as an array	Example: Find the sales volume of each member in the current [Member list] who is in the [Sales summary table] Formula: Lookup([Name], [Sales summary table].[Name], [Sales summary table].[Sales volume])
NTH function	List.NTH(Position) Returns the Nth item from a list or a set of data	List: An array of data or a field in a base Position: The position of the returned value (starting from 1)	LIST(1,3,5,7).NTH(2)=3 LIST("B","A","S","E").NTH(4)=E
UNIQUE function	UNIQUE(Value 1, [Value 2, ...]) Returns a list of unique values from a list or a set of data	Value 1: The list or data set from which unique values will be extracted. Could be a field in the base Value 2: The list or data set that will be combined with Value 1 for extracting unique values For details, see UNIQUE function.	UNIQUE(1,2,2,4,5,5)=1,2,4,5