Conditional formatting based off the previous X number of cells

Question

I am trying to setup conditional formatting so that the formatting takes effect if the previous 4 cells in a column are blank. I need the range inside the conditional formatting formula to move as it is applied to cells below it, for example:

If I am tracking sales by store, if there is a 4 week period where a certain store doesn't record any sales I want those 4 weeks to automatically highlight.

My current formula is to select cells B5:B11, enter the formula =SUM(B2:B5)=0 to trigger the cell highlighting.

My problem is that when I look at cell B6 the range it looks for to be blank is still B2:B5 and I need it to be B3:B6.

Is there a way to set this up so that the range will change as it moves down a column?

Answer 1

This one has gotten me thinking a bit, and here is my solution for it.

My solution needs to use four conditional formattings, i.e. set up four conditional formattings in each cell, and then use format painter to apply the conditional format to all the cells.

Suppose your week table is in range A1:D20, and suppose you want to highlight cells if there are at least (not only) four consecutive blank cells.

Highlight cell B2, and then set up conditional formatting using the following four formulas:

=SUM(OFFSET($B2,0,0,4,1))=0

=SUM(OFFSET($B2,-1,0,4,1))=0

=SUM(OFFSET($B2,-2,0,4,1))=0

=SUM(OFFSET($B2,-3,0,4,1))=0

Then should have something similar to the following when examing the conditional formatting window:

Lastly, highlight cell B2, use the Format Painter function under Home tab and then apply the format to the rest of the cells in the week table. Then you should have something similar to the following:

It is easy to find the first blank cell of four consecutive blank cells, but it is difficult to identify the second, third and fourth blank cells using one formula. The workaround uses four different formulas to identify each of the four blank cells (I hope this makes sense). Please note the following is only looking at Column B of my example, not all columns:

Answer 2

Apply the following condition to each respective ranges:

Range: B2:D2:

=SUM(B2:B5)=0

Range: B3:D3:

=OR(SUM(B2:B5)=0, SUM(B3:B6)=0)

Range: B4:D4:

=OR(SUM(B2:B5)=0, SUM(B3:B6)=0, SUM(B4:B7)=0)

Range: B4:D8:

=OR(SUM(B2:B5)=0, SUM(B3:B6)=0, SUM(B4:B7)=0, SUM(B5:B8)=0)

Result with sample data:

Answer 3

Not pretty, but it works. This will highlight all occasions where the store has at least 4 weeks of consecutive donuts.

Columns B:D = Original Data

Columns E:G = Helper Set 1

Columns H:J = Helper Set 2

There's probably a way to get just one helper set, but this works and was easy to validate. First I added 3 empty rows above week 1.

In cells E4, E5 and E6 place a 0. You don't have to, you could also leave these blank.

Cell E5 formula, which is counting the number of consecutive blanks:

=IF(ISBLANK(B5),E4+1,0)

Drag it over to column G and drag it down.

Cell H5 formula, which is looking at 4 weeks later to see if there were consecutive blanks found in helper set 1:

=IF(E8>=4,"Yes","No")

Drag it over to column J and drag it down. Also, in the row above it, repeat week 1 values.

Lastly, select your original data range (B5:D14) and create this conditional formatting formula:

=(OR(H5="Yes",H4="Yes",H3="Yes",H2="Yes"))

Once you're satisfied, you can hide rows 2:4 and can also hide your helper columns.