Skip to content

Help center/ Formulas

Formula guide

Calculate totals, work with dates and connect records across tables. Find the syntax and examples you need for your next formula.

Start with a simple formula

A formula field calculates a value for each record. Select fields from the editor so Cynexio can resolve their names and types. The examples use common order, invoice and project fields. Field and table names are illustrative; select the matching fields from your own app.

Calculate a total

Formula
[Unit Price] * [Quantity]

If Unit Price is 12.50 and Quantity is 4, the result is 50.

Add a condition

Formula
IF([Stock Available] >= [Order Quantity], "Ready to ship", "Backorder")

Returns “Ready to ship” when available stock covers the order quantity.

Use Test on first row to preview a result without changing records. Save the formula in the editor, then save the field settings to persist the change.

Fields & syntax

Field names refer to values in the current row. Use double quotes for text, commas between function arguments and parentheses to control calculation order. Optional arguments appear in square brackets in function signatures; those brackets are documentation notation.

Formula operators
OperatorMeaningExample
+ - * /Arithmetic([Unit Price] * [Quantity]) / 2
= != > >= < <=Compare values[Quantity] >= 3
&Join text[First Name] & " " & [Last Name]
AND / OR / NOTCombine conditionsAND([Quantity] > 0, [Unit Price] > 0)
Field names and types matter. A text value that looks like a date is not automatically a date field. Select the intended field token rather than putting its name in double quotes.

Calendar days or working days?

Calendar days: add or subtract an integer

Formula
[Order Date] + [Shipping Days]
Formula
[Delivery Date] - 3

Use the first formula to estimate delivery from the order date. Use the second to schedule a reminder three days before delivery.

Date arithmetic includes weekends and holidays. If Order Date is September 10, 2026 and Shipping Days is 3, [Order Date] + [Shipping Days] returns September 13. The offset must be a whole number; a blank date or offset returns a blank result.

Working days: use WORKDAY

Formula
WORKDAY([Order Date], 3)

Moves forward three working days, skipping weekends and configured holidays. An order placed on Thursday, September 10, 2026 finishes three working days of processing on Tuesday, September 15. A negative offset moves backward; zero keeps the starting date.

The current default holiday calendar is US. WORKDAY skips Saturday and Sunday; it does not model weekend make-up workdays. A blank starting date causes an error. See WORKDAY for the optional holiday argument.

Calculate across tables

Use a table as the receiver of COUNTIF to count matching records. For example, your current table and Tasks both have a Project relation field pointing to the same project records.

Formula
Tasks.COUNTIF(@CurrentValue.Project = Project)

@CurrentValue.Project is the project on the task record being checked. The unqualified Project is the project on your current row. If five task records match, the result is 5.

Count only completed tasks

Formula
Tasks.COUNTIF(AND(@CurrentValue.Project = Project, @CurrentValue.Status = "Completed"))

If two of those five matching tasks have Status set to Completed, the result is 2. A single-select comparison accepts an unambiguous option label such as “Completed”, as well as the stored option key.

Relations match record IDs. Two projects with identical displayed names can still be different records. Use the actual table, field and option names from your app.

Describe it. Generate it. Check it.

  1. Describe your formula. Name the fields, the condition and the result you want. Try: “Count tasks in Tasks for the same Project whose Status is Completed.”
  2. Generate. Review the formula and “How it works”. Suggested examples use fields from your current table.
  3. Use formula. Insert the generated result into the editor. This does not save the field yet.
  4. Test on first row. Preview against the table’s default first record, which may differ from the first row in a filtered or sorted view. No records are changed.
  5. Save. Save the editor and then the field settings. For an AI-generated formula, the description and generated result are retained so you can review them when editing again.
AI can generate formulas using all 112 functions listed here, along with arithmetic, comparisons, text joining and date offsets. Describe your calculation, review the result, and test it on the first row before saving.

Function reference

Browse the functions registered in Cynexio’s engine. Each entry includes its syntax, description and reference example. Use the syntax and argument types shown for each function.

112 functions

Date functions · Syntax & examples

Returns the date after adding a number of days to the given date.

Syntax
ADDDATE(date, days)

Arguments. date; days.

Example

Estimate the delivery date using calendar days.

ADDDATE([Order Date], [Shipping Days])

Converts numbers representing year, month, and day into a date.

Syntax
DATE(year, month, day)

Arguments. year, month, day (numbers or field refs).

Example

