How can we help?

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.

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 |

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

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.

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.

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:

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.

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.

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

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

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

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

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 | Sunday only |

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

COUNTIF(criteria_range,criteria_count_range) | 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,[true]) | Returns the sum of the values in this row and the previous row of the column in your subtable, used for calculating running balances. Including "true" allows 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. |

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

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

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.

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.

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

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

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.

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

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

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,count_range) |

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

**count_range** is required. This range will be counted once the field value in criteria_range matches for criteria.

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',A4) entered into 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 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,...** Required. Rows to be counted when criteria_range1 matches criteria.

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

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 the sum of the values in subtable column C11 when the order date (the value of the subtable column B11) is later than that in A16 field and earlier than that in 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:**

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.

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

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, to apply all formulas on this sheet to all saved records.

If you frequently change a formula or a variable that is used in a formula, you also have the option to add a script that will recalculate your formula every day.

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:** 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 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.

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.