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.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	1	2	3	4	5	6	7	8	9	10
3	1	2	3	4	5	0	0	0	0	0
4	1	2	3	4	5	6	7	8	9	10
5	1	-1	2	3	-5	0	0	1	0	0
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:J5	Expression	=AND(A2=0,AVERAGE($A2:A2)<>0)	text	NO

 

[SDowse]

Here is a snippet...

Copy of FESL and FFH Cash Flow Forecast 16062022 - SD.xlsx
	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
FESL Analysis

Cell Formulas
Range	Formula
D2:AC2	D2	=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2))
C18:AC21	C18	=-(SUMPRODUCT('FESL CF'!$XT$15:$AWG$113*('FESL CF'!$XT$6:$AWG$6>='FESL Analysis'!C$2)*('FESL CF'!$XT$6:$AWG$6<'FESL Analysis'!D$2)*('FESL CF'!$B$15:$B$113='FESL Analysis'!$B18)))/1000

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:AC2	Expression	=C$2>EOMONTH(TODAY(),-1)	text	NO
C2:AC2	Expression	=C$2<=EOMONTH(TODAY(),-1)	text	NO
N18	Expression	=AND(N$2>EOMONTH(TODAY(),-1),N18=0,AVERAGE($C18:M18)>0)	text	NO
M18:AC18	Expression	=AND(M$2>EOMONTH(TODAY(),-1),M18=0,AVERAGE($C18:M18)>0)	text	NO
C3:AC100	Expression	=C$2>EOMONTH(TODAY(),-1)	text	NO
C3:AC100	Expression	=C$2<=EOMONTH(TODAY(),-1)	text	NO

 

[SDowse]

It's the M18:AC18 conditional formatting I want to amend so that the AVERAGE range is anchored to C18, but the M18 changes to N18, O18, P18 etc...so it is always capturing from C18 to the column to left of the cell being formatted

 

[Fluff]

In that case just change the ranges like

=AND(M18=0,AVERAGE($C18:L18)<>0)

 

[SDowse]

It doesn't appear to be working - here is my conditional formatting...

=AND(C$2>EOMONTH(TODAY(),-1),C3=0,AVERAGE($C3:C3)<>0)

The last column is AC, but the above is what I see if highlight the row and check the conditional formatting.

As from the May-22 column the average wont equal 0, I would have expected everything from Jul-22 onwards to be red.

View attachment 67223

 

[SDowse]

Sorry, try again...

Copy of FESL and FFH Cash Flow Forecast 16062022 - SD.xlsx
	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
82	£5	£8	£0	£7	£8	£1	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
FESL Analysis

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:AC2	Expression	=C$2>EOMONTH(TODAY(),-1)	text	NO
C2:AC2	Expression	=C$2<=EOMONTH(TODAY(),-1)	text	NO
C3:AC100	Expression	=AND(C$2>EOMONTH(TODAY(),-1),C3=0,AVERAGE($C3:C3)<>0)	text	NO
C3:AC100	Expression	=C$2>EOMONTH(TODAY(),-1)	text	NO
C3:AC100	Expression	=C$2<=EOMONTH(TODAY(),-1)	text	NO

 

[Fluff]

What have dates got to do with anything, you said

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.

 

[SDowse]

I have a header row with dates, so that the conditional formatting only applies to dates in the future. I left that off my post, I have that nailed and was trying to simplify my post.

 

[Fluff]

In that case please explain exactly what you are trying to do.