Build the first date of a fiscal year that starts in January.

DATE([Fiscal Year], 1, 1)

Returns the difference between two dates in days, months, or years.

Syntax
DATEDIF(start_date, end_date, unit)

Arguments. start_date, end_date (date values); unit: "Y" years, "M" months, "D" days.

Example

Calculate completed years of service for an employee.

DATEDIF([Hire Date], TODAY(), "Y")

Returns the day of the month as a number.

Syntax
DAY(date)

Arguments. date.

Example

Extract the billing day of the month.

DAY([Invoice Date])

Returns the number of days between two dates.

Syntax
DAYS(end_date, start_date)

Arguments. end_date, start_date.

Example

Measure the calendar days between an order and its delivery.

DAYS([Delivery Date], [Order Date])

Builds an engine duration value from days, hours, minutes and seconds.

Syntax
DURATION(days, [hours], [minutes], [seconds])

Arguments. days, hours, minutes, seconds.

Example

Build a duration from a job's planned days and hours.

DURATION([Planned Days], [Planned Hours], 0, 0)
  • Current date-field arithmetic supports integer calendar-day offsets; do not substitute DURATION for a day offset in [Order Date] + [Shipping Days].

Returns the date that is a specified number of months before or after the start date.

Syntax
EDATE(start_date, months)

Arguments. start_date; months (positive for future, negative for past).

Example

Calculate a contract renewal date after the agreed number of months.

EDATE([Contract Start Date], [Contract Months])
  • If parameters cannot be converted to date or are empty, returns #VALUE!. If the resulting month has fewer days, returns last day of that month.

Returns the last day of the month that is a specified number of months before or after start_date.

Syntax
EOMONTH(start_date, months)

Arguments. start_date; months (positive future, negative past).

Example

Find the last day of the invoice month.

EOMONTH([Invoice Date], 0)

Returns the hour component of a time value.

Syntax
HOUR(time)

Arguments. time.

Example

Extract the hour of an employee's check-in.

HOUR([Check-in Time])

Returns the minute component of a time value.

Syntax
MINUTE(time)

Arguments. time.

Example

Extract the minute component of an employee's check-in.

MINUTE([Check-in Time])

Returns the month number of a date.

Syntax
MONTH(date)

Arguments. date.

Example

Extract the month number for invoice reporting.

MONTH([Invoice Date])

Counts working days in the inclusive interval, excluding weekends and configured holidays.

Syntax
NETWORKDAYS(start_date, end_date, [holidays_or_country], [country])

Arguments. start_date and end_date: Valid dates. Optional holiday values or a supported country code; the default calendar is US.

Example

Count working days in a project schedule, including eligible start and end dates.

NETWORKDAYS([Project Start Date], [Project End Date])
  • The engine swaps reversed endpoints.

Returns the current date and time.

Syntax
NOW()

Example

Show the current date and time when an order formula is evaluated.

NOW()
  • Evaluated when the engine calculates the formula. This does not imply a continuously ticking cell or a scheduled refresh.

Returns the seconds component of a time value.

Syntax
SECOND(time)

Arguments. time.

Example

Extract the seconds component of a machine event timestamp.

SECOND([Machine Event Time])

Returns today's date.

Syntax
TODAY()

Example

Show today's date when a delivery planning formula is evaluated.

TODAY()
  • Evaluated when the engine calculates the formula. A date change alone does not schedule a grid refresh.

Returns the day of week as a number.

Syntax
WEEKDAY(date, [type])

Arguments. date; type (1=Sunday start, 2=Monday start; default 1).

Example

Identify the weekday of a delivery, with Monday numbered 1.

WEEKDAY([Delivery Date], 2)

Returns the week number of the year for a date.

Syntax
WEEKNUM(date, [type])

Arguments. date; type (1=Sunday start, 2=Monday start; default 1).

Example

Assign an order to a reporting week that starts on Monday.

WEEKNUM([Order Date], 2)

Moves a date forward or backward by working days, skipping Saturday, Sunday and configured holidays.

Syntax
WORKDAY(start_date, days, [holidays_or_country])

Arguments. start_date: A date field or valid date. days: An integer from -10000 to 10000. holidays_or_country: Optional custom holiday values or a supported country code.

Example

Calculate when order processing will finish, skipping weekends and configured holidays.

