I wrote a formula that attempts to create a checkerboard pattern via conditional formatting, that should even work with Merged Cells. However, it seems to fail in an almost undebuggable manner with seemingly random outputs; is this a Google Spreadsheet bug or am I just missing something here?

Conditional formatting formula:


Formula breakdown:

  • OFFSET(A1,ROW(K5)-1,0,1,COLUMN(K5)) -- this will give you A5:K5
  • COUNTIF(..., "<>") -- this will count all non-empty cells in a range
  • ISODD -- equivalent to mod 2, this will turn [1,2,3,4,5,...] into booleans [T,F,T,F,...]

Goal: In a given row, swap color whenever we encounter a new non-empty cell.

The idea is that each cell counts from the left to see how many non-blank values there are inclusive of itself; whenever a new value is added to the row, the color should flip for that value and everything to the right of it. This will reduce to a checkerboard pattern but will handle merged cells correctly (since they're technically there but are blank squares).

However, when I try to implement this, even piecemeal, the result seems almost random.

(If anyone else was thinking of doing this: My current workaround to get something similar is to make a random array of colors by pumping the row and column through a very simple hash function, probabilistically coloring some fraction of the outputs a random color over the course of a few independent formulae.)

example row (^^^^ indicates true/shaded, otherwise false/unshaded):

[ 1 ][ 2 ][ a ][ 4 ][   ][ z ][ 7 ][ q ][   ][   ][ 8 ][   ][ 9 ][   ]
^^^^^     ^^^^^          ^^^^^     ^^^^^^^^^^^^^^^          ^^^^^^^^^^

[ 1 ][ 2 ][ a ][ 4 mrgd ][ z ][ 7 ][ q merged    ][ 8 mrgd ][ 9 mrgd ]
^^^^^     ^^^^^          ^^^^^     ^^^^^^^^^^^^^^^          ^^^^^^^^^^
  • can you share a copy of your sheet with example of desired output? – user0 Jul 17 '19 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.