Database Design Documentation
How can we help?
Full Site Search

Formulas

Usage of Formulas in Ragic

Formulas in Ragic work similarly to those used in spreadsheet applications such as Excel. In Excel, we refer to fields by referring to the cells containing their field values. However, in Ragic, we assign formulas by referring to the field containing their field header. This is because Ragic subtables allow multiple cells to contain field values for a field.

Formulas can be used to calculate not only numbers, but also strings and dates. Ragic will try to automatically determine what type of formula is needed, but it's best to specify the Field Type (such as Number or Date) to ensure accuracy.

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 and enter your formula into the Formula tab.

Below you can see an example of a Sales Order sheet complete with calculations. The Amount Due (A20) field has the formula that will add and calculate the Total (A17) and Tax (A19) fields.

There is a fx() icon in the field assigned with formula.

Clicking on the icon, the system will automatically highlight all of the referenced fields of this formula.

Note: The "Multiple Select" field type cannot be configured as a reference field in formulas.

For a list of supported formulas in Ragic, please see below.

Calculation Operators in Ragic

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.

Arithmetic Operators

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

Comparison Operators

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

Strings in Formulas

To create strings in formulas, you can use either single quotes, e.g. 'Single Quoted String', or double quotes, e.g. "Double Quoted String" to denote a string in the formula. In this document we will use single-quoted strings for consistency, but please note that both formats are acceptable in Ragic.

Values in Subtables

Assigning formulas to field headers makes calculations much easier while allowing you to write more complicated formulas with less effort, especially in Subtables.

Let's go back to our Sales Order example. The subtable that lists the order information includes Unit price (B12) and Quantity (C12). Multiplying these variables will calculate the total amount of money (D12) owed for each item. Notice how the subtotal of this amount in field A17 references the amount in field header D12.

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

Calculate unique values in subtable

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 referenced subtable field. The default separator for subtable values would be ",". If you don't modify the separator in your formula, the result will use the default separator and would look like the image below. If you would like to configure your own 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 referenced subtable field.

For example:

Return specific field value when criteria matched

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 (A2 field in the below example).