WORKDAY([Order Date], [Processing Days])
  • The current deployment defaults to the US holiday calendar. This is not a China business-day calendar; weekend make-up workdays are not modeled.
  • A zero offset returns the starting date. A blank starting date is an error. For calendar days, use [Order Date] + [Shipping Days] or [Delivery Date] - 3 instead.
  • Custom holidays can be supplied as a list, for example WORKDAY([Order Date], 3, LIST("2026-09-21")). These dates are added to the default holiday calendar.

Returns the year of a given date.

Syntax
YEAR(date)

Arguments. date.

Example

Extract the invoice year for annual reporting.

YEAR([Invoice Date])

Logical functions · Syntax & examples

Returns TRUE only if all arguments evaluate to TRUE; returns FALSE if any argument is FALSE.

Syntax
AND(logical_expression1, [logical_expression2, ...])

Arguments. logical_expression1: Boolean expression or field reference. logical_expression2: Additional boolean expressions.

Example

Check that an order line has a positive quantity and price.

AND([Quantity] > 0, [Unit Price] > 0)

Determines whether the range contains the specified values. Note: Text substring match is not supported; use CONTAINTEXT for that.

Syntax
CONTAIN(range, [value_to_find, ...])

Arguments. range: A number, array, or list to search. value_to_find: One or more values to search for.

Example

Check whether either of the two assigned markets is Japan.

CONTAIN(LIST([Primary Market], [Secondary Market]), "Japan")

Returns TRUE if the range contains all specified values.

Syntax
CONTAINSALL(range, [values...])

Arguments. range: Number or list range. values: Values or list to check.

Example

Check that a shipment includes both an invoice and a packing list.

CONTAINSALL(LIST([Document 1 Type], [Document 2 Type], [Document 3 Type]), "Invoice", "Packing list")

Returns TRUE only if the range contains exactly and only the specified values.

Syntax
CONTAINSONLY(range, [values...])

Arguments. range: Number or list range. values: Values or list to check.

Example

Check whether the review decisions contain only the Approved value.

CONTAINSONLY(LIST([Reviewer 1 Decision], [Reviewer 2 Decision]), "Approved")

Returns the logical value FALSE.

Syntax
FALSE()

Example

Return false when stock cannot cover an order.

IF([Order Quantity] > [Stock Available], FALSE(), TRUE())

Returns one value if a condition is TRUE, and another value if it is FALSE.

Syntax
IF(condition, value_if_true, [value_if_false])

Arguments. condition: A boolean expression. value_if_true: Value when TRUE. value_if_false: Value when FALSE.

Example

Show whether an order can be fulfilled from current stock.

IF([Stock Available] >= [Order Quantity], "Ready to ship", "Backorder")

Returns the original value when it is not blank; otherwise returns the fallback.

Syntax
IFBLANK(value, value_if_blank)

Arguments. value: Value to return when not blank. value_if_blank: Fallback when value is blank.

Example

Use a clear label when no company name is supplied.

IFBLANK([Company Name], "Individual customer")

Returns the value when it is not an error; otherwise returns the fallback.

Syntax
IFERROR(value, value_if_error)

Arguments. value: The value to return when no error. value_if_error: Return value when an error occurs.

Example

Show a review message when average selling price cannot be calculated.

IFERROR([Revenue] / [Units Sold], "Check units sold")

Evaluates multiple conditions and returns the value corresponding to the first condition that evaluates to TRUE.

Syntax
IFS(condition1, value1, [condition2, ...], [value2, ...])

Arguments. condition1/value1: First test and result; then additional condition/value pairs.

Example

Assign a collection action based on how overdue an invoice is.

IFS([Days Overdue] > 30, "Escalate", [Days Overdue] > 0, "Follow up", TRUE, "On time")

Checks whether a value is blank; returns TRUE if blank, otherwise FALSE.

Syntax
ISBLANK(value)

Arguments. value: Field or literal to test.

Example

Identify orders that still need a delivery address.

ISBLANK([Delivery Address])

Checks whether the value is an error.

Syntax
ISERROR(value)

Arguments. value: Value to test.

Example

Check whether an average selling price calculation fails.

ISERROR([Revenue] / [Units Sold])

Checks whether a value is NULL; returns TRUE if NULL, otherwise FALSE.

Syntax
ISNULL(value)

Arguments. value: Field or literal to test.

Example

Check whether an approval date has no value.

ISNULL([Approval Date])

Checks whether the value is a number.

Syntax
ISNUMBER(value)

Arguments. value: Any type.

Example

