Conditional Formatting Based on Average of Previous Cells

[SDowse]

Hi,

I have a simple spreadsheet, let's say 2 rows, 10 columns.

I am trying to do conditional formatting that highlights a cell where the value of that cell is 0, but the average of the previous cells in the row is greater than 0.

For example:

1	2	3	4	5	6	7	8	9	10
1	2	3	4	5	0	0	0	0	0
1	2	3	4	5	6	7	8	9	10

In the 2nd row above, I want the cells that a zero to be highlighted, because the previous cells are not 0. I have managed to do this on a single cell, where I use average of the previous columns, but I cannot work out how to expand the range in conditional formatting, so that as I drag the formatting across, the first cell is "anchored" but the range picks up all the subsequent columns - i.e. the range should be from the first column up to the column immediately before the cell being formatted (but the conditional formatting across the entire row - if that makes sense).

The reason I need this sort of dynamic range is because through time the columns may be updated and numbers other than zero calculated.

Hoping someone can help - thank you in advance.

 

[SDowse]

Ok, so in a row, like the example given above, I want conditional formatting to highlight a cell in red where:
1. The date in the first row (which will be the first of each month) is greater than EOMONTH(TODAY(),-1)
2. The value in each cell does not equal 0
3. The values in all of the columns to left of each column are not 0 - I used AVERAGE to do this, I just couldn't work out how to "anchor" the first column and then have the range expand across the row - so H would be C:G, I would be C:H, J would be C:I etc...

Hopefully that is clear?

 

[Fluff]

Ok, how about

Fluff.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24
18	£0	£0	£62	£1,292	£1,513	£5,170	£2,632	£6,696	£6,856	£7,924	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
19	£0	£0	£1,401	£4,000	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
20	£0	£0	£6	£0	-£628	-£785	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
21	£0	£0	£0	£180	£634	£3,453	£1,701	£1,902	£1,584	£1,644	£2,802	£8,787	£14,185	£18,263	£19,659	£17,272	£15,554	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D18:AC21	Expression	=AND(D18=0,D$2>EOMONTH(TODAY(),-1),AVERAGE($C18:C18)<>0)	text	NO

 

[SDowse]

Many thanks - I'll give that a try. Will a simple paste format across all the cells work and adjust the $C18:C18 across all the columns?

 

[Fluff]

Just select D18 to the last cell it needs to work on & then apply the rule.

 

[SDowse]

Excellent, that seems to have worked - many thanks. Some oddities in my data made it look a bit weird at first, but all seems to be working. Thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.