Conditional formatting as you move down a row

[1021557]

Hi, I have setup a formula that is based on other rows to return a certain value, in effect in cell AW4 it's something like =IF(AM4="",Average,AM4) with Average being a named range. Cell AW5 has the same formula except the values for average move down one; I'm covering range AW4:BE94 with this formula. It all works great but what I want to do is conditionally format the cells in range AW4:BE94 so that if the value in those cells is equal to the average value in its corresponding row then it will be highlighted, if it something other than the average then it's not highlighted. I'm kind of stumped as while it's easy to highlight based on one value this is dynamic as the Average figure is different in each row as you move down the sheet. I've tried putting in different formulas but none seem to work. Any help would be appreciated.

 

[etaf]

no sure i understand what you are comparing

I want to do is conditionally format the cells in range AW4:BE94 so that if the value in those cells is equal to the average value in its corresponding row then it will be highlighted,

You are taking the average of AW4:BE94
so thats easy
=Average($AW$4:$BE$94)
now we need to compare to the cell you want to reference

can you give a few examples

 

[1021557]

Hi, Sorry what you propose isn't correct. Just to clarify, suppose I have a range of prices for a particular product in cells A2:I2 but as some suppliers haven't quoted a price for that item then the cell is blank, so just say that cell A2 is blank. Then in cell J2 I calculate the average of A2:I2. I have assigned the range name 'Average' to the averages column, i.e. column J. Following on from that I have a formula in cells K2:S2 such that if there is a value in a cell A2:I2 then it returns that value, if not then it returns the average from J2. I then have this working down a few hundred rows. This all works fine. What I'd really like to do though is where the cells in columns K2:S2 are returning the average as opposed to returning a value from cells A2:I2 I'd like to use conditional formatting to highlight these. Something like: If reading a non-blank cell in A2:I2 then don't highlight but if returning the average value from column J then make colour red. Hope this makes sense. Thanks again.

 

[etaf]

what formula are you using to test A2 is blank and then enter the average into J2 ?
Then what formula are you using in K2:S2

you can use a very similar formula for conditional formatting

 

[1021557]

J2 formula is =AVERAGE(A2:I2)
Average or value formula is =IF(A2="",Average,A2) and so forth for each row and column where I'm using the actual or average

 

[etaf]

Then you can use the formula to show true
=$A2<>""

If A2 is NOT blank - then it will be using A2 value and
you can use that in the other cells for conditional formatting value

The $ keeps the column A as you move from K to S

for 2007, 2010 or 2013 excel version
Conditional Formatting


Highlight applicable range >>

K2:S2



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



=$A2<>""



Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

if you want to test
A to K for blank
take off the $