returnField Required. The subtable field containing the return value (A9 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 or not the returnFiled returns multiple values. The default value is false. If there are multiple entries that may match the criteria, please set it to "true".

Example

Let's say you want to find the teacher of a specific class in the subtable below:

You may create a new free text field for 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. The formula will return the teacher's name according to the query class inputted.

Calculating Dates and Times

Formulas that refer to date fields

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 of the person who has that birthday.

Check the list of supported formulas below for detailed information about formulas that work with dates.

Formulas that refer to time fields

To calculate the time differences within a single day, you can use time fields with formatting (HH:mm).

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 time 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".


If your start and end times span different dates, you will need to use a date field with formatting that contains 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 need to use a numeric field for A3 with formatting (0.0) and the formula "(A2-A1)*24".

WORKDAY Function

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 take into account working and non-working days.

Formula Syntax
WORKDAY WORKDAY(start_date,days,["holidays"], ["makeup_workdays"])

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. It is recommended for users to simply enter date values, but optionally, an array constant of the serial numbers that represent the dates can also be used by advanced users. By default, "January 1, 1900" is serial number 1, and "January 1, 2008" is serial number 39448 because it is 39,448 days after January 1, 1900.

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.

Example

Apply the formula "WORKDAY(A1,A2,["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 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.

WORKDAY.INTL Function

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 as workdays.

Formula Syntax
WORKDAY WORKDAY.INTL(start_date,days,weekend_no,["holidays"], ["makeup_workdays"])

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. It is recommended for users to simply enter date values, but optionally, an array constant of the serial numbers that represent the dates can also be used by advanced users. By default, "January 1, 1900" is serial number 1, and "January 1, 2008" is serial number 39448 because it is 39,448 days after January 1, 1900.

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.

Example

Apply the formula "WORKDAY(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.

NETWORKDAYS Function

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"])

Start_date Required. A date that represents the start date.

End_date Required. A date that represents the end date.

Holidays Optional. An optional range of one or more dates to exclude from the working calendar, such as government and floating holidays. It is recommended for users to simply enter date values, but optionally, an array constant of the serial numbers that represent the dates can also be used by advanced users as above.

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.

Example

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.

NETWORKDAYS.INTL Function

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 as workdays.

Formula Syntax
NETWORKDAYS.INTL NETWORKDAYS.INTL(start_date,end_date,weekend_no,["holidays"], ["makeup_workdays"])

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 set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a date, or an array constant of the serial values that represent those dates as above. The ordering of dates or serial values in holidays can be arbitrary.

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.

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.

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

List of Supported Formulas

Below is a short list of the supported formulas in Ragic. Please note that the following formulas are case-sensitive.

Formula Description
SUM(value) Returns the sum of (adds) all the field values. You can add individual values, field references or ranges, or a mix of all three. The call to SUM() is actually unnecessary, as it's equivalent to just "value".
AVG(value1, value2,...) Returns the average (arithmetic mean) of all the listed field values. Field values can either be numbers or names, ranges, or field references that contain numbers. Using the average function also works for subtables, but please note that the average of all field values that are being referenced will be added to the calculation.
AVERAGE(value1, value2,...) Returns the average (arithmetic mean) of all the listed field values. Field values can either be numbers or names, ranges, or field references that contain numbers. Using the average function also works for subtables, but please note that the average of all field values that are being referenced will be added to the calculation.
MIN(value) Returns the smallest number in a set of field values. Field values can either be numbers or names, arrays, or field references that contain numbers. Using the minimum function also works for subtables.
MAX(value) Returns the largest numeric value in a range of field values. Field values can either be numbers or names, arrays, or field references that contain numbers. Using the maximum function also works for subtables.
MODE.SNGL(value1,[value2],...) Returns the most common value in a range of field values. Field values can either be numbers or names, arrays, or field references that contain numbers. This function also works for subtables and global constants.
MODE.MULT(value1,[value2],...) Returns multiple most common values in a range of field values. Field values can either be numbers or names, arrays, or field references that contain numbers. This function also works for subtables and global constants.
ABS(value) Returns the absolute value of a number. The absolute value of a number is the number without its sign.
CEILING(value) Returns the smallest integer greater than or equal to a given number. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.
FLOOR(value) Returns the largest integer less than or equal to a given number.
ROUND(value) Rounds a number to the nearest integer.
ROUND(value,N) Rounds a number to N decimal place.
ROUNDUP(value,N) Rounds up a number (away from zero) to N decimal place.
ROUNDDOWN(value,N) Rounds down a number (toward zero) to N decimal place.
MROUND(number,N) Rounds a number to the nearest multiple of N
SQRT(value) Returns the square root of a number.
COUNT(value1,value2,...) Returns the total number of field values. Empty values will not be counted when referencing independent fields but will be counted when referencing subtable fields.
LEFT(value,length) Returns the first character or characters (from the left side) of a text string, based on the number of characters you specify.
RIGHT(value,length) Returns the last character or characters (from the right side) of a text string, based on the number of characters you specify.
MID(value,start,[length]) Extracts a given number of characters from the middle of a supplied text string. For the starting character, the first character on the referenced field will be specified as 0. For example, if the value on field A1 is ABCD, setting the formula as MID(A1,1,2) on another field will return BC.
FIND(find_text,within_text,[start_num]) Locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string.
LEN(value) Returns the number of characters in a text string.
TODAY() Returns the current date. In case of automatically daily recalculation, please replace TODAY() with TODAYTZ().
TODAYTZ() Returns the current date according to Company Local Time Zone in your Account Settings.
NOW() Returns the current date and time.
NOWTZ() Returns the current date and time according to Company Local Time Zone in your Account Settings.
EDATE(start_date, months) Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. Both "start_date" and "months" are required, and the start_date needs to be a date field.
EOMONTH(start_date, months) Returns the serial number for the last day of the month that is a specified number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. Both "start_date" and "months" are required, and the start_date needs to be a date field.
YEAR() Returns the year value of a date field
MONTH() Returns the month value of a date field
DAY() Returns the day value of a date field
DATE(year,month,day) Combines values in referenced numeric fields into a date. Please use four-digit years to prevent confusion.
WEEKDAY() Returns the day of the week, using numbers 1 (Sunday) through 7 (Saturday)
PI() Returns the number 3.14159265358979, the mathematical constant pi and the ratio of the circumference of a circle to its diameter, accurate to 15 digits.
RAND() Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
UPPER(value)/TOUPPERCASE(value) Coverts all lowercase letters in a text string to uppercase without changing the original string.
LOWER(value)/TOLOWERCASE(value) Coverts all uppercase letters in a text string to lowercase without changing the original string.
PROPER(value) Capitalizes the first letter in a text string and any other letters in a text that follow any character other than a letter. Converts all other letters to lowercase letters.
SUBSTITUTE(text,old_text,new_text,[instance_num]) Substitutes new_text for old_text when you want to replace specific text in a text string.
TEXT() Formats a number or date value into a specified format. For details, click here.
POWER(value,power) Returns the result of a number value raised to a power.
MOD(value,divisor) Returns the remainder after a number value is divided by a divisor. The result has the same sign as the divisor.
GCD(value1,[value2],...) Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides the specified number values without a remainder.
LCM(value1,[value2],...) Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments value1, value2, and so on. Use LCM to add fractions with different denominators.
FIRST(value) Returns the first data point of the column in your subtable.
FIRSTA(value) Returns the first not data point that is not empty of the column in your subtable.
LAST(value) Returns the last data point of the column in your subtable.
LASTA(value) Returns the last data point that is not empty of the column in your subtable.
IF(value==condition,value_if_true,value_if_false) Returns one value if the condition evaluates to TRUE, or another value if the condition evaluates to FALSE. For details, click here.
IFS(value=condition1,value_if_true1,value=condition2,value_if_true2,...,true,default value) Check whether one or more conditions are met, and return a value that corresponds to the first TRUE condition. For details, click here.
LOOKUP(value,lookup_list,[result_list]) 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). For details, click here.
AND(logical1, [logical2], ...) Returns TRUE if all its conditions evaluate to TRUE; returns FALSE if one or more conditions evaluate to FALSE. For details, click here.
OR(logical1, [logical2], ...) Returns TRUE if any condition is TRUE; returns FALSE if all conditions are FALSE. For details, click here.
NOT(logical) Returns TRUE if the test condition is FALSE, and FALSE if the condition is TRUE. For details, click here.
COUNTIF(criteria_range,criteria) Returns the number of values in a range within a subtable field that meet a single specified criterion. For details, click here.
COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...) Applies criteria to fields across multiple ranges and counts the number of times all criteria are met. For details, click here.
SUMIF(range,criteria,[sum_range]) Returns the sum of values in a range that meet a single specified criterion. For details, click here.
SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2, criteria2],...) Adds all arguments that meet specified criteria. For details, click here.
UPDATEIF(condition,value_if_true) Modifies a field value when at least one condition is met. For details, click here
REPT(value,number_times) Returns the repeated value a given number of times.
COUNTA(value) Counts the number of subtable rows that are not empty.
SUBTABLEROW(value,nth_row) Returns the targeted data of the column in your subtable, this can only be set in stand-alone fields.
RUNNINGBALANCE(value, [allow_backend_formula_recalculation=false]) Returns the sum of the values in this row and the previous row of the column in your subtable, used for calculating running balances. "Allow_backend_formula_recalculation=true" means allowing backend formula recalculation for this formula. To use this formula, your subtable records must be created with the correct order.
WORKDAY(start_date,days,["holidays"], ["makeup_workdays"]) Returns a number that represents a date that is the indicated number of working days before or after a given date. For details, click here
WORKDAY.INTL(start_date,days,weekend_no,["holidays"], ["makeup_workdays"]) Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.For details, click here
NETWORKDAYS(start_date,end_date,["holidays"], ["makeup_workdays"]) Returns the number of whole working days between a start_date and an end_date. For details, click here.
NETWORKDAYS.INTL(start_date,end_date,weekend_no,["holidays"], ["makeup_workdays"]) Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. For details, click here.
CHAR(value) Returns a character when given a valid character code. For example, CHAR(10) returns a line break, and CHAR(32) returns a space.
LARGE(arg, nth, ["arg2"]) Refers to the subtable field(s) and checks the ordinal value of one column while returning the value of another column in the same row. The referenced field "arg2" needs to be in the same subtable as "arg". The formula will sort your entries in descending order in the backend and return the field value of the specified ordinal number.
UNIQUE() Lists the unique values of referenced subtable field. For details, click here.
UNIQUE().length Calculates the number of unique values of referenced subtable field. For details, click here
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) Returns the maximum value among cells specified by a given set of conditions or criteria.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) Returns the minimum value among cells specified by a given set of conditions or criteria.
SPELLNUMBER(number, [lang]) You will see numbers that are written in words in some formal documents. For example, use "one hundred" instead of "100". You can use SPELLNUMBER formula if you need to see numbers in words in your sheets. For details, click here.
ISOWEEKNUM(date) Returns the number of the ISO week number of the year for a given date. Every week begins on Monday.
WEEKNUM(Date,[return_type]) Returns the week number of a specific date in that year, you can define which day the week begins. For details, click here.
DATEVALUE(date_text, date_format) Applied on date (time) fields where you can convert a referenced date of a free text field to a date (time) value. For this formula, "date_text" is the date in a free text field that you will be referencing, and "date_format" is the format of the referenced field with the date. For example, if A1 is a free text field with the value “2019/02/01” and you would like to convert it to a value on the date field, you can use the formula DATEVALUE(A1,"yyyy/MM/dd") on the date field to obtain the converted result.
HOUR() There are three ways to use this formula:

1. Setting the parameter as a numerical value between 0-1 will return the number of hours in regards to the proportion of 24 hours defined by the parameter. For example: HOUR(0.5)=12.

2. Setting the parameter as a date field will return the field’s hour value. For example, if field A9’s value is 2020/10/30 18:30:19, HOUR(A9)=18.

3. Setting the parameter as a date will return the hour value. For example, HOUR(“2020/10/13 17:35:22”)=17.

MINUTE() There are three ways to use this formula:

1. Setting the parameter as a numerical value between 0-1 will return the number of minutes in regards to the proportion of 60 minutes defined by the parameter. For example: MINUTE(0.5)=30

2. Setting the parameter as a date field will return the field’s minute value. For example, if field A9’s value is 2020/10/30 18:50:19, MINUTE(A9)=50.

3. Setting the parameter as a date will return the minute value. For example, MINUTE(“2020/10/13 17:35:22”)=35.

SECOND() There are three ways to use this formula:

1. Setting the parameter as a numerical value between 0-1 will return the number of seconds in regards to the proportion of 60 seconds defined by the parameter. For example: SECOND(0.5)=30

2. Setting the parameter as a date field will return the field’s second value. For example, if field A9’s value is 2020/10/30 18:50:19, SECOND(A9)=19.

3. Setting the parameter as a date will return the second value. For example, SECOND(“2020/10/13 17:35:22”)=22.