Check whether an imported amount is a number rather than text.

ISNUMBER([Imported Amount])

Logical negation of its boolean argument.

Syntax
NOT(boolean)

Arguments. boolean: Any expression that evaluates to TRUE or FALSE.

Example

Check whether a shipment has a tracking number.

NOT(ISBLANK([Tracking Number]))

Returns TRUE if any argument is TRUE; returns FALSE only when all arguments are FALSE.

Syntax
OR(logical_expression1, [logical_expression2, ...])

Arguments. logical_expression1: Boolean expression or field reference. logical_expression2: More boolean expressions.

Example

Check whether a shipment goes to either supported destination.

OR([Shipping Country] = "Canada", [Shipping Country] = "United States")

Compares an expression to specified cases and returns the matching value; can include a default. Result cannot be an array.

Syntax
SWITCH(expression, case1, value1, [case2 or default, ...], [value2, ...])

Arguments. expression: The value to compare. case1/value1: First case and result. Additional case/value pairs or a default.

Example

Assign an estimated transit time of 1, 5 or 10 calendar days by shipping method.

SWITCH([Shipping Method], "Express", 1, "Standard", 5, 10)

Returns the logical value TRUE.

Syntax
TRUE()

Example

Mark an invoice as fully paid when its outstanding amount is zero.

IF([Outstanding Amount] = 0, TRUE(), FALSE())

Text functions · Syntax & examples

Returns the Unicode character for the given code point.

Syntax
CHAR(number)

Arguments. number.

Example

Put the customer name and street address on separate lines of a shipping label.

[Customer Name] & CHAR(10) & [Street Address]

Joins text strings together. Similar to CONCATENATE.

Syntax
CONCAT(text1, [text2, ...])

Example

Build a customer's full name.

CONCAT([First Name], " ", [Last Name])

Concatenates multiple strings.

Syntax
CONCATENATE(text1, [text2, ...])

Arguments. text1: initial string; text2: additional strings.

Example

Create a readable order label.

CONCATENATE([Order Number], " - ", [Customer Name])

Returns TRUE if text contains the search substring.

Syntax
CONTAINTEXT(text, search_text)

Arguments. text: string or field; search_text: substring.

Example

Flag delivery instructions that mention fragile items.

CONTAINTEXT([Delivery Instructions], "fragile")

Returns the URL-encoded string.

Syntax
ENCODEURL(text)

Example

Encode a product name for use in a URL query.

ENCODEURL([Product Name])

Finds first occurrence position starting at start_pos (1-based); returns -1 if not found.

Syntax
FIND(search_for, within_text, [start_pos])

Arguments. search_for (case-sensitive); within_text; start_pos default 1.

Example

Locate the separator in a customer's email address.

FIND("@", [Email Address])

Formats a template with placeholders {1},{2},... using provided values.

Syntax
FORMAT(template, [values...])

Example

Create a short order summary.

FORMAT("Order {1} for {2}", [Order Number], [Customer Name])

Builds an HTML link string from a URL and optional display text.

Syntax
HYPERLINK(url, [display_text])

Arguments. url: full address; display_text: text to display.

Example

Build a link labeled with the invoice number.

HYPERLINK([Invoice URL], [Invoice Number])
  • Whether this appears as a clickable link depends on the field renderer.

Returns leftmost substring.

Syntax
LEFT(text, [num_chars])

Example

Extract the regional prefix from an order number such as LON-2026-0142.

LEFT([Order Number], 3)

Returns length of a string.

Syntax
LEN(text)

Example

Measure the length of a product description before publishing it.

LEN([Product Description])

Converts to lowercase.

Syntax
LOWER(text)

Example

Normalize an email address to lowercase.

LOWER([Email Address])

Returns substring from 1-based start of given length.

Syntax
MID(text, start, length)

Example

Extract 2026 from an order number such as LON-2026-0142.

MID([Order Number], 5, 4)

Extracts the first substring matching regex.

Syntax
REGEXEXTRACT(text, pattern)

Example

Extract the final four digits from an order number such as LON-2026-0142.

REGEXEXTRACT([Order Number], "[0-9]{4}$")

Extracts all substrings matching regex.

Syntax
REGEXEXTRACTALL(text, pattern)

Example

Extract all SKU references mentioned in packing notes.

REGEXEXTRACTALL([Packing Notes], "SKU-[0-9]+")

Returns TRUE if text matches regex.

