Conditional Formatting Based on Average of Previous Cells

SDowse

Board Regular
Joined
Sep 23, 2009
Messages
119
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:
12345678910
1234500000
12345678910

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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
How about
Fluff.xlsm
ABCDEFGHIJ
1
212345678910
31234500000
412345678910
51-123-500100
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:J5Expression=AND(A2=0,AVERAGE($A2:A2)<>0)textNO
 

SDowse

Board Regular
Joined
Sep 23, 2009
Messages
119
Here is a snippet...
Cell Formulas
RangeFormula
D2:AC2D2=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2))
C18:AC21C18=-(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
CellConditionCell FormatStop If True
C2:AC2Expression=C$2>EOMONTH(TODAY(),-1)textNO
C2:AC2Expression=C$2<=EOMONTH(TODAY(),-1)textNO
N18Expression=AND(N$2>EOMONTH(TODAY(),-1),N18=0,AVERAGE($C18:M18)>0)textNO
M18:AC18Expression=AND(M$2>EOMONTH(TODAY(),-1),M18=0,AVERAGE($C18:M18)>0)textNO
C3:AC100Expression=C$2>EOMONTH(TODAY(),-1)textNO
C3:AC100Expression=C$2<=EOMONTH(TODAY(),-1)textNO
 

SDowse

Board Regular
Joined
Sep 23, 2009
Messages
119
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
In that case just change the ranges like
Excel Formula:
=AND(M18=0,AVERAGE($C18:L18)<>0)
 

SDowse

Board Regular
Joined
Sep 23, 2009
Messages
119
It doesn't appear to be working - here is my conditional formatting...

Excel Formula:
=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

Board Regular
Joined
Sep 23, 2009
Messages
119
Sorry, try again...

Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:AC2Expression=C$2>EOMONTH(TODAY(),-1)textNO
C2:AC2Expression=C$2<=EOMONTH(TODAY(),-1)textNO
C3:AC100Expression=AND(C$2>EOMONTH(TODAY(),-1),C3=0,AVERAGE($C3:C3)<>0)textNO
C3:AC100Expression=C$2>EOMONTH(TODAY(),-1)textNO
C3:AC100Expression=C$2<=EOMONTH(TODAY(),-1)textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 23, 2009
Messages
119
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,510
Office Version
  1. 365
Platform
  1. Windows
In that case please explain exactly what you are trying to do.
 

Forum statistics

Threads
1,175,528
Messages
5,897,949
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top