Logical Functions in Excel

Function

Description

Syntax and Formula

AND

The AND function is a logical function in Excel that returns TRUE if all of the conditions that are specified are true, and returns FALSE if any of the conditions are false.

Syntax: AND(logical1, [logical2], …)
Formula: AND(A1>5,B1<10)

BYCOL

UMNBYCOLUMN is a function in Microsoft Excel that allows users to sort a range of data by column.

Syntax: BYCOLUMN(array, [col], [ascending])

Formula: BYCOLUMN(A1:C10, 2, TRUE)This example will sort the range A1:C10 by the second column in ascending order.

BYROW

BYROW is an Excel function that returns an array of numbers from a given range of cells. It takes two arguments: array and row_num. Array is a range of cells that contains numbers, and row_num is a number that indicates which row should be returned.

Syntax: BYROW(array, row_num)

Formula: If the range A1:C3 contains the following numbers:A1: 1A2: 2A3: 3B1: 4B2: 5B3: 6C1: 7C2: 8C3: 9Then the formula BYROW(A1:C3, 2) will return the array {2, 5, 8}.

FALSE

The FALSE function in Excel is a logical function that returns the value FALSE.

Syntax: FALSE()

Formula: FALSE()This formula will return the value FALSE.

IF

The IF function in Excel is a logical function that allows you to make a logical comparison between two values and returns a value based on the result of that comparison.

Syntax: IF(logical_test, value_if_true, value_if_false)

Formula: IF(A2>B2, “A is greater than B”, “B is greater than A”)

IFERROR

The IFERROR function is used to catch and handle errors in a formula. It tests a value for an error and returns a different value if an error is found.

Syntax: IFERROR(value, value_if_error)

Formula: IFERROR(A1/B1, 0)

IFNA

IFNA: This function returns the value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

Syntax: IFNA(value_if_error, value_if_no_error)

Formula: IFNA(0,A1/A2)

IFS

IFS is an Excel function that allows you to test multiple conditions at the same time and returns a value that corresponds to the first TRUE result.

Syntax: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

Formula: IFS(A1=1, “Yes”, A1=2, “No”, A1=3, “Maybe”)This formula will check the value in cell A1 and return “Yes” if it is equal to 1, “No” if it is equal to 2, and “Maybe” if it is equal to 3.

LAMBDA

The LAMBDA function in Excel is used to create custom functions. It allows users to create their own functions that can be used in formulas. It is a powerful tool that can be used to create complex formulas.

Syntax: LAMBDA(argument1, argument2, …, argument_n, expression)Arguments:argument1, argument2, …, argument_n: These are the arguments that will be used in the expression.expression: This is the expression that will be evaluated.

Formula: LAMBDA(x, y, x+y)This function adds two arguments (x and y) and returns the sum of the two.

MAKEARRAY

The MAKEARRAY function creates a single-column array from multiple values.

Syntax: MAKEARRAY(value1, [value2], [value3], …)

Formula: MAKEARRAY(1,2,3,4)This will return an array of {1;2;3;4}.

MAP

MAP is an Excel function that allows you to map one set of values to another set of values. It takes three arguments: lookup_value, from_array, and to_array.

Syntax: MAP(lookup_value, from_array, to_array)

Formula: MAP(2, {1,2,3}, {10,20,30})This example would return 20 as the result.

NOT

The NOT function is a logical function in Excel that returns the opposite of a given logical value. It takes a single argument that is either TRUE or FALSE and returns the opposite.

Syntax: NOT(logical)

Formula: NOT(TRUE) returns FALSE.

OR

The OR function is a logical function in Excel that returns either TRUE or FALSE based on one or more conditions.

Syntax: OR(logical1, [logical2], …)

Formula: OR(A1=”Yes”, B1=”Yes”)

REDUCE

The REDUCE function is an Excel function that reduces a range of values by applying a given function. It is a dynamic array function that can take multiple values and return a single result.

Syntax: REDUCE(function, range[, initial])

Formula: REDUCE(SUM, A1:A10, 0)This example will return the sum of the values in the range A1:A10, starting with an initial value of 0.

SCAN

The SCAN function searches for specific characters within a text string and returns the text string from the start position to the character found.

Syntax: SCAN(text, character_set, [start_num], [num_chars])

Formula: SCAN(“Hello World”, ” “, 1, 4)The example above will return “Hello” as the text string from the start position of 1 to the character found, which is a space.

SWITCH

The SWITCH function is a logical function in Microsoft Excel that evaluates an expression against multiple conditions and returns a result corresponding to the first matching condition.

Syntax: SWITCH (expression, value_1, result_1, [value_2, result_2], … [default])

Formula: SWITCH(A1,”A”,1,”B”,2,”C”,3,”D”,4,0)In this example, the SWITCH function will evaluate the value in cell A1 and return a result based on the following conditions:If A1 = “A”, the result is 1If A1 = “B”, the result is 2If A1 = “C”, the result is 3If A1 = “D”, the result is 4If none of the conditions are met, the result is 0

TRUE

The TRUE function returns the logical value TRUE.

Syntax: TRUE()

Formula: TRUE()This formula will return the logical value TRUE.

XOR

The XOR function in Excel returns a logical exclusive OR of all arguments. It returns TRUE if an odd number of arguments are TRUE, and FALSE if an even number of arguments are TRUE.

Syntax: XOR(logical1, [logical2], …)

Formula: XOR(TRUE, FALSE, TRUE)This example would return TRUE.