Logical functions are used to compare multiple values and return an output depending on whether or not the comparison meets the function’s conditions.

Logical functions are used to compare multiple values and return a specified value or boolean value (TRUE or FALSE) depending on whether or not the comparison meets the function’s conditions. Logical functions are not only input into cells like all other functions, but are also often used to make complex formulas in conjunction with other functions.

You may have already encountered functions in other sections that have logical conditions integrated, like SUMIF and COUNTIF. Logical functions can be useful in many applications and types of spreadsheets, including data verification and cleaning messy datasets.

List of Logical Operators in Spreadsheet.com

When logical functions compare two values they use a logical operator, also known as a boolean operator. Logical operators are the mathematical version of statements like “greater than”, “equal to”, and “not equal to”. Understanding the syntax required of each logical operator is key to using logical functions properly.

The logical operators supported by Spreadsheet.com are:

You’ll see these logical operators used in the examples below.

How to Use Logical Functions to Compare Two Values

In the examples below, we’ll take a look at some commonly used logical functions. For documentation on every logical function supported by Spreadsheet.com, reference our section on Logical functions.

When using functions in your workbook, you can input and edit formulas either directly in the cell, or from the formula bar between the worksheet toolbar and the spreadsheet grid. Learn more in our article on Creating and Editing Formulas.

How to Create an If Statement with One Condition (IF)

The IF formula is used to create a logical test that compares a value against a specified condition, and then returns one of two values depending on whether or not the condition is met.

In the example above, we’ve used the IF function in Cells C2 through C4 to compare budgeted costs against actual costs.

The logical test created by our IF function asks if the budgeted cost is greater than or equal to the actual cost. If it is, the function returns a value of “Yes”; if not, it returns a value of “No”.

IF functions allow you to specify a single condition with which you can compare your data. If you want to specify multiple conditions, you can use the IFS function, described below.

How to Create an If Statement with Multiple Conditions (IFS)

The IFS function works similarly to the IF function, but supports multiple conditions by which you can compare your data

In this example, we’ve taken the same data as before and have used IFS functions in place of our IF functions. Now, we’re comparing our budgeted costs and actual costs with three conditions instead of one:

• If the budgeted cost is greater than the actual cost, then our formula will return “Under Budget”
• If the budgeted cost is equal to the actual cost, then our formula will return “On Budget”
• If the budgeted cost is less than the actual cost, then our formula will return “Over Budget”

The IFS function supports an unlimited number of conditions.

How to Create And and Or Statements (AND and OR)

The AND function takes multiple arguments and returns a boolean value – TRUE if all conditions are met, and FALSE if some or none of the conditions are met.

In the example above, our AND functions in Column C are testing whether or not the values in both Columns A and B equal “A”. If both do, the function returns TRUE; if one or both do not, the function returns FALSE.

The OR function works similarly, but will return a TRUE value if only one of the conditions is met.

Here, we’ve changed our previous AND functions to OR functions. Note that the function in Cell C3 now returns TRUE, because one of the function’s conditions is met in Cell A3.

More Logical Functions

The functions shown above are just some of the many logical functions supported by Spreadsheet.com. Take a look at our full suite of articles on logical functions to learn more.

How to Use Logical Functions to Create Complex Formulas

Now that we’ve explored how logical functions work independently, we can string them together with other functions to create complex formulas. Let’s take a look at a basic data table.

Here, we have three columns of values containing either an “A” or a “B”. We want to use Column D to record whether some, all, or none of the adjacent contain an “A”.

We can’t just use the AND or OR formulas, as these will only return TRUE and FALSE values. We could use the IFS function, but this would require us defining nine separate conditions and wouldn’t be very efficient.

Instead, we can write a nested If statement with the IF formula. A nested If statement is an If statement that contains other If statements within it. If the values being compared fail an initial logical test, instead of returning an output, the values are instead subject to a second logical test, also defined with an IF function. Take a look at the formulas below:

Let’s examine the formulas in Column D more closely and break it down step by step:

=IF(AND(A#="A",B#="A",C#="A"),"All", IF(OR(A#="A",B#="A",C#="A"),"Some", "None"))

1. The formula tests the first part of our IF function, AND(A#="A",B#="A",C#="A"). If the values are all equal to “A”, then the formula returns “All” as it does in Cell D2.
2. If the values fail the first test, then the formula tests the next IF function, OR(A#="A",B#="A",C#="A"). If some of the values are equal to “A”, then the formula returns “Some” as it does in Cell D3.
3. If the values fail the second test because none of the values are equal to “A”, then the formula returns “None” as it does in Cell D4.

How to Troubleshoot Formula Errors

When writing complex formulas, it's not uncommon to see errors like #VALUE!, #NAME?, or #REF!. If you do, double check your formula in the formula bar and make sure the syntax is correct. Some common causes of errors are misplaced or missing parentheses, invalid cell references, or an incorrect number of formula arguments.

Learn more about working with complex formulas in our suite of articles on Assigning Values with Formulas. Or, read on to learn more about other types of functions supported by Spreadsheet.com, including lookup functions, information functions, statistical functions, and more.