Forum Discussion
Conditional format cells only that contain a sequence of blanks
To count max number of sequential blanks you may use
=MAX(FREQUENCY(IF(($E2:$O2=""),COLUMN($E2:$O2)),IF($E2:$O2<>"",COLUMN($E2:$O2))))see in Column P attached. In general it shall be array formula (Ctrl+Shift+Enter), but I'm on Dynamic Array Excel now and can't test. Column A is highlighted using this formula.
8 Replies
This involves a bit of lateral thinking to transform your problem into a form that is easy to solve. Given the range, I defined a Named Formula 'signature' using Name Manager to refer to
= CONCAT( N(ISBLANK(range)) )
That gives a binary string with 1 for a blank cell and 0 otherwise.
The next step was to search the string for the occurrence of "111" using the named formula 'format?' which refers to
= IFERROR( SEARCH("111", signature ), 0 )
The conditional format then is based upon
= format?
A side benefit is that
= INDEX( range, format? )
is the first cell of the three or more consecutive blank cells.
Peter, great idea with binary string
- UPDATE: incorrect information below: "The desired outcome is to highlight the cell A2 as row A has 3 or more blanks in a row. " Row 2 is the correct reference. Thanks
You may apply to your range in column A, starting from A2, conditional formatting rule with formula
=COUNTBLANK($E2:$O2)>=3- Hi Sergei, This formula is identifying rows with the required amount of blank cells, but is there a way to identify blanks in a sequence? Say row 2 has 4 blanks cells (I2:L2) but row 4 only has a maximum is 2 blanks in sequence, I need to identify only the rows that have 4 or more blanks in sequence. Is this possible? Thanks for your help.
