Can I use conditional formulas in the formula fields?

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.

For example, 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

The IF function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to 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.

Referencing a string condition with the IF function

If you would like to reference string values in numeric or selection fields, please add .RAW after the field that you're referencing to.

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

The LOOKUP Function

The conditional process in formulas can also be done with the LOOKUP function, which is the equivalent of conditional processing.

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.

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 45, 'Medium' for 45~65 and 'Large' for 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 45, B5 for 45~65 and B6 for over 65.

The AND Function

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

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.

The OR Function

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

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.