1

The following is a part of a formula I'm using to filter for week day rows (for conditional formatting):

    OR(
        MOD(ROW(),7)=2,
        MOD(ROW(),7)=3,
        MOD(ROW(),7)=4,
        MOD(ROW(),7)=5,
        MOD(ROW(),7)=6
    ),

What I really want to be able to say is something like the below pseudo-code:

    OR(
        MOD(ROW(),7) IN (2,3,4,5,6)
    ),

Is there a way of doing that? I prefer the non-repating, more concise, and (I think) more performant nature of the proposed version.

Performance isn't something I expected to have to think about but this is a snippet of a pretty long formula I'm running on every cell in a spreadsheet and it gets quite slow.

2
  • 1
    What about MOD(ROW(),7)>1?
    – JohnSUN
    Commented May 17, 2023 at 12:14
  • That is perfect. Why didn't I think of that?! Doesn't quite answer my general question, but perfectly suits my real-world use case. Thank you! Can you write that as an answer so I can accept?
    – Matt
    Commented May 17, 2023 at 13:05

2 Answers 2

2

Since the result of MOD(ROW(),7) can have 2 inappropriate values (0 and 1) and 5 suitable values (2..6), it makes sense to check the condition for a shorter sequence:

MOD(ROW(),7)>1

In this case, there is no need to connect multiple conditions using OR()

1

To answer your general question.

=COUNTIF({2,3,4,5,6},MOD(ROW(),7))>0

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.