Formulas in Ragic work very similar to the formulas you use on spreadsheet software such as Excel. But the major difference is, instead of assigning formulas to field values, you **assign cell references to field headers**. Formulas can calculate not only numbers, but also strings and dates. Ragic will try to determine what type of formula that is needed automatically, but it's safer to assign the right field type (such as Number or Date) to be more specific.

To assign a formula to a field header, navigate to the **Design Mode** on your form page, and focus on the field header. On the left hand side, choose the tab **Formula**, and write your formula here.

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

**Please kindly note that "Multiple Select" field type cannot be configure as reference field in formulas. **

Check all the supported formulas in the list of supported formulas below.

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using parentheses.

**Please note that a colon (:) character cannot 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, use the following arithmetic operators.

Arithmetic operator |
Meaning |
Example |
---|---|---|

+ (plus sign) | Addition | 3+3 |

– (minus sign) | Subtraction | 3–1 |

* (asterisk) | Multiplication | 3*3 |

/ (forward slash) | Division | 3/3 |

^ (caret) | Exponentiation | 3^2 |

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE that can be used within conditional formulas.

Comparison operator |
Meaning |
Example |
---|---|---|

= | Equal to | A1=B1 |

== | Equal to | A1==B1 |

> | Greater than | A1>B1 |

< | Less than | A1<B1 |

>= | Greater than or equal to | A1>=B1 |

<= | Less than or equal to | A1<=B1 |

!= | Not equal to | IF(A1!=B1,'yes','no') |

You can use either single quote like: 'Single Quoted String' or double quote like: "Double Quoted String" to denote a string in the formula. In this document we will use single quoted strings for consistency, but both formats are acceptable.

Assigning formulas to the field headers like this makes calculations much easier, especially for **Subtables**, for which you can write more complicated formulas with less effort.

Let's go back to our Sales Order example. The subtable that lists the items being ordered includes the Unit price (B12) and quantity (C12). The multiplication of these will give the amount of money (D12) the customer will be paying for each item. Notice how the subtotal of this amount on cell D17 references the field header of the amount in D12.

Formulas can also work with subtables. 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, or the SUMIFS Function.

A formula referencing to date fields will be calculating dates **based on the number of days**.

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() - A1)/365.25" to represent the 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, A1 is start time (HH:mm), A2 is end time (HH:MM), and there will be two ways to calculate the duration from time A1 to A2 base on the number of hours :

1. Use a time field A3 with formatting (HH:mm), you can set up formula "A2-A1". or

2. Use a numeric field A3 with formatting (0.0), you can set up formula "(A2-A1)/60".

**If your time spans across different dates**, you will need to use a date field with formatting that contains both time and date elements.

For example, A1 is start date and time (yyyy/MM/dd HH:mm), A2 is 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 would be "(A2-A1)*24"

Returns a number that represents a date that is the indicated number of working days before or after a certain date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

Formula | Syntax |
---|---|

WORKDAY | WORKDAY(start_date,days,["holidays"]) |

**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 state and federal holidays and floating holidays. The list can be either a date value or an array constant of the serial numbers that represent the dates.

**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 identify the dates "2017/06/16" and "2017/06/19" to be excluded,

The date 9 workdays from the start date, excluding identified holidays (2017/06/16 and 2017/06/19) would be **2017/06/30**

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in 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"]) |

**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 state and federal holidays, and floating holidays. The list can be either a date value or an array constant of the serial numbers that represent the dates.

**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" while E2 contains the value "2017/10/31", the dates "2017/10/04","2017/10/09",and "2017/10/10" will be 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") 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_number,["holidays"]) |

**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** 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. The ordering of dates or serial values in holidays can be arbitrary.

**Example**

Apply the formula "NETWORKDAYS.INTL(E1,E2,11,['2017/06/16'])" in a numeric field.

