Notification rules in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This sheet tracks tasks with their status and due dates. Notification rules can alert when tasks are pending and due soon.

Cell	Value
A1	Task
B1	Status
C1	Due Date
A2	Submit report
B2	Pending
C2	2024-06-10
A3	Team meeting
B3	Completed
C3	2024-06-05
A4	Update website
B4	Pending
C4	2024-06-12

Formula Trace

=AND(B2="Pending", C2-TODAY()<=3)

Step 1: B2="Pending"

Step 2: C2-TODAY()

Step 3: 4 <= 3

Step 4: AND(TRUE, FALSE)

Cell Reference Map

    A           B           C
1 | Task      | Status    | Due Date
2 | Submit report | Pending  | 2024-06-10

References:
- B2 for status check
- C2 for due date
- TODAY() is current date (2024-06-06)

The formula checks if the task in row 2 is pending (B2) and if the due date (C2) is within 3 days from today.

Result

    A           B           C           D
1 | Task      | Status    | Due Date  | Notify?
2 | Submit report | Pending  | 2024-06-10 | FALSE

Column D shows the notification rule result for row 2.

The formula result in D2 is FALSE, meaning no notification is triggered because the due date is more than 3 days away.

Sheet Trace Quiz - 3 Questions

What does the formula check first?

A If the task status is 'Pending'

B If the due date is today

C If the task is completed

D If the due date is more than 3 days away

Key Result

AND(condition1, condition2) returns TRUE only if both conditions are TRUE.