If

Contents

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:

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

IF value in col(A) is larger than col(B), returns 1, otherwise returns 0.

  1. Create a new Workbook. Fill col(A) and col(B) with some random number.
  2. Add col(C). Right click on col(C) and select Set Column Values from context menu. In the Set Values dialog enter formula
    if(A>B,1,0)
    

Ex2

IF value in col(A) is larger than 0.5, returns Good, otherwise returns Bad.

  1. Create a new Workbook. Fill col(A) with some random number.
  2. Double click in the first cell of col(B) and enter formula
    =if(A1>0.5,"Good","Bad")$
    
  3. Escape the in-place edit mode. Select cell B1 and carefully move your mouse to the bottom-right edge of B1 until the cursor become "+". Hold down the left mouse button and drag down to the last cell of col(B) to auto-fill those cells.

Ex3

IF value in col(A) is larger than 0.5, returns Large, smaller than 0.3, returns Small, otherwise returns Median.

  1. Create a new Workbook. Fill col(A) with some random number.
  2. Right click on col(B) and select Set Column Values from context menu. In the Set Values dialog enter formula
    If(A>0.5,"Large",If(A<0.3,"Small","Median")$)$
    

Ex4

  1. Create a new Workbook. Select columns A and B, right click and Fill Column with: Arbitrary set of Text&Numeric values. In Text to Repeat, enter "Larry Moe Curly" (without the " " marks). Set Mode = Random and click OK. Columns A and B are filled with text values.
  2. Add a third column to the worksheet and in the F(x)= cell of the third column, enter the following:
    if(A$==B$,1,0)
    
    The column will be filled with "0" where col(A)[i] does not match col(B)[i] and "1" where col(A)[i] does match col(B)[i].
  3. Add a fourth column to the sheet and in the F(x)= cell enter
    if(A$==B$, "Stooge Match!", " ")
    
    Where strings in col(A)[i] and col(B)[i] match, the cell is filled with "Stooge Match!". Where there is no match, the cell is left blank.

Ex5

"If the difference between adjacent elements in column A is non-zero, return zero, otherwise return the value in column B."

if(diff(A,2),0,B)

See Also

Ifs, Switch