Export (0) Print
Expand All

IF Function

Checks if a condition provided as the first argument is met. Returns one value if the condition is TRUE, and returns another value if the condition is FALSE.

IF(logical_test>,<value_if_true>, value_if_false)

Term

Definition

logical_test

Any value or expression that can be evaluated to TRUE or FALSE.

value_if_true

The value that is returned if the logical test is TRUE. If omitted, TRUE is returned.

value_if_false

The value that is returned if the logical test is FALSE. If omitted, FALSE is returned.

Any type of value that can be returned by an expression.

If the value of value_if_true or value_if_false is omitted, IF treats it as an empty string value ("").

If the value referenced in the expression is a column, IF returns the value that corresponds to the current row.

The IF function attempts to return a single data type in a column. Therefore, if the values returned by value_if_true and value_if_false are of different data types, the IF function will implicitly convert data types to accommodate both values in the column. For example, the formula IF(<condition>,TRUE(),0) returns a column of ones and zeros and the results can be summed, but the formula IF(<condition>,TRUE(),FALSE()) returns only logical values. For more information about implicit data type conversion, see Data Types Supported in PowerPivot Workbooks.

The following example uses nested IF functions that evaluate the number in the column, Calls, from the table FactCallCenter in Adventure Works DW Multidimensional 2012 . The function assigns a label as follows: low if the number of calls is less than 200, medium if the number of calls is less than 300 but not less than 200, and high for all other values.

=IF([Calls]<200,"low",IF([Calls]<300,"medium","high"))

The following example gets a list of cities that contain potential customers in the California area by using columns from the table ProspectiveBuyer in Adventure Works DW Multidimensional 2012 . Because the list is meant to plan for a campaign that will target married people or people with children at home, the condition in the IF function checks for the value of the columns [MaritalStatus] and [NumberChildrenAtHome], and outputs the city if either condition is met and if the customer is in California. Otherwise, it outputs the empty string. 

=IF([StateProvinceCode]= "CA" && ([MaritalStatus] = "M" || [NumberChildrenAtHome] >1),[City])

Note that parentheses are used to control the order in which the AND (&&) and OR (||) operators are used. Also note that no value has been specified for value_if_false. Therefore, the function returns the default, which is an empty string.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft