Aug 10 2019 09:13 PM
Aug 10 2019 09:17 PM
Aug 11 2019 05:46 AM
You may apply to your range in column A, starting from A2, conditional formatting rule with formula
=COUNTBLANK($E2:$O2)>=3
Aug 15 2019 05:46 PM
Aug 16 2019 03:56 AM
SolutionTo 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.
Aug 16 2019 11:30 PM
Aug 17 2019 12:17 AM
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.
Aug 17 2019 04:57 AM
You are welcome. Forgot to say, array formula (CSE) if only you use it for the calculation in the cell. Within the rule you may use it as regular one, Conditional Formatting is smart enough to recognise array calculation.
Aug 17 2019 05:06 AM
Peter, great idea with binary string
Aug 16 2019 03:56 AM
SolutionTo 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.