TIME(hour, minute, second) The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Hour: A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = 0.125 or 3:00 AM.

Minute: A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = 0.520833 or 12:30 PM.

Second: A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = 0.023148 or 12:33:20 AM

ISBLANK() Checks whether the referenced field is empty. For example, ISBLANK(A2) or IF(ISBLANK(A2), 'Y', 'N').
PMT(rate, nper, pv, [fv], [type]) Calculates the payment for a loan.

rate (Required): The interest rate.
nper (Required): The total number of payments for the loan
pv (Required): The present value or principal
fv (Optional): The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0, that is, the future value of a loan is 0.
type (Optional.): Fill 0 or omitted, if payments are due at the end of the period. Fill 1, If payments are due at the beginning of the period.

PRODUCT() Multiplies all the numerical values in referenced fields (neglecting empty and text values). You can also reference a subtable field to multiply all the numeric values of that field.
TRIM() Remove fullwidth and halfwidth spaces at the beginning and the end of a field value. And if there are multiple fullwidth and halfwidth spaces between texts, it will only keep the first space. Example: TRIM(" a c ") will get "a c".
INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) If all the listed values (which can be of any field type or value) are contained in the options, returns true. For details click here.
NOT_INCLUDES_ALL(Multiple select/ image/ file upload field, value1, value2,...) If all the listed values (which can be of any field type or value) are not contained in any of the options, returns true, equivalent to the inverse of INCLUDES_ANY. For details click here.
INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) If at least one of the listed values (which can be of any field type or value) is contained in any of the options, returns true. For details click here.
NOT_INCLUDES_ANY(Multiple select/ image/ file upload field, value1, value2,...) If none of the listed values (which can be of any field type or value) is contained in any of the options, returns true, equivalent to the inverse of INCLUDES_ALL. For details click here.
ITEMS_COUNT(Multiple select/ multiple image/ file upload field) Returns the number of values in a multiple select field. For example, if three options are selected in a multiple select field, returns 3; if there are two files in a file upload field, returns 2.