When E1 contains the value "2017/06/01", while E2 contains the value "2017/06/30", taking only Sunday as weekend, and identify the date "2017/06/16" to be excluded.

Results in **25** workdays by subtracting 10 nonworking days (4 Sundays, 1 Holiday) from the 30 days between 2017/06/01 and 2017/06/30. Uses the 11 argument for weekend, which is Sunday only. There is one holiday identified in this time period.

**Weekend 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 formulas available. **Please note that the following formulas are case-sensitive.**

Formula |
Description |
---|---|

SUM(value) | Returns the sum of all the field values. The call to SUM() is actually unnecessary, it's equivalent to just "value". |

AVG(value1, value2,...) | Returns the average of all the listed field values. 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 of all the listed field values. 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 minimum of all the listed field values. Using the minimum function also works for subtables. |

MAX(value) | Returns the maximum of all the listed field values. Using the maximum function also works for subtables. |

ABS(value) | Returns the absolute value of a number. |

CEILING(value) | Returns the smallest integer greater than or equal to a given number. |

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 number of field values. |

LEFT(value,length) | Returns a Variant (string) containing a specified number of characters from the left side of a string. |

RIGHT(value,length) | Returns a Variant (string) containing a specified number of characters from the right side of a string. |

MID(value,start,[length]) | Returns a Variant (string) containing a specified number of characters from a string. |

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 a Long containing the number of characters in a string. |

TODAY() | Returns the current date. |

NOW() | Returns the current date and time. |

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

EOMONTH(start_date, months) | Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Both "start_date" and "months" are required, and 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 |

WEEKDAY() | Returns the day of the week, with numbers 1 (Sunday) through 7 (Saturday) |

PI() | Ratio of the circumference of a circle to its diameter, approximately 3.14159 |

RAND() | Returns a random number between 0 and 1. |

TOUPPERCASE(value) | Converts a string to uppercase letters, without changing the original string. |

TOLOWERCASE(value) | Converts a string to lowercase letters, without changing the original string. |

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 divisor. The result has the same sign as 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 of the column in your subtable. |

LAST(value) | Returns the last data of the column in your subtable. |

IF(value==condition,value_if_true,value_if_false) | Returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE. For details, click here. |

LOOKUP(value,lookup_list,[result_list]) | Searches for the value in the lookup_list and returns the value from the same position in the result_list. For details, click here. |

AND(logical1, [logical2], ...) | Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE. For details, click here. |

OR(logical1, [logical2], ...) | Returns TRUE if any argument is TRUE; returns FALSE if all arguments 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 criteria you specify. For details, click here. |

COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...) | Applies criteria to cells 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 criteria you specify. For details, click here. |

SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2, criteria2],...) | Adds all of its arguments that meet multiple criteria that you specify. For details, click here. |

UPDATEIF(condition,value_if_true) | Update a field value only when a 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 cells that are not empty in a range. |

SUBTABLEROW(value,nth_row) | Returns the targeted data of the column in your subtable. |

