Design Guide
Design Guide
Formulas in Ragic work similarly to those in Excel. However, Ragic's formulas are developed by us, meaning that the supported formulas or syntax may not be exactly the same. Especially, when assigning formulas, it references the Field Header directly.
Formulas can calculate not only numbers but also strings and dates. To ensure correct results, set the Field Type according to the type of calculation. For example, use a Numeric or Money field when applying numerical formulas. This allows the system to interpret the formula correctly and return the expected result. If the field type is set incorrectly, the formula may not work as intended.
To assign a formula to a Field Header from your Form Page, navigate to Design Mode and select the Field Header. Go to the Field Settings menu on the left sidebar and enter your formula into the Formula tab.
For example, in the "Sales Order" sheet, if the formula for the "Amount Due" field (A20) is "Total (A17) + Tax (A19)", you would enter "A17+A19" in that cell. Note that the formula should reference the location of the field headers.
There is a fx() icon in the field assigned with the formula.
Clicking on the icon, the system will automatically highlight all the referenced fields of this formula.
For the list of supported formulas in Ragic, please refer here.
Note: The Multiple Select Field Type can only apply specific formulas from the list.
When you don't know how to create formulas, you can specify the rule you want in the Formula Generator. Let AI help you generate it.
Note: On-premises Private Server require a parameter to enable this feature. See this section for details.
Please note：
1. Describe the rules and specify the expected return value for this field. For example: Return today's date.
2. If you want to include text in your formula, enclose it in double quotation marks. For example: "Transaction Date".
3. After configuring the formula, please manually verify whether the results meet the expected outcomes.
To open the Formula Generator interface, click on the Ask AI for formula help below.
Input rules and click Generate Formula.
Below are some default scenarios you can select and enter the fields based on your sheet, including Regular formulas and Approval formulas.
For example, if you want the "Free Delivery" field to return "Yes" when the "Total" is greater than the "Free Delivery Amount," and "No" otherwise, you can choose "If A1 is greater than A2, return 'Y', otherwise return 'N'," and then modify it to match the relevant fields and return values.
After completing, click Generate Formula to generate the corresponding formula below. Click the "copy" icon next to the formula to automatically input the formula.
Operators specify the type of calculation that you want to perform on the arguments of a formula. There is a default order in which calculations are programmed to occur, but you can change this order by using "parentheses ()".
Note: Unlike Excel, Ragic does not allow a colon ":" to be used as a reference operator to combine ranges of cells.
To perform basic mathematical operations such as addition, subtraction, or multiplication and produce numeric results, please use the following arithmetic operators:
| Arithmetic operator | Meaning | Example |
|---|---|---|
| + (plus sign) | Addition | 3+3 |
| – (minus sign) | Subtraction | 3–1 |
| * (asterisk) | Multiplication | 3*3 |
| / (forward slash) | Division | 3/3 |
| ^ (caret) | Exponentiation | 3^2 |
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE that can be used within conditional formulas.
| Comparison operator | Meaning | Example |
|---|---|---|
| = | Equal to | A1=B1 |
| == | Equal to | A1==B1 |
| > | Greater than | A1 > B1 |
| < | Less than | A1 < B1 |
| > = | Greater than or equal to | A1 > =B1 |
| < = | Less than or equal to | A1 < =B1 |
| != | Not equal to | IF(A1!=B1,'yes','no') |
| <> | Not equal to | IF(A1<>B1,'yes','no') |
To create strings in formulas, you can use either 'single quotes', or "double quotes". In this document, we will use 'single quotes' for consistency, but both formats are acceptable in Ragic.
The following lists all formula categories supported in Ragic. You can refer to each category for detailed formulas and usage examples as needed.
Numeric Calculation Formula: Formulas for calculating numerical values and amounts, such as obtaining sums, averages, maximum and minimum values, etc.
Dates and Times Formulas: Formulas for obtaining date and time-related data, such as returning the year, month, day, time, or specific workdays.
Strings Formulas: Formulas for obtaining field value strings or checking field content, such as getting string characters, changing case, checking for null values, etc.
Conditional Formulas: Formulas that return field values based on specific conditions, for example, returning "Yes" when the condition is met or summing the values of fields that match the criteria.
Subtables Formulas: Formulas for obtaining data related to Subtable fields, such as returning a specified entry of a Subtable, retrieving the number of unique or non-empty Subtable rows, etc.
Multiple Select Field Formulas: In multiple selection fields (for example, Multiple Select, Multiple Image Upload, or Multiple File Upload), you can apply formulas to check for specific items, find missing ones, or count uploaded attachments.
Approval Formula: Used to return values related to the approval process when an Approval Flow is configured in the sheet.
Assigning formulas to Field Headers simplifies calculations, especially in Subtables, where complex formulas are unnecessary.
Here's an example of calculations in the "Sales Order" sheet:
In the Subtable, the Unit Price (B12) and Quantity (C12) of each item are listed.
Multiplying these values provides the Total Amount (D12=B12*C12) owed for each item.
Then, the subtotal in the Total field (A17) references the total amount of money (D12) in the Subtable.
Formulas can also work on the Subtables themselves. For instance, if you need to count how many rows there are in a Subtable, you can simply create a separate field in your sheet that uses the COUNT() formula.
For more advanced conditional formula types to count or sum up values in Subtables, please see the COUNTIF Function, the COUNTIFS Function, the SUMIF Function, and the SUMIFS Function.
You can use UNIQUE() and UNIQUE().length to find or calculate the number of unique values in a Subtable.
UNIQUE(): Lists the unique values of the referenced Subtable field. If you don't modify the separator in your formula, Subtable values will default to using "," as the separator, resulting in the image below.
If you would like to configure your separator, you should modify your formula to UNIQUE(field,"separator"). For example, you can do UNIQUE(A1,"/"), UNIQUE(A1," "), or UNIQUE(A1,", "). The result would be "Angie/Rex/Lillian", "Angie Rex Lillian", or "Angie, Rex, Lillian" respectively.
UNIQUE().length: Calculates the number of unique values of the referenced Subtable field.
For example:
The VLOOKUP function returns the field value of Subtable rows if a specified condition evaluates to TRUE.
| Formulas | Syntax |
|---|---|
| VLOOKUP | VLOOKUP(value, queryField, returnField, [approximateMatch=true], [findMultiple=false]) |
The VLOOKUP function syntax has the following arguments:
value: Required. The value you want to look up. Can be a number, expression, reference to another field, or a text string.
queryField: Required. The Subtable field where the lookup value is located (A4 field in the below example).
returnField: Required. The Subtable field contains the return value (B4 field in the below example).
[approximateMatch=true]: Optional. The approximateMatch argument specifies whether you want VLOOKUP to find an approximate or an exact match. The default value is "true" (approximate match). Set to "false" if you would like to find an exact match.
[findMultiple=false]: Optional. The findMultiple argument determines whether the returnFiled returns multiple values. The default value is false. If multiple entries may match the criteria, please set it to "true".
Example
If you want to find the teacher of a specific class in the Subtable below:
You may create a new Free Text field for the user to input the query class. Then, create another free text field and apply the VLOOKUP(A9, A4, B4, false, false) or VLOOKUP(A9, A4, B4) formula. It will return the teacher's name according to the query class inputted.
To return multiple values that meet the criteria and perform an approximate match, you can apply "VLOOKUP(A9, A4, B4, true, true)".
A formula referencing Date fields can calculate dates N number of days in the future or past.
For example, if A1 is a Date field, then A1+7 will be the date for 7 days after A1.
Another common use for using dates in calculations would be: if B1 is a birthday, you can set the formula to "(TODAY() - B1)/365.25" to represent the current age.
Check the list of supported formulas for detailed information about formulas that work with dates.
To calculate time differences within a day, you need a Date field (HH:mm format) or a Numeric field for calculations.
For example, if A1 is the start time (HH:mm) and A2 is the end time (HH:mm), there are two ways to calculate the duration from time A1 to time A2 based on the total number of hours:
1. Use a Date field A3 with formatting (HH:mm) by setting up the formula "A2-A1".
2. Use a Numeric field A3 with formatting (0.0) by setting up the formula "(A2-A1)/60".
Note:
(1) The Date fields referenced in the formula cannot be empty; otherwise, the formula will not trigger (a time value cannot be calculated by adding or subtracting an empty value). If fields might be empty, it is recommended to change the field type to a Numeric field to ensure the formula triggers successfully.
Example:
A1 is the "Start Time" (HH:mm), and A2 is the "Duration" (HH:mm). To calculate the A3 "End Time" (HH:mm) using the formula "A2 + A1", if A2 might be empty, the formula for A3 will not trigger, resulting in an empty value for A3. However, if you change A2 to a Numeric field (representing the duration in minutes), A3 will still display its content even if A2 is empty.
(2) If your start and end times span different dates, you will need to use a Date field formatted to include both time and date elements.
For example, if A1 is the start date and time (yyyy/MM/dd HH:mm) and A2 is the end date and time (yyyy/MM/dd HH:mm), you will require a Numeric field for A3 formatted as (0.0) and apply the formula "(A2-A1)*24".
Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). The WORKDAY function takes a date and returns the nearest working day in the future or past, based on an offset value you provide. Working days exclude weekends and, optionally, any dates specified as holidays, but include specified makeup workdays.
Use WORKDAY to calculate invoice due dates, expected delivery times, total days of work performed, or whenever you need to consider working and non-working days.
| Formula | Syntax |
|---|---|
| WORKDAY | WORKDAY(start_date,days,["holidays"], ["makeup_workdays"]) |
The WORKDAY function syntax has the following arguments:
Start_date: Required. A date that represents the start date.
Days: Required. The number of non-weekend and non-holiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as government and floating holidays. The list should be an array constant of serial numbers representing dates.
Makeup_workdays: Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example 1:
Apply the formula "WORKDAY(A1,A2,["2017/06/16","2017/06/19"])" to a date field.
When A1 contains the value "2017/06/15", and A2 contains the value "9", the formula will use 2017/06/15 as the start date and calculate a date nine workdays in the future, excluding the identified holidays on 2017/06/16 and 2017/06/19.
The resulting date would be 2017/06/30.
Example 2:
Applying the formula "WORKDAY(A1,A2,["2017/06/16","2017/06/19"],["2017/06/24"])" to a date field.
When A1 contains the value "2017/06/15", and A2 contains "9", with "2017/06/16" and "2017/06/19" specified as non-countable dates, and "2017/06/24" designated as a workday (despite being a Saturday).
Then, the formula will use "2017/06/15" as the start date and calculate a date of nine workdays in the future. It will exclude the identified holidays on "2017/06/16" and "2017/06/19” but include the specified working day on "2017/06/24".
The resulting date would be 2017/06/29.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered workdays.
| Formula | Syntax |
|---|---|
| WORKDAY.INTL | WORKDAY.INTL(start_date,days,[weekend_no],["holidays"], ["makeup_workdays"]) |
The WORKDAY.INTL function syntax has the following arguments:
Start_date: Required. A date that represents the start date.
Days: Required. The number of non-weekend and non-holiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
Weekend_no: Optional. If the weekend days are not on Saturday and Sunday, you can use a weekend number that specifies when weekends occur.
Holidays: Optional. An optional list of one or more dates to exclude from the working calendar, such as government and floating holidays. The list should be an array constant of serial numbers representing dates.
Makeup_workdays: Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example:
Apply the formula " WORKDAY.INTL(A1,A2,2,["2017/06/16","2017/06/19"])" in a date field.
When A1 contains the value "2017/06/15", and A2 contains the value "9", the formula will use 2017/06/15 as the start date, take Sunday and Monday as the weekend, and calculate a date nine workdays in the future, excluding the identified holidays on 2017/06/16 and 2017/06/19.
The resulting date would be 2017/06/29.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
Returns the number of whole working days between the start_date and end_date. Working days exclude weekends and any dates identified as holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
| Formula | Syntax |
|---|---|
| NETWORKDAYS | NETWORKDAYS(start_date,end_date,["holidays"], ["makeup_workdays"]) |
The NETWORKDAYS function syntax has the following arguments:
Start_date: Required. A date that represents the start date.
End_date: Required. A date that represents the end date.
Holidays: Optional. An optional list of one or more dates to exclude from the working calendar, such as government and floating holidays. The list should be an array constant of serial numbers representing dates.
Makeup_workdays: Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example 1:
Apply the formula "NETWORKDAYS(E1,E2,['2017/10/04','2017/10/09','2017/10/10'])" in a Numeric field.
When E1 contains the value "2017/10/01" and E2 contains the value "2017/10/31" and the dates "2017/10/04","2017/10/09" and "2017/10/10" are identified to be excluded.
The number of workdays between the start (2017/10/01) and end date (2017/10/31), with the three identified holidays as non-working days ("2017/10/04","2017/10/09", and "2017/10/10") excluded would be 19.
Example 2:
Apply the formula "NETWORKDAYS(E1,E2,['2017/10/04','2017/10/09','2017/10/10'],['2017/10/28','2017/10/29'])" in a Numeric field.
When E1 contains the value "2017/10/01" and E2 contains the value "2017/10/31", with the dates "2017/10/04", "2017/10/09", and "2017/10/10" identified to be excluded, and "2017/10/28 (Sat)" and "2017/10/29 (Sun)" designated as workdays.
The number of workdays between the start date (2017/10/01) and end date (2017/10/31), with the exclusion of the three identified holidays ("2017/10/04", "2017/10/09", and "2017/10/10") but including the specified working days on "2017/10/28 (Sat)" and "2017/10/29 (Sun)", would be 21.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered workdays.
| Formula | Syntax |
|---|---|
| NETWORKDAYS.INTL | NETWORKDAYS.INTL(start_date,end_date,[weekend_no],["holidays"], ["makeup_workdays"]) |
The NETWORKDAYS.INTL function syntax has the following arguments:
Start_date and End_date: Required. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
Weekend_no: Optional. If the weekend days are not on Saturday and Sunday, you can use a weekend number that specifies when weekends occur.
Holidays: Optional. An optional list of one or more dates that are to be excluded from the working day calendar. The list should be an array constant of serial numbers representing dates.
Makeup_workdays Optional. An optional list of one or more dates to include in the working calendar, such as a make-up workday on Saturday. The list should be an array constant of serial numbers representing dates.
Example
Apply the formula "NETWORKDAYS.INTL(E1,E2,11,['2017/06/16'])" in a Numeric field.
When E1 contains the value "2017/06/01" and E2 contains the value "2017/06/30", the 11 argument is used to specify the weekend as Sunday only, and the date "2017/06/16" is identified to be excluded, the formula subtracts 10 nonworking days (four Sundays, one Holiday) from the 30 days between 2017/06/01 and 2017/06/30.
The result is 25 days.
Note: When using the holidays or makeup_workdays arguments, to avoid setting them individually for each sheet, you can define an array Global Constant for consistent dates across the database and use it in the formula. For more details, please refer to this section.
The following number values indicate the following weekend days:
| Weekend number | Weekend day(s) |
|---|---|
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Sunday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
This function returns the week number of a specific date in the year. You can define which day the week begins.
| Formula | Syntax |
|---|---|
| WEEKNUM | WEEKNUM(Date,[return_type]) |
The WEEKNUM function syntax has the following arguments:
Date: The date field to calculate.
Return_type: (Optional) A number that specifies which day the week begins. The default is 1 (Sunday as the first day of the week). Other valid values are listed in the table below.
There are two systems used for this function:
System 1: The week containing January 1 is the first week of the year, and is numbered week 1.
System 2: The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
If there are no specific requirements, it is recommended to use the System 1.
| Return_type | Week begins on | System |
|---|---|---|
| 1 or omitted | Sunday | 1 |
| 2 | Monday | 1 |
| 11 | Monday | 1 |
| 12 | Tuesday | 1 |
| 13 | Wednesday | 1 |
| 14 | Thursday | 1 |
| 15 | Friday | 1 |
| 16 | Saturday | 1 |
| 17 | Sunday | 1 |
| 21 | Monday | 2 |
Example:
The Date field (A2) has the value 2020/01/07 (Tuesday). Using the WEEKNUM function with different syntax yields the following results:
| Formula | Result |
|---|---|
| WEEKNUM(A2) | 2 |
| WEEKNUM(A2, 13)(Week starts on Wednesday) | 1 |
A string formula is straightforward: if the value in C1 is "Michael", and C2 is "Scott", then "C1+C2" will be "MichaelScott". If you want to add a space between two strings, you can use "C1+' '+C2", resulting in "Michael Scott".
Note: If you want to represent "\" in a formula, it should be written as "\\".
You can use the combination of RIGHT() or LEFT() with the FIND() function to find a specific character and get the corresponding string values before and after this character.
In the example below, there is a sheet with "Full Name" as the field header in cell A1. We will extract the first and last name of a person using the space character.
Use LEFT(A1,FIND(" ",A1,1)) for the first name,
and RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) for the last name. Notice that we're looking for the space character with the blank space in between quotation marks (" ") with FIND.
The output is the first name and last name that is extracted from the full name.
Ragic supports the use of conditional formulas. Please be reminded that changing the Field Type may affect the calculation results in some situations.
Note: These are the two circumstances in which you'll need to add ".RAW" to the referenced field upon assigning the conditional formulas:
1. When using the operator "=" to reference two fields that equal the condition of the formula. However, when referring to only one field that equals a fixed value, ".RAW" is not needed.
2. When assigning the formula to a Numeric field using the operator "=" to reference a string field(text, selection, date, etc.) that equals a fixed string value (which will return a numeric value as a result). If you are referencing one Numeric field that equals a value with operator "=", ".RAW" is not needed.
For more details about ".RAW" please refer to this section.
Date fields are calculated as days.
Conditional formulas can also be nested.
The IF function returns one value if a specified condition evaluates to TRUE, and another value if it evaluates to FALSE.
| Formula | Syntax |
|---|---|
| IF | IF(value==condition,value_if_true,value_if_false) |
Examples
Basic example: IF(A2==10,10,0)
If the value in the reference field A2 is equal to 10, the value in this field would be 10. For any other value of A2, the value of this field will be 0.
Having a string value as a result: IF(A1==1,'True','False')
If the value in the reference field A1 is equal to 1, the value in this field would be "True". For any other value of A1, the value of this field will be "False".
Practical usage: IF(A2>=60,'Yes','No')
If the age field is greater than or equal to 60, the value in this field "Qualifies for senior discount?" would be "Yes"; otherwise, the value would be "No".
Note:
Usage of the older syntax of the IF function in Ragic is still supported.
Value=='condition'?'value_if_true':'value_if_false'
Basic Example: A1=='open'?'O':'C'
If A1 is open, give O. If not, give C.
If you would like to reference two fields that are equal to each other with the operator "=" as the condition in conditional formulas, please add .RAW after the referenced fields.
| Syntax |
|---|
| IF(field1.RAW=field2.RAW,value_if_true,value_if_false) |
Examples
Basic Example1: IF(A1.RAW=A2.RAW,1,0)
If the value in the referenced field A1 equals the value in field A2, return 1, otherwise return 0.
Basic Example2: IF(A1.RAW=A2.RAW,'Open','Closed')
If the value in the referenced field A1 equals the value in field A2, return "Open", otherwise return "Closed".
| Syntax |
|---|
| IF(string_field1.RAW="string",numeric_value_if_true,numeric_value_if_false) |
Example: IF(A1.RAW="Yes",1,0)
If the value in the referenced string field A1 equals "Yes", return 1, otherwise return 0.
If you are referencing one Numeric field that equals a value with operator "=", ".RAW" is not needed.
Example:IF(A1=1,'YES','NO')
If the value in the referenced field A1 equals 1, return "YES", otherwise return "NO".
If you would like to use a formula to check whether a field is empty or not, your formula must add .RAW after the referenced field.
For example, the formula IF(A8.RAW='',"TRUE","FALSE") is used to check if the field on cell A8 is empty, and that field's value may be 0. Therefore, .RAW should be added.
Note: Without adding .RAW to your referenced field on your formula, the numerical value "0" will also be considered as an empty value.
For example, if the field on A1 is a free text field with the numeric value "10001", and the field on A2 is a linked field with a conditional formula set to reference and return A1's value "10001", the formula would need to be set as the following: IF(A1!='', A1.RAW)
If you would like to retrieve text from referenced fields by using the functions LEFT(), RIGHT(), and MID(), please add +"" after the field you're referencing.
Example:
IF(A1="Yes",A5,LEFT(A5+"",2))
If the value in the referenced field A1 is equal to "Yes", the value in this field would be the field value of A5. For any other value of A1, the value of this field will be the first 2 digits of A5.
Since the system does not support referencing the value of TODAY() or NOW() within an IF() formula directly, you'll need to create another field that references a field containing the value of TODAY() or NOW().
Example:
If you want to compare the value of the date field A1 to TODAY(), you can create field A2 and configure it with TODAY(). Then, apply the formula: IF(A1>A2,"Valid","Expired")
If the value in the reference field A1 is larger than TODAY(), the value in this field would be "Valid". For a value that is smaller than TODAY(), the value of this field will be "Expired".
On the other hand, if you want to use the TODAY() formula or the field assigned the TODAY() formula as the referenced conditional field for calculations in IF() formulas, you can create field A2 for whole calculations as follows: A1-TODAY(). Then, apply the formula: IF(A2>0,"Valid","Expired")
Note: The value of TODAY() or NOW() will not be auto-recalculated once the entries are saved. If it's necessary to recalculate the TODAY() or NOW() value, you'll need to Apply a Daily Workflow.
1. Apply on the non-Date field to compare the values of Date fields
For example, A1 and A2 are date fields, applying TODAY() to A2. In A4, enter the formula: IF(A1>A2,"Yes","No")
2. Apply on Numeric fields to execute addition or subtraction operations for Date fields
For example, it's not supported to enter the formula IF(A1-1>A2,"Yes","No") in A4. You will need to create the other two numeric fields C1 and C2. In C1, enter the formual: A1-1. In C2, enter A2. In A4, change the formula to IF(C1>C2,"Yes","No").
The conditional process in formulas can also be done with the LOOKUP function, which is the equivalent of conditional processing.
| Formula | Syntax |
|---|---|
| LOOKUP | LOOKUP(value,lookup_list,[result_list]) |
LOOKUP searches for the value in a one-column or one-row range (lookup_list) and returns the corresponding value from another one-column or one-row range (result_list).
value: Required. The value to search for in the lookup_range.
lookup_list: Required. An array like [0,100,500]. The LOOKUP function searches for a value in this list, which would need to be in ascending order.
result_list: Optional. An array that is the same size as the lookup_range like ['Small','Medium','Large']. If the result_list parameter is omitted, the LOOKUP function will return the value in the lookup_list. If the LOOKUP function cannot find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. If the value is smaller than all of the values in the lookup_range, the LOOKUP function will return an empty string.
Examples
Basic Example: LOOKUP(A1,[0,45,65],['Small','Medium','Large'])
The value would be 'Small' if the value of A1 is between 0 and 44, 'Medium' for 45~64, and 'Large' for greater than or equal to 65.
Referencing multiple fields: LOOKUP(A1,[0,45,65],[A3+A4,B5,B6])
The value would be A3+A4 if the value of A1 is between 0 and 44, B5 for 45~45, and B6 for greater than or equal to 65.
Returns TRUE if all test conditions evaluate to TRUE; returns FALSE if one or more conditions evaluate to FALSE.
| Formula | Syntax |
|---|---|
| AND | AND(logical1, [logical2], ...) |
The AND function syntax has the following arguments:
logical1: Required. The first test condition that can evaluate to either TRUE or FALSE.
logical2, ...: Optional. Additional test conditions that can evaluate to either TRUE or FALSE or be arrays or references that contain logical values.
Returns TRUE if any test condition is TRUE; returns FALSE if all conditions are FALSE.
| Formula | Syntax |
|---|---|
| OR | OR(logical1, [logical2], ...) |
The OR function syntax has the following arguments:
logical1: Required. The first test condition that can evaluate to either TRUE or FALSE.
logical2, ...: Optional. Additional test conditions that can evaluate to either TRUE or FALSE or be arrays or references that contain logical values.
Returns TRUE if the test condition is FALSE, and FALSE if the condition is TRUE.
| Formula | Syntax |
|---|---|
| NOT | NOT(logical) |
Example
NOT(A2>10)
If the value in reference field A2 is less than or equal to 10, the system returns "true"; otherwise, it returns "false".
Use COUNTIF to count the number of rows in a Subtable that met a specified criterion; for example, to count the number of times a particular item appears in a receipt.
| Formula | Syntax |
|---|---|
| COUNTIF | COUNTIF(criteria_range,criteria) |
The COUNTIF function syntax has the following arguments:
criteria_range: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria: Required. The criteria that defines which cells in criteria_range will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
COUNTIF can only refer to a single Subtable and needs to be set in independent fields.
COUNTIF uses only a single criterion. Use COUNTIFS if you want to use multiple criteria.
Example:
The Formula COUNTIF(A4,'apple') entered field A9 returns the number of rows that Subtable column A4 contains for the product name apple.
| Formula | Syntax |
|---|---|
| COUNTIFS | COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...) |
The COUNTIFS function syntax has the following arguments:
criteria_range1: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria1: Required. The criteria that defines which cells in criteria_range1 will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
criteria_range2, criteria2,...: Optional. Rows are counted when additional criteria_ranges match their associated criteria.
COUNTIFS can only refer to a single Subtable and needs to be set in independent fields.
Use SUMIF to sum up the values stored in a specified Subtable row that meet a single criterion; for example, to sum up the monetary value of a specific merchandise item when it appears in a receipt.
| Formula | Syntax |
|---|---|
| SUMIF | SUMIF(range,criteria,[sum_range]) |
The SUMIF function syntax has the following arguments:
range: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria: Required. The criteria that defines which cells in range will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
sum_range: Optional. The actual fields to add, if you want to add values within Subtable fields other than those specified in the range argument. If sum_range is omitted, only the fields that are specified in the range argument will be added (the same fields to which the criteria are applied).
SUMIF can only refer to a single Subtable and needs to be set in independent fields.
SUMIF uses only a single criterion. Use SUMIFS if you want to use multiple criteria.
Example:
The Formula SUMIF(A4,'apple',B4) that is entered into field A9 returns the sum of the values in Subtable column B4, when Subtable field header A4 is the product name apple.
Use SUMIFS to sum up the value stored in a specified Subtable row that meets multiple criteria; for example, to sum up the monetary value of a number of specific merchandise items in specific store locations when it appears in a receipt.
| Formula | Syntax |
|---|---|
| SUMIFS | SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2, criteria2,...) |
The SUMIFS function syntax has the following arguments:
sum_range: Required. This range must be a Subtable field to be checked for values that fit specified criteria.
criteria_range1: Required. criteria_range1 and criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in sum_range are added.
criteria1: Required. The criteria that defines which cells in criteria_range1 will be added. This can be a number, expression, reference to another field, or text string that determines which values will be added. Reference the table below:
| Application | Examples of Formula Input |
|---|---|
| Reference Number | "8" |
| Number Comparison | "> 8"、"< 8"、"!= 8" |
| Reference Text String | "apple" |
| String Inequality | "!='apple'"(Use different quotes outside and inside, for example, double quotes outside and single quotes inside) |
| Reference the Subtable Field | A4 (No need to add double quotes or "="; just write the field name) |
| Comparison with the Subtable Field | "> A4"、"< A4"、"!=A4" |
criteria_range2,criteria2,...: Optional. Additional ranges to be added and their associated criteria.
In the case that you want to apply multiple criteria to a single field, for example, the sum of the A1 field is equal to A or equal to B, you need to use multiple SUMIF() instead of SUMIFS().
SUMIFS can only refer to a single Subtable and needs to be set in independent fields.
Example:
The Formula SUMIFS(C11,B11," > A16",B11," < A17") in the field A18 returns the sum of values Subtable column C11 when the order date (the value B11) is later than that A16 and earlier A17 field.
Use UPDATEIF to retain the old value in the field if the condition is false. The value in a field where the UPDATEIF function is used should change only if the condition being tested by the UPDATEIF Function is true.
| Formula | Syntax |
|---|---|
| UPDATEIF | UPDATEIF(condition,value_if_true) |
Examples:
Basic Example: UPDATEIF(A2==10,10)
If the value in the reference field A2 is equal to 10, the value in this field would be 10. For any other value of A2, the value of this field would remain unchanged from the previously saved version of the record.
Practical Example: UPDATEIF(A2=='Same as home address',A1)
If the value in field A2 is 'Same as home address', the value in the field "Shipping address" would be A1 (home address); otherwise, the value would remain empty.
Conditional formulas can be nested when multiple conditions must be met.
Example 1:
IF(A1==1,'Bad',IF(A1==2,'Good',IF(A1==3,'Excellent','No Valid Score')))
The above formula means that:
if A1 is 1, the result is "Bad".
if A1 is 2, the result is "Good".
if A1 is 3, the result is "Excellent".
if A1 is anything else, the result would be "No Valid Score".
Example 2:
IF( AND(A1.RAW=='YES',A2.RAW=='Jimmy'), C3*C7, IF( AND(A1.RAW=='YES',A2.RAW=='John'), C3*C8, IF( AND(A1.RAW=='YES',A2.RAW=='Jane'), C3*C9, C3*C10 ) ) )
The above formula means that:
if A1 has the value "YES", and A2 has the value "Jimmy", the result is C3*C7.
if A1 has the value "YES", and A2 has the value "John", the result is C3*C8.
if A1 has the value "YES", and A2 has the value "Jane", the result is C3*C9.
if these conditions do not apply, then the result is C3*C10.
In addition to the nested conditional formulas, you can also use IFS() to check whether one or more conditions are met, and return a value that corresponds to the first TRUE condition.
| Formula | Syntax |
|---|---|
| IFS() | IFS(value=condition1,value_if_true1,value=condition2,value_if_true2,...,true,default value) |
The IFS function syntax has the following arguments:
value=condition1: Required. The first condition evaluates to TRUE or FALSE.
value_if_true1: Required. Result to be returned if value=condition1 evaluates to TRUE.
value=condition2: Required. The second condition evaluates to TRUE or FALSE.
value_if_true2: Required. Result to be returned if value=condition2 evaluates to TRUE.
*You need to set at least two sets of conditions. You can apply more if needed.
true: Optional. Please input "true" if you want to set the default value when none of the other conditions are met.
default value: Optional. The result is returned if none of the other conditions are met.
Example:
IFS(A1=1,"Bad",A1=2,"Good",A1=3,"Excellent",true,"No Valid Score")
The above formula means that:
if A1 is 1, the result is "Bad".
if A1 is 2, the result is "Good".
if A1 is 3, the result is "Excellent".
if A1 is anything else, the result would be "No Valid Score".
| Formula | Syntax |
|---|---|
| INCLUDES_ALL | INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) |
| NOT_INCLUDES_ALL | NOT_INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) |
| INCLUDES_ANY | INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) |
| NOT_INCLUDES_ANY | NOT_INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) |
| ITEMS_COUNT | ITEMS_COUNT(Multiple select/ multiple image/ file upload field) |
Using INCLUDES_ALL as an example, first apply INCLUDES_ALL(D1, A1, A2, B4) to D2.
A1 = Free text field, the field value is "A"
A2 = Multiple select field, the field values are B, C
B4 = Free text field in subtable with two records. The value in the first record is "D"; in the second record is "E"
D1 = Multiple select field, the field values are A, B, C, D, E
D2 will return "true".
Formula calculations will be executed when you edit referenced fields, and the values are saved when you save the entry.
By default, the values that are already saved will not change when you modify the formula while designing your sheet. This is because, in most cases, a previous calculation is still valid for older entries and should not be overwritten when you have updated the formula. A practical example would be calculating taxes after a tax hike; all previous entries would still need to reflect the older tax rate.
In some cases, you may need to recalculate a formula on all previous entries. To do so, you can choose to apply the formula change to all saved records, or, if you have modified more than one formula, apply all formulas on this sheet to all saved records.
Remember to save the design first before recalculating formulas in Design Mode.
Besides manually applying formula recalculation, you can also recalculate through the Workflow. Additionally, if you frequently change formulas or use TODAY(), you may consider using Daily Workflow to recalculate formulas daily.
Note: With workflow formula recalculation, there are two situations where changes will not be recorded in the entry history.
1. No changes are made after recalculation.
2. To optimize system performance, recalculations exceeding 3,500 entries will not be logged in the history (although the recalculation still executes normally).
To run a formula recalculation on related records on other sheets, go to Form Settings > Form Settings > Recalculate all formulas on parent or related sheets.
Parent Sheets:
In the above example, A and B are parent sheets of C.
Related sheets:
B and C are related sheets of A; A and C are related sheets of B.
Note: Currently, the limit number of recalculated records is 1000. If the number of records to be recalculated exceeds the system's limitation, it will ignore the setting to recalculate all formulas on related sheets.
The diagram below shows the design concepts and logic between parent sheets, child sheets, and related sheets.
Formulas also work in Description Fields for display purposes only.
This is useful if you need to recalculate a formula each time your database form is loaded, but do not need to keep this value in your database. You will need to use the BBCode [formula] for your formula to work.
For example, to display a person's age based on their birthday, you can use the formula [formula](TODAY() - A1)/365.25[/formula] in a description field. This formula will continuously update to show the person's age according to the current date.
About the Math Objects supported in Ragic, please refer to this page.
If you need to use other unsupported formulas, please write to Ragic Support to suggest them.
Thank you for your valuable feedback!