Syntax
REGEXMATCH(text, pattern)

Example

Check that an order number follows the LON-2026-0142 format.

REGEXMATCH([Order Number], "^[A-Z]{3}-[0-9]{4}-[0-9]{4}$")

Replaces all matches with replacement.

Syntax
REGEXREPLACE(text, pattern, replacement)

Example

Remove spaces and punctuation from a phone number.

REGEXREPLACE([Phone Number], "[^0-9]", "")

Replaces part of text with new text.

Syntax
REPLACE(text, position, length, new_text)

Example

Replace the three-letter regional prefix of an order number.

REPLACE([Order Number], 1, 3, "NYC")

Returns rightmost substring.

Syntax
RIGHT(text, [num_chars])

Example

Extract the sequence number from an order number such as LON-2026-0142.

RIGHT([Order Number], 4)

Splits text by delimiter.

Syntax
SPLIT(text, delimiter)

Example

Split a semicolon-separated recipient list into individual addresses.

SPLIT([Recipient Emails], ";")

Replaces occurrences of old_text; when instance_num provided, only that occurrence.

Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])

Example

Update the edition label in a product name.

SUBSTITUTE([Product Name], "Limited Edition", "Special Edition")

Converts value to text using format such as "YYYY/MM/DD", "MM/DD HH:MM", "DDDD", "DDD", "0.0%".

Syntax
TEXT(value, format)

Example

Format an invoice date for a customer-facing label.

TEXT([Invoice Date], "YYYY/MM/DD")

Converts text to date/time.

Syntax
TODATE(text)

Example

Convert an imported order-date string into a date value.

TODATE([Imported Order Date])

Removes leading, trailing and repeated spaces.

Syntax
TRIM(text)

Example

Remove extra spaces from a customer name.

TRIM([Customer Name])

Converts to uppercase.

Syntax
UPPER(text)

Example

Normalize a product SKU to uppercase.

UPPER([Product SKU])

Number functions · Syntax & examples

Returns the absolute value of a number.

Syntax
ABS(number)

Arguments. number: The value to return as a non-negative number.

Example

Show the size of a budget difference, whether spending is above or below budget.

ABS([Budget Variance])

Returns the arccosine of a number, in radians.

Syntax
ACOS(number)

Arguments. number: The value to compute.

Example

Calculate a beam angle in degrees from its measured lengths.

ACOS([Adjacent Length] / [Beam Length]) * 180 / PI()

Returns the inverse hyperbolic cosine of a number.

Syntax
ACOSH(number)

Arguments. number: The value to compute.

Example

Recover the dimensionless position from a catenary cable height ratio of at least 1.

ACOSH([Normalized Cable Height])

Returns the arcsine of a number, in radians.

Syntax
ASIN(number)

Arguments. number: The value to compute.

Example

Calculate a ramp angle in degrees.

ASIN([Rise] / [Ramp Length]) * 180 / PI()

Returns the inverse hyperbolic sine of a number.

Syntax
ASINH(number)

Arguments. number: The value to compute.

Example

Transform positive and negative sales variances for a chart with a compressed scale.

ASINH([Sales Variance])

Returns the arctangent of a number, in radians.

Syntax
ATAN(number)

Arguments. number: The value to compute.

Example

Convert a measured slope to an angle in degrees.

ATAN([Rise] / [Run]) * 180 / PI()

Returns the arctangent for the given X and Y coordinates.

Syntax
ATAN2(x, y)

Arguments. x: The X coordinate value. y: The Y coordinate value.

Example

Calculate the direction of a surveyed point from its X and Y offsets.

ATAN2([East Offset], [North Offset]) * 180 / PI()

Returns the inverse hyperbolic tangent of a number.

Syntax
ATANH(number)

Arguments. number: The value to compute.

Example

Apply a Fisher transform to a correlation strictly between -1 and 1.

ATANH([Correlation])

Returns the arithmetic mean of values.

Syntax
AVERAGE(value1, [value2, ...])

Arguments. value1: First number or field. value2: Additional numbers or fields.

Example

Calculate average monthly revenue for the first quarter.

AVERAGE([January Revenue], [February Revenue], [March Revenue])

Rounds a number up to the nearest multiple of factor.

Syntax
CEILING(value, [factor])

Arguments. value: Number to round up. factor: Optional rounding multiple (default 1, cannot be 0).

Example

Round an order quantity up to a full pack multiple.

CEILING([Order Quantity], [Pack Size])

