Data validation rules in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This data shows ages in column A. We want to check if each age is valid (between 18 and 65) using data validation rules.

Cell	Value
A1	Age
A2	25
A3	17
A4	30
A5	15
B1	Valid Age?
B2
B3
B4
B5

Formula Trace

=AND(A2>=18, A2<=65)

Step 1: A2

Step 2: A2>=18

Step 3: A2<=65

Step 4: AND(TRUE, TRUE)

Cell Reference Map

    A       B
1 | Age   | Valid Age? |
2 |  25   |           |
3 |  17   |           |
4 |  30   |           |
5 |  15   |           |

Formula in B2 references A2.

The formula checks the value in cell A2 to see if it is between 18 and 65.

Result

    A       B
1 | Age   | Valid Age? |
2 |  25   |   TRUE    |
3 |  17   |   FALSE   |
4 |  30   |   TRUE    |
5 |  15   |   FALSE   |

Column B shows TRUE if the age in column A is between 18 and 65, otherwise FALSE.

Sheet Trace Quiz - 3 Questions

What does the formula =AND(A2>=18, A2<=65) check?

A If the value in A2 is less than 18 or greater than 65

B If the value in A2 is exactly 18 or 65

C If the value in A2 is between 18 and 65 inclusive

D If the value in A2 is not a number

Key Result

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