PREVIOUSROW(value) | Returns the sum of the value in this row and previous row of the column in your subtable, used for calculating running balances. (It's not supported for manual formulas recalculation.) |

WORKDAY(start_date,days,["holidays"]) | Returns a number that represents a date that is the indicated number of working days before or after a date. For details, here |

NETWORKDAYS(start_date,end_date,["holidays"]) | Returns the number of whole working days between start_date and end_date. For details, click here. |

NETWORKDAYS.INTL(start_date,end_date,weekend_number,["holidays"]) | 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 line break, and CHAR(32) returns a space. |

LARGE(arg, nth, ["arg2"]) | Refers to the subtable field(s). You can specify to check the ordinal value of a column and return the value of the other column in the same row. The referenced field "arg2" needs to be in the same subtable as "arg" is. The formulas will sort your entries descending in the backend and return the field value of the specified ordinal number. |

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

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

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

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

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

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

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 the full name on A1.

Using LEFT(A1,LEN(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 conditional formulas. Please note that the conditional formulas are **case sensitive**, and that the field input type changes how formulas calculate in some situations.

**Important Note:** When used on free text or selection fields that contain strings, **.RAW** is required to be added to the referenced field name (please see below for "Referencing a string condition with the IF function"), while this is not needed when used to reference a numeric field. Date fields are calculated as days.

Conditional formulas can be nested.

The IF function returns one value if a specified condition evaluates to TRUE, or 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 equals 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 equals 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 equal or greater than 60, the value in this field "qualifies for senior discount?" would be "yes", otherwise, the value would be "no".

**Note**

An older syntax of using 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 string values in numeric or selection fields, please add **.RAW** after the field that you're referencing to.

Syntax |
---|

IF(value.RAW='string condition',value_if_true,value_if_false) |

**Examples**

Basic Example: IF(A1.RAW=='Active',1,0)

If the value in the reference field A1 is "Active", the value in this field would be 1. For any other value of A1, the value of this field will be 0.

Having a string value as a result: IF(A1.RAW=='Pending','Open','Closed')

If the value in the reference field A1 is "Pending", the value in this field would be "Open". For any other value of A1, the value of this field will be "Closed".

If you would like to retrieve text from referenced fields by LEFT(), RIGHT(), and MID(), please add **+""** after the field you're referencing to.

**Example**

IF(A1="Yes",A5,LEFT(A5+"",2))

If the value in the reference field A1 equals 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 first 2 digits of A5.

Since the system does not support referencing the value of TODAY()within an IF() formula directly, you'll need to create another field that references a field containing the value of TODAY().

**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 TODAY() formula or the field assigned with TODAY() formula as the referenced field to make calculation in the condition in IF() formulas, you can create field A2 for whole calculation, for example A1-TODAY(). Then, apply the formulas as below:

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

Please kindly note that the value of TODAY() will not be auto-recalculated once the entries are saved. If it's necessary to recalculate the TODAY() value, you'll need to apply a daily workflow.

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

Searches for the value in the lookup_list and returns the value from the same position in the result_list.

**value** is the value to search for in the lookup_range.

**lookup_list** is an array like [0,100,500]. The LOOKUP function searches for value in this list, which would need to be in ascending order.

**result_list** is optional. It is 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 can not 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, then the LOOKUP function will return empty string.

**Examples**

Basic Example: LOOKUP(A1,[0,45,65],['Small','Medium','Large'])

The value would be 'Small' if A1 is between 0 and 44, 'Medium' for 45~64 and 'Large' for equal or over 65.

Referencing multiple fields: LOOKUP(A1,[0,45,65],[A3+A4,B5,B6])

The value would be A3+A4 if A1 is between 0 and 44, B5 for 45~45 and B6 for equal or over 65.

Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.

Formula |
Syntax |
---|---|

AND | AND(logical1, [logical2], ...) |

The AND function syntax has the following arguments:

**logical1** is required. The first condition that you want to test that can evaluate to either TRUE or FALSE.

**logical2, ...** is optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE.

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

Formula |
Syntax |
---|---|

OR | OR(logical1, [logical2], ...) |

The OR function syntax has the following arguments:

**logical1** is required. Subsequent logical values such as **logical2, ...** is optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE. The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.

Use COUNTIF to count the number of rows in a subtable that met a criterion, for example, to count the number of times a particular item appears in a reciept.

Formula |
Syntax |
---|---|

COUNTIF | COUNTIF(criteria_range,criteria,count_range) |

The COUNTIF function syntax has the following arguments:

**criteria_range** is required. This range must be a subtable field that you want to check for values that fit in the criteria.

**criteria** is required. A number, expression, reference to another field, or text string that determines which cells will be counted. For example, you can use a number like 8, a comparison like ">8", a cell 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 criteria. Use COUNTIFS if you want to use multiple criteria.

**Example:**

The Formula COUNTIF(A4,'apple',A4) that is set on A9 returns the number of rows in subtable cell 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** is required. This first range must be a subtable field that you want to check for values that fit in the associated criteria.

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

**criteria_range2, criteria2,...** is optional. Additional ranges to be counted and their associated criteria.

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

Use SUMIF to sum up the value stored in a specified subtable row that met a criterion, for example, to sum up the monetary value of a specific merchandise item when it appears in a reciept.

Formula |
Syntax |
---|---|

SUMIF | SUMIF(range,criteria,[sum_range]) |

The SUMIF function syntax has the following arguments:

**range** is required. This range must be a subtable field that you want to check for values that fit in the criteria.

**criteria** is required. A number, expression, reference to another field, or text string that determines which cells will be added. For example, you can use a number like 8, a comparison like ">8", a cell like A4, or a word like "apple".

**sum_range** is optional. The actual cells to add, if you want to add values within subtable cells other than those specified in the **range** argument. If sum_range is omitted, the cells that are specified in the **range** argument will be added (the same cells to which the criteria is applied).

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

SUMIF uses only a single criteria. Use SUMIFS if you want to use multiple criteria.

**Example:**

The Formula SUMIF(A4,'apple',B4) that is set on A9 returns the sum of the values in subtable cell B4, when subtable cell A4 is product name apple.

Use SUMIFS to sum up the value stored in a specified subtable row that met multiple criterion, for example, to sum up the monetary value of a number of specific merchandise items in specific store locations when it appears in a reciept.

Formula |
Syntax |
---|---|

SUMIFS | SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2, criteria2],...) |

The SUMIFS function syntax has the following arguments:

**sum_range** is required. This range must be a subtable field that you want to check for values that fit in the criteria.

**criteria_range1** is 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** is 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 cells will be added. For example, you can use a number like 8, a comparison like ">8", a subtable cell like A4, or a word like "apple".

**criteria_range2,criteria2,...** is optional. Additional ranges to be summed and their associated criteria.

In case that you want to apply multiple criteria in a single field, for example, sum if A1 field equals to A or equals to B, you need to use multiple SUMIF() instead of SUMIFS(). Besides, SUMIFS can only refer to a single subtable, and can be set in stand-alone fields.

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 equals to 10, the value in this field would be 10. For any other value of A2, the value of this field would be unchanged from the previous 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 this field "Shipping address" would be A1 (home address), otherwise, the value would remain empty.

Conditional formulas can be nested, when you have multiple conditions to meet.

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

A calculation based on the formula you have written will be done whilst you are entering data in your database. This value is saved when you save your entry.

This values that are already saved in your database **do not change** by default when you change the formula in the design of your sheet. The reason for this is because, in most cases a previous calculation is still valid for older entries and it should not be automatically changed when you have update the formula. An example for this would be calculating taxes after a tax hike, the previous entries would still need to show the older tax rate.

In some cases, you may need to recalculate a formula on all the previous entries. To do this recalculation, navigate to the **Advanced Setting** under your **Formula** tab. You can choose to apply your new formula change to all saved records, or apply all formulas on this sheet to all saved records if you have modified more than one formula.

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

If you need a formula to run a formula recalculation on related records on other sheets, you can configure this from **Form Settings > Form Settings > Recalculate all formulas on related sheets**.

If you need to run a formula recalculation on related records on parent sheets instead, check the **Recalculate all formulas on parent sheets** setting instead.

Note that the setting to recalculate all formulas on related sheets will be ignored if the number of records to be recalculated exceeds system limitations.

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

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

Learn more about Ragic: Why we created Ragic | Webinars | User Guide.

Keep in touch: Blog | Email | Facebook | Twitter.

Ragic, Inc. | +1 888-666-8037 | Terms | Privacy

Keep in touch: Blog | Email | Facebook | Twitter.

Ragic, Inc. | +1 888-666-8037 | Terms | Privacy