Returns the cosine of an angle in radians.

Syntax
COS(angle)

Arguments. angle: The angle in radians.

Example

Calculate a beam's horizontal projection from its angle in degrees.

[Beam Length] * COS([Beam Angle] * PI() / 180)

Returns the hyperbolic cosine of a real number.

Syntax
COSH(number)

Arguments. number: The value to compute.

Example

Calculate the support-to-center height difference of a symmetric catenary cable.

[Cable Parameter] * (COSH([Span] / (2 * [Cable Parameter])) - 1)

Counts values from a supplied list or receiver. The engine accepts at most one argument.

Syntax
COUNT([values])

Arguments. values: Optional collection, array or scalar value.

Example

Count all records in the Orders table.

Orders.COUNT()
  • Use COUNT(LIST([Primary Email], [Billing Email])) for a list of values, Orders.COUNT() for all records, and Orders.COUNTIF(condition) for matching records.

Counts non-empty values in a dataset.

Syntax
COUNTA(value1, [value2, ...])

Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.

Example

Count how many contact methods have been filled in.

COUNTA([Email Address], [Phone Number])

Counts records in a table for which the condition is true.

Syntax
table.COUNTIF(condition)

Arguments. table: The table to scan. condition: One expression evaluated for each record; use AND or OR for multiple conditions.

Example

Count completed tasks for the project on the current row.

Tasks.COUNTIF(AND(@CurrentValue.Project = [Project], @CurrentValue.Status = "Completed"))
  • @CurrentValue refers to a Tasks record; an unqualified Project refers to the current row in your formula's table.
  • Related fields compare record IDs, not just displayed names. Single-select fields accept an unambiguous option label or its stored key.

Rounds a number down to the nearest multiple of factor.

Syntax
FLOOR(value, [factor])

Arguments. value: Number to round down. factor: Optional rounding multiple (default 1, cannot be 0).

Example

Find how many units can be supplied in complete packs.

FLOOR([Stock Available], [Pack Size])

Rounds a number down to the nearest integer less than or equal to it.

Syntax
INT(number)

Arguments. number: The value to compute.

Example

Calculate the number of complete packs available for positive stock quantities.

INT([Stock Available] / [Pack Size])

Returns TRUE if a number is odd, otherwise FALSE.

Syntax
ISODD(number)

Arguments. number: The value to test.

Example

Identify odd-numbered weeks for an alternating maintenance schedule.

ISODD([Week Number])

Returns the largest value in a dataset.

Syntax
MAX(value1, [value2, ...])

Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.

Example

Find the highest supplier quote.

MAX([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])

Returns the median of a dataset.

Syntax
MEDIAN(value1, [value2, ...])

Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.

Example

Find the middle daily sales value for the workweek.

MEDIAN([Monday Sales], [Tuesday Sales], [Wednesday Sales], [Thursday Sales], [Friday Sales])

Returns the smallest value in a dataset.

Syntax
MIN(value1, [value2, ...])

Arguments. value1: First value or referenced field. value2: Additional values or referenced fields.

Example

Find the lowest supplier quote.

MIN([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])

Returns the remainder after division.

Syntax
MOD(dividend, divisor)

Arguments. dividend: Number to be divided. divisor: Number to divide by.

Example

Find the units left over after filling complete packs.

MOD([Order Quantity], [Pack Size])

Returns the mathematical constant pi (3.14159265358979).

Syntax
PI()

Arguments. none.

Example

Calculate the circular base area of a storage tank.

PI() * POWER([Tank Radius], 2)

Returns a number raised to a power.

Syntax
POWER(base, exponent)

Arguments. base: The base number. exponent: The power to raise the base to.

Example

Estimate compound growth using an annual rate stored as a decimal, such as 0.05.

[Principal] * POWER(1 + [Annual Rate], [Years])

Returns the integer portion of a division result.

Syntax
QUOTIENT(dividend, divisor)

Arguments. dividend: Number to be divided. divisor: Number to divide by.

Example

Calculate the number of complete packs in a positive order quantity.

QUOTIENT([Order Quantity], [Pack Size])

Generates a random integer within the inclusive range.

Syntax
RANDOMBETWEEN(min_integer, max_integer, [volatile])

Arguments. min_integer: Inclusive lower bound. max_integer: Inclusive upper bound. volatile: Accepted for compatibility; it currently does not change evaluation behavior.

Example

Draw a random ticket number within the issued range.

