Google Sheets 101: Intro To Logic

LESSONS

In this lesson you’re going to learn the main logic functions in spreadsheets.

They help you to get a specified output based on provided tests or 'logical expressions'.

You will often nest other functions within these functions so that your formulas are only executed and data only appears when specific conditions are met.

IF

The IF function is the most commonly used logic function.

It tests a single logical expression and outputs one value if the test is TRUE and another if the test is FALSE.

Syntax

=IF(logical_expression, value_if_true, value_if_false)

Example

You want to compare two numbers in cells A1 and A2.

If the first is equal to the second you want the output to be "EQUAL".

If the first is not equal to the second you want the output to be "NOT EQUAL".

=IF(A1=A2, "EQUAL", "NOT EQUAL")

Nesting

You can also nest IF functions together to test multiple conditions:

=IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, value_if_false))

If the first logical_expression is FALSE, the nested IF function is executed.

If nesting doesn’t make sense, you can use the IFS function instead:

IFS

The IFS function is similar to IF but it allows you to test more than one logical expression.

It can handle slightly more complex situations than IF.

Syntax

=IFS(condition1, value1, [condition2, value2, …])

The function is executed from left to right with the value output from the first logic test (or 'condition') that is TRUE.

Example

You want to compare two numbers in cells A1 and A2.

If the first is greater than the second you want the output to be "GREATER".

If the first is equal to the second you want the output to be "EQUAL".

If the first is less than the second you want the output to be "LESS".

=IFS(A1>A2, "GREATER", A1=A2, "EQUAL", A1<A2, "LESS")

When writing your IFS function make sure that all outputs are accounted for otherwise the output will be an #N/A error.

Logic Exercises

Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.

1. Are They Equal?

In cell B8, enter a formula using the IF function that tests if Dwight and Jim's sales are "Equal" or "Not Equal" (with those outputs). From this point in the course assume the numbers may change in the future and you should use cell/range references.

2. Who's Better?

In cell B9, enter a formula using the IF function that tests if Dwight or Jim has the higher sales numbers and outputs the first name of the better salesperson. You can ignore the possibility that their sales are the same.

3. IFS

In cell B10, enter a formula using the IFS function that tests Dwight's sales against Jim's and outputs "Lower", "Equal", or "Higher" depending on the result.

Current question: See the correct formula or clear the formula bar.