Excel’s logical functions are essential tools for making decisions within your spreadsheets. They allow you to test conditions and perform actions based on the results. By mastering these logical functions, you can automate decision-making processes, streamline data analysis, and create powerful and flexible spreadsheets.
Excel’s Logical Functions: A Powerful Tool for Decision-Making
IF Function
The most fundamental logical function, IF, allows you to test a condition and return one value if true, and another if false.
Practical Applications
- Conditional Formatting: Highlight cells based on specific criteria.
- Data Validation: Ensure data entry accuracy.
- Decision Making: Automate decisions based on certain conditions.
- Creating Dynamic Reports: Adjust calculations and formatting based on specific criteria.
By mastering these logical functions, you can significantly enhance your Excel skills and automate many tasks that would otherwise require manual intervention.
| Function | Version | Purpose | Arguments |
|---|---|---|---|
| AND – .pdf | Excel 2003 | Test multiple conditions at the same time | logical1 logical2 … |
| FALSE – .pdf | Excel 2003 | Generate the logical value FALSE | |
| IF – .pdf | Excel 2003 | Test for a specific condition | logical_test value_if_true value_if_false |
| IFERROR – .pdf | Excel 2007 | Trap and handle errors | value value_if_error |
| IFNA – .pdf | Excel 2013 | Trap and handle #N/A errors | value value_if_na |
| IFS – .pdf | Excel 2019 | Test multiple conditions, return first true | test1 value1 test2, value2 … |
| NOT – .pdf | Excel 2003 | Reverse arguments or results | logical |
| OR – .pdf | Excel 2003 | Test multiple conditions at the same time | logical1 logical2 … |
| SWITCH – .pdf | Excel 2019 | Match multiple values, return first match | expression val1/result1 val2/result2 … default |
| TRUE – .pdf | Excel 2003 | Generate the logical value TRUE | |
| XOR – .pdf | Excel 2013 | Perform exclusive OR | logical1 logical2 … |
BYROW function
Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
Syntax
=BYROW(array, lambda(row))
The BYROW function syntax has the following arguments:
array An array to be separated by row.
lambda A LAMBDA that takes a row as a single parameter and calculates one result. The LAMBDA takes a single parameter:
row A row from array.
Errors
Providing an invalid LAMBDA function or an incorrect number of parameters returns a #VALUE! error called “Incorrect Parameters”.
Not providing a LAMBDA function or anything but a single value returned from the LAMBDA function returns a #CALC error.
Examples
Example 1: Returns the maximum value of each row of data
Enter the sample data into cells A1:C2, and then copy the formula into cell D4:
=BYROW(A1:C2, LAMBDA(array, MAX(array)))
Example 2: Returns the sum of the squared values for each row of data using the SUMSQ function
Enter the sample data into cells A1:C2, and then copy the formula into cell D4:
=BYROW(A1:C2, LAMBDA(array, SUMSQ(array)))
xx
LAMBDA function
Applies To
Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.
You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be error-prone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so non-programmers can also benefit from its use.
Syntax
=LAMBDA([parameter1, parameter2, …,] calculation)
Argument | Description |
|---|---|
parameter | A value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters. This argument is optional. |
calculation | The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required. |
Remarks
Lambda names and parameters follow the Excel syntax rules for names, with one exception: don’t use a period (.) in a parameter name. For more information, see Names in formulas.
Make sure you follow best practices when creating a LAMBDA function as you do with any native Excel formula, such as passing of the correct number and type of arguments, matching open and close parentheses, and entering numbers as unformatted . Also, when you use the Evaluate command, Excel immediately returns the result of the LAMBDA function and you can’t step into it. For more information, see Detect errors in formulas.
Errors
If you enter more than 253 parameters, Excel returns a #VALUE! error.
If an incorrect number of arguments is passed to a LAMBDA function, Excel returns a #VALUE! error.
If you call a LAMBDA function from within itself and the call is circular, Excel can return a #NUM! error if there are too many recursive calls.
If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.
Create a LAMBDA function
Here’s a step-by-step process to follow that helps make sure your Lambda works as you intended and closely resembles the behavior of a native Excel function.
Examples
BYCOL Function
Applies a LAMBDA to each column and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 3 columns by 1 row.
Syntax
=BYCOL (array, lambda(column))
The BYCOL function syntax has the following arguments and parameters:
array An array to be separated by column.
lambda A LAMBDA that takes a column as a single parameter and calculates one result. The LAMBDA takes a single parameter:
column A column from array.
Errors
Providing an invalid LAMBDA function or an incorrect number of parameters returns a #VALUE! error called “Incorrect Parameters”.
Not providing a LAMBDA function or anything but a single value returned from the LAMBDA function returns a #CALC error.
Examples
Example 1: Return the maximum value of each column
Enter the sample data into cells A1: C2, and then copy the formula into cell D4:
=BYCOL(A1:C2, LAMBDA(array, MAX(array)))
Example 2: Return the sum of the squared value of each array element in each column
Enter the sample data into cells A1: C2, and then copy the formula into cell D4:
=BYCOL(A1:C2,LAMBDA(array,SUMSQ(array)))