RANDOMBETWEEN(1, [Ticket Count])
  • Each evaluation can generate a new number, including when volatile is FALSE.

Returns the rank of a value within a dataset.

Syntax
RANK(value, range, [ascending])

Arguments. value: Value to rank. range: Dataset to compare. ascending: TRUE for ascending rank, FALSE for descending (default FALSE).

Example

Rank the current sales record by revenue, highest first.

RANK([Revenue], Sales.[Revenue])

Returns the unique ID of the current record.

Syntax
RECORD_ID()

Arguments. none.

Example

Display the unique identifier of the current order record for reconciliation.

RECORD_ID()

Rounds a number to the specified number of digits.

Syntax
ROUND(number, digits)

Arguments. number: Value to round. digits: Decimal places (1 for one decimal place, 0 for integer, -1 for tens place).

Example

Round an order line total to two decimal places.

ROUND([Unit Price] * [Quantity], 2)

Rounds a number down toward zero to a specified number of digits.

Syntax
ROUNDDOWN(number, digits)

Arguments. number: Value to round. digits: Decimal places (1 for one decimal place, 0 for integer, -1 for tens place).

Example

Discard fractional reward points for a non-negative balance.

ROUNDDOWN([Reward Points], 0)

Rounds a number up away from zero to a specified number of digits.

Syntax
ROUNDUP(number, digits)

Arguments. number: Value to round. digits: Decimal places (1 for one decimal place, 0 for integer, -1 for tens place).

Example

Calculate how many parcels are needed for a shipment.

ROUNDUP([Shipment Weight] / [Weight per Parcel], 0)

Generates a numeric sequence from start to end.

Syntax
SEQUENCE(start, end, [step])

Arguments. start: First number in the sequence. end: Last number in the sequence. step: Optional increment/decrement (default depends on start and end).

Example

Generate installment numbers from 1 through the planned count.

SEQUENCE(1, [Installment Count])
  • The step must be nonzero. A sequence contains at most 10000 elements.

Returns the sine of an angle in radians.

Syntax
SIN(angle)

Arguments. angle: The angle in radians.

Example

Calculate a beam's vertical projection from its angle in degrees.

[Beam Length] * SIN([Beam Angle] * PI() / 180)

Returns the hyperbolic sine of a real number.

Syntax
SINH(number)

Arguments. number: The value to compute.

Example

Calculate the length of a symmetric catenary cable over a specified span.

2 * [Cable Parameter] * SINH([Span] / (2 * [Cable Parameter]))

Returns the total of numeric values.

Syntax
SUM(value1, [value2, ...])

Arguments. value1: First value or referenced column. value2: Additional values or referenced columns.

Example

Calculate the combined cost of fulfilling an order.

SUM([Materials Cost], [Labor Cost], [Shipping Cost])

Returns the sum of values in the target range that satisfy a condition.

Syntax
range.SUMIF(condition)

Arguments. range: Target field or list to sum. condition: Condition used to test each item (use @CurrentValue for row-by-row checks).

Example

Sum positive outstanding invoice balances.

Invoices.[Outstanding Amount].SUMIF(@CurrentValue > 0)

Returns the tangent of an angle in radians.

Syntax
TAN(angle)

Arguments. angle: The angle in radians.

Example

Calculate the rise of a ramp from its run and angle in degrees.

[Run] * TAN([Ramp Angle] * PI() / 180)

Returns the hyperbolic tangent of a real number.

Syntax
TANH(number)

Arguments. number: The value to compute.

Example

Compress a demand score into the interval between -1 and 1 for a planning model.

TANH([Normalized Demand Score])

Converts a numeric text string (such as currency, date, or time text) to a number.

Syntax
VALUE(text)

Arguments. text: The text value to convert.

Example

Convert an imported price such as $49.90 into a number.

VALUE([Imported Unit Price])

List functions · Syntax & examples

Joins elements of an array/list into a string using the delimiter. Typically used with LIST.

Syntax
ARRAYJOIN(array, [delimiter])

Arguments. array; delimiter (default ",").

Example

Build a mailing address from three address fields.

ARRAYJOIN(LIST([Street Address], [City], [Postal Code]), ", ")

Filters records that satisfy a predicate. The result can be counted, sorted or projected to a field.

Syntax
range.FILTER(predicate)

Arguments. range: A table or record list. predicate: A condition using @CurrentValue for the record being checked.

Example

