excelexcel-formulaconditional-formatting

Why does conditional formatting break when AND contains OFFSET?

I'm trying to use conditional formatting to color-code a schedule that repeats every 6 rows. The rule I'm using is =AND(OFFSET(C8,4-MOD(ROW(C8)-2,6),0)="TRAINING",MOD(ROW(C8)-2,6)<3). The TRUE/FALSE cells pictured below contain said rule for visibility. As you can see in the snip below, the TRUE cells should be turning green while the FALSE cells stay white, but for some reason it simply doesn't work. enter image description here

I tried wrapping the whole rule in NOT, but the rule still wouldn't activate. The problem seems to arise for any rule I try to write where AND or OR contains OFFSET, where no matter what the formatting will not activate. Is there something I'm doing wrong or can change to work around this?

Solution

  • Instead of using AND() function use an AND() operator * :

    enter image description here

    =(OFFSET(C8,4-MOD(ROW(C8)-2,6),0)="TRAINING")*(MOD(ROW(C8)-2,6)<3)

    Also, suggestion instead of using volatile functions, use INDEX() function:

    =AND(INDEX(C:C,ROW(C8)+4-MOD(ROW(C8)-2,6))="TRAINING",MOD(ROW(C8)-2,6)<3)

    Or,

    =(INDEX(C:C,ROW(C8)+4-MOD(ROW(C8)-2,6))="TRAINING")*(MOD(ROW(C8)-2,6)<3)

    INDEX() functions work both with AND() as well as with the AND() operator *

    Also, when OFFSET() function don't work with logical functions like AND() / OR() in Conditional Formatting then use