1

I made a google sheet to track some daily goals of mine, such as doing Duolingo or having good posture. The first column is the task, the following are daily grades with some notes, so it looks sort of like:

Posture C A did yoga B
Duolingo F F F

I was thinking it'd be nice to color code it based on if I give myself an F too many times in a row. So on this third day here, it'd look at itself and the previous 2 entries and see they all start with F, and be red. Then the following day, I do Duolingo and mark an A, that entry will not be red.

I'm not sure where to start with this, any help is much appreciated.

1

1 Answer 1

0

Custom Formula

=AND(C1=B1, C1=A1, C1<>"") 
  • Based on a range that starts with C1.
  • Some examples of valid ranges for that formula are C:Z, C1:Z1, C1:G40, etc.

Why C1?

No reason other than column C being the first column that's preceded by at least two columns.

Whatever range you use in your rule, must be reflected in the custom formula based on its first cell (top-left).

For example, if your range is F4:K42 the custom formula would instead be:

=AND(F4=E4, F4=D4, F4<>"")

Explanation

  1. Uses AND to ensure all three conditions are met.
    1. Current cell's value (cell_1) is equivalent to the value of the cell to its immediate left (cell_2).
    2. Current cell's value (cell_1) is equivalent to the value of the cell 2 columns to its left (cell_3). You could alternatively compare cell_2 and cell_3 as the second test.
    3. Current cell (cell_1) is not empty. You could alternatively use cell_2 or cell_3 in this test.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.