Get invoice totals for the customer on the current row.

Orders.FILTER(@CurrentValue.Customer = [Customer]).[Invoice Total]

Returns the first value of a list or field.

Syntax
FIRST(list)

Example

Get the lowest of three numeric supplier quotes.

FIRST(SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])))

Returns the last value of a list or field.

Syntax
LAST(list)

Example

Get the highest of three numeric supplier quotes.

LAST(SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])))

Returns a list that can contain values or nested lists.

Syntax
LIST([values...])

Example

Collect first-quarter revenue figures into a list.

LIST([January Revenue], [February Revenue], [March Revenue])

Concatenates multiple lists/fields and returns the combined sequence.

Syntax
LISTCOMBINE(field1, [field2, ...])

Example

Combine contact addresses into one list.

LISTCOMBINE(LIST([Primary Email], [Billing Email]), LIST([Shipping Email]))

Looks up data based on a matching field and returns values from another field.

Syntax
LOOKUP(search_value, match_field, return_field, [mode])

Arguments. search_value (field/value in current table); match_field [Table].[Field]; return_field [Table].[Field]; mode (1 split multi-selects, 0 treat as whole array; default 1).

Example

Look up a product's unit price using its SKU.

LOOKUP([Product SKU], Products.[Product SKU], Products.[Unit Price])

Transforms each item in a receiver range and returns a list of results.

Syntax
range.MAP(mapping_formula)

Arguments. range: The receiver list. mapping_formula: One expression using @CurrentValue for the current item.

Example

Calculate invoice totals including a 10% surcharge.

Orders.[Invoice Total].MAP(@CurrentValue * 1.1)
  • Use a receiver: Orders.[Invoice Total].MAP(@CurrentValue * 1.1). The mapping expression has one argument; MAP(range, formula) is not supported.

Returns the N-th (1-based) value from a list/field.

Syntax
list.NTH(n)

Example

Get the second-lowest supplier quote.

SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote])).NTH(2)

Returns a random element from a list.

Syntax
RANDOMITEM(list, [volatile])

Arguments. list: A collection of candidate values. volatile: Accepted for compatibility; it does not persist a one-time result.

Example

Choose a contact channel for a randomized outreach experiment.

RANDOMITEM(LIST("Email", "Phone", "Video call"))
  • Each evaluation can return a different value. The engine does not store a fixed result when volatile is FALSE.

Sorts values in a list (numeric).

Syntax
SORT(list, [ascending])

Arguments. list; ascending: TRUE ascending, FALSE descending; default TRUE.

Example

Sort numeric supplier quotes from lowest to highest.

SORT(LIST([Supplier A Quote], [Supplier B Quote], [Supplier C Quote]))

Sorts records by a column in the range, and returns the specified return column.

Syntax
range.SORTBY(sort_by_column, [ascending]).return_column

Arguments. range: A table or filtered record list. sort_by_column: A field on the same table, such as @CurrentValue.[Order Date]. ascending defaults to TRUE.

Example

List invoice totals in ascending order of order date.

Orders.SORTBY(@CurrentValue.[Order Date]).[Invoice Total]
  • The sort field and return field must belong to the receiver table.

Removes duplicates from values and returns unique items.

Syntax
UNIQUE(value1, [value2, ...])

Example

Build a list of distinct customer email addresses from orders.

UNIQUE(Orders.[Customer Email])

When a result looks wrong

COUNTIF returns zero

Check both sides of the condition: @CurrentValue refers to the scanned table, while an unqualified field refers to your current row. Check relation targets, record IDs and the exact option label.

AI says a function is unavailable

All listed functions are available for generation. Start a new generation with the exact field names and desired calculation. For calendar days use [Order Date] + 3; for working days use WORKDAY([Order Date], 3).

Use formula reports a structure mismatch

The displayed expression and its generated structure could not be reconciled. Generate again and verify that the formula references the intended fields before saving.

A date is blank or unexpected

Check the field type, a whole-number offset and the holiday calendar. Calendar-day arithmetic and WORKDAY intentionally handle weekends differently.

The preview differs from my selected row

Test on first row uses the table’s default row order, not the selected record or current view order. Check which values the preview used.

The grid still shows an old result

Save both the formula editor and field settings. Edits to source fields in the same record recalculate its formulas and notify open views. Cross-table changes do not yet guarantee automatic refresh of every dependent formula. Refresh and retest if needed.

Formula guide - Cynexio