3.5.10.4 If

If-func

Description

Minimum Origin Version Required: 2019 SR0

Like the MS Excel function of the same name, this function is used to evaluate a condition and return certain values depending on whether the condition is True or False. The values can be text and numeric. To compare a set of values by multiple conditions, use nested IF functions.

Notes:

• IF function can only be used in Set Column Values dialog or Cell Formula. It cannot be used in general Labtalk script.
• As a general programming rule, a non-zero value is treated as "true", while zero is treated as "false". Therefore, if your "conditional" parameter -- which typically (but not necessarily) takes the form of a comparison -- evaluates to con > 0, it will be treated as "true"; and con = 0, will be treated as "false." See example 5, below.

Syntax

double If(string con, double val_true[, double val_false])

string If(string con, string val_true$[, string val_false$])$

Parameters

con

The condition expression to be evaluated as "true" or "false." See "Notes" above.

val_true

the returned value when the condition is True. It can be numeric or text. If text value, must be enclosed in double quotes (" ").

val_false

the returned value when the condition is False. It can be numeric or text. If text value, must be enclosed in double quotes (" ").

Return

Return the specified values depending on the result of comparison.

Example

Ex1 - Compare Values in Two Columns

Compare values in column A and B row by row: If A > B, return 1, otherwise 0.

1. Create a new Workbook. Fill col(A) and col(B) with random numbers.
2. Add col(C), and in the F(x)= cell, enter:

if(A > B, 1, 0)

Ex2 - Compare Values in Cell Formula

Check cell value in column A above average of column A or not and return text accordingly.

1. Create a new Workbook. Fill col(A) with random numbers.
2. In cell B1, enter the formula:

=if(A1 > mean(A), "Above Average", "Below Average")

3. Press Enter to confirm.
4. To fill down the rest of the column, drag from the bottom-right corner of B1.

Ex3 - Nested IF to Categorize Values

Use nested IF to return "Large", "Small", or "Median" based on value range.

1. Create a new Workbook. Fill col(A) with random numbers.
2. In Column B's F(x)= cell, enter:

If(A > 0.5, "Large", If(A < 0.3, "Small", "Median"))

Ex4 - Use Function Return as Condition

There are 3 groups in column A. Use diff(A, 2) as condition to only show difference at the start of each group. Note: When using value as condition, any non-zero value will be treated as true.

1. Create a new Workbook.
2. Copy these numbers: 1 1 1 2 2 2 0.5 0.5 0.5. Right-click cell A1 in column A and choose Paste Transpose.
3. In column B's F(x)= cell, enter:

if(diff(A, 2), diff(A, 2), ) //  show difference if there is difference, otherwise fill with missing value

4. Add column C. In col(C)'s F(x)= cell, enter:

if(diff(A, 2), diff(A, 2), "") // show difference if there is difference, otherwise leave it blank

Ex5 - Compare Text in Two Columns

Compare two string columns and return a match status. Add $ after column short name will treat the column as strings.

1. Create a new Workbook. Select columns A and B, right-click and choose Fill Column with: Arbitrary set of Text&Numeric values. In Text to Repeat, enter Alpha Beta Gamma. Set Mode = Random and click OK.
2. Add a third column. In the F(x)= cell, enter:

if(A$==B$, 1, 0) // 1 if A matches B, else 0

3. Add a fourth column. In the F(x)= cell, enter:

if(A$==B$, "Match", "") // label matching strings

Ex6 - Detect Email Domain with OR Condition

Return "Non-Commercial" if email domain contains ".org", ".gov" or ".edu".

1. Create a new Workbook.
2. Copy the following email addresses jane@nonprofit.org john@example.com smith@university.edu ccc@usda.gov.
3. Right click on A1 cell and paste transpose.
4. In col(B)'s F(x)= cell, enter the formula:

if(find(A$, ".org") != -1 || find(A$, ".gov") != -1|| find(A$, ".edu") != -1, "Non-Commercial", "")

See Also

Ifs, Switch