Formulas in Strings

A string formula is pretty straightforward: if the value on C1 is Michael, and C2 is Scott, then C1+' '+C2 will be "Michael Scott".

Further to the previous example, if you want to add a fixed string into your formulas, please mark the string with either single quotes or double quotes. For example, C1+" "+C2+" "+"Jr.". Then, the result would be "Michael Scott Jr.".

Note: If you want to represent "\" in a formula, it should be written as "\\".

Concatenating Text

As a more advanced example, we will create a field that will display an address in standard postal format for shipping purposes in the U.S.

Make sure you have all the fields that you need to display the information required.

Here we would like to have the field header Full Shipping Address display the title and name of the customer, with the shipping address written in the standard postal format. We add the following formula to the field settings:

A3+' '+A4+' '+D14+' '+D13+' '+D12+' '+D10+' '+D11

Now that the Full Shipping Address is displayed, we can use information from this field whenever we need a full address, for example when printing shipping labels.

Extracting Text before and after a specified character

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, we will get the first and last name of a person, using the space character.

Our form design is quite simple, with Full Name as the field header A1.

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.

Conditional Formulas

Ragic supports the use of conditional formulas. Please be reminded that conditional formulas are case sensitive, and 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 IF() formulas:

1. When using the operator "=" to reference two fields that equal each other as the condition of the formula, and

2. When assigning the formula to a numeric field using the operator "=" to reference a string field that equals a fixed string value (which will return a numeric value as a result).

Nevertheless, when referring to only one numeric field that equals to a value, ".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

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.

When to add .RAW?

1.Referencing two fields that are equal to each other with the IF function

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

2.Referencing a string field that equals a fixed string value as the condition and returns a numeric value as the result with the IF function

Syntax
IF(string_field1.RAW="string",numeric_value_if_true,numeric_value_if_false)

Examples

Basic Example1: 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 to a value with operator "=", ".RAW" is not needed.

Examples

IF(A1=1,'YES','NO')

If the value in the referenced field A1 equals 1, return "YES", otherwise return "NO".

3.Determining Whether a Field is Empty or Not

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, if you would like to see if the field on cell A8 has an empty value, your formula would need to be like the following.

IF(A8.RAW='','TRUE','FALSE')

Note: Without adding .RAW to your referenced field on your formula, the numerical value “0” will also be considered as an empty value.

4. Referencing the numeric value of a free text field and returning it as a text string

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)

Retrieving text with the IF function

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.

Referencing TODAY() or NOW() with the IF function

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 formulas as below:

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 as below:

IF(A2>0,"Valid","Expired")

Please kindly note that 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.

Date field with the IF function

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 operation 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 LOOKUP Function

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.

The AND Function

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.

The OR Function

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.

The NOT Function

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

The COUNTIF Function

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. A number, expression, reference to another field, or text string that determines which values will be included. For example, you can use a number like 8, a comparison like ">8", a field header like A4, or a word like "apple".

COUNTIF can only refer to a single subtable and can be set in stand-alone fields.

COUNTIF uses only a single criterion. Use COUNTIFS if you want to use multiple criteria.

Example:

The Formula COUNTIF(A4,'apple') entered into field A9 returns the number of rows that subtable column A4 contains for the product name apple.

The COUNTIFS Function

Formula Syntax
COUNTIFS COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...)

The COUNTIFS function syntax has the following arguments:

criteria_range1 Required. This first range must be a subtable field to be checked for values that fit a set of specified criteria.

criteria1 Required. A number, expression, reference to another field, or text string that determines which values will be counted. For example, you can use a number like 8, a comparison like ">8", a field header like A4, or a word like "apple".

criteria_range2, criteria2,... Optional. Rows to be counted when additional criteria_ranges matches their associated criteria.

COUNTIFS can only refer to a single subtable and can be set in stand-alone fields.

The SUMIF Function

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. A number, expression, reference to another field, or text string that determines which values will be added. For example, you can use a number like 8, a comparison like ">8", a field header like A4, or a word like "apple".

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 can be set in stand-alone 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.

  

The SUMIFS Function

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. For example, you can use a number like 8, a comparison like " > 8", a subtable field header like A4, or a word like "apple".

criteria_range2,criteria2,... Optional. Additional ranges to be added and their associated criteria.

SUMIFS can only refer to a single subtable and can be set in stand-alone fields. 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().

Example

The Formula SUMIFS(C11,B11," > A16",B11," < A17") in the field A18 returns sum of values subtable column C11 when order date (the value B11) is later than that A16 and earlier A17 field.

The UPDATEIF Function

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.

Nesting Conditional Formulas

Conditional formulas can be nested when multiple conditions must be met.

Example:

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:

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.

IFS()

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. Result to be 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 for Multiple Select Field

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 Recalculation

A calculation based on the formula you have entered will be done while you are first entering data into your database. This value is saved when you first save your entry.

By default, the values that are already saved in your database will not change when you change 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.

If you frequently change a formula or apply TODAY(), you also have the option to add a script that will recalculate your formula every day. Please note that, when recalculating records exceeding 5000 through workflow, changes will not be included in entry history.

Triggering formula recalculation on parent or related sheets

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.

The definition of parent and related sheets

Parent Sheets:

In the above example, A and B are parent sheets of C.

  • The sheet which generates a new sheet from its subtable (sheet A) is the parent sheet of the new sheet (sheet C).
  • The sheet which shows references from existing sheets (sheet B) is also the parent sheet of the existing sheet (sheet C).
  • Related sheets:

    B and C are related sheets of A; A and C are related sheets of B.

  • Two sheets are called related sheets if they share the same subtable and are not each other's multiple versions (sheet A and B).
  • Therefore, to sheets A and B, sheet C (which is generated from the subtable in sheet A) is their related sheet.
  • Note: The setting to recalculate all formulas on related sheets will be ignored if the number of records to be recalculated exceeds the system's limitation. Currently, the number of recalculated records is 1000.

    The diagram below shows the design concepts and logic between parent sheets, child sheets, and related sheets.

    Formulas in Description Fields

    Formulas also work in Static Text 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, let's say that you would like to view a person's age according to their birthday. The formula [formula](TODAY() - A1)/365.25[/formula] written in a description field would display the age of this person and will be recalculated according to the current day.

    Math Objects supported in Ragic

    About the Math Objects supported in Ragic, please refer to this page.

    Suggest a Formula

    If you need to use other unsupported formulas, please write to Ragic Support to suggest them.

    Top of Page Table of Contents

    Start Ragic for Free

    Sign up with Google