Conditional Formatting Averages to Exclude values of 0

**spikesplayfood** · 06-12-2012, 04:21 PM

I have four cells that already have conditional formatting to highlight the two that are above average in red and the two that are below average in green. sometimes the cells have a value of $0, but the conditional formatting includes that as a result will highlight those cells in green. I am looking to exclude the $0 from the formatting so that it will only highlight the cells with actual values in them.

Thanks for the help

- Matt.

**TMS** · 06-12-2012, 04:42 PM

You don't give us much to go on. No cell references, no formulae, no Conditional Formatting formulae, no sample workbook. How would you have us proceed?

Regards, TMS

**spikesplayfood** · 06-12-2012, 04:47 PM

test.xlsx

Sorry about that, in the attached example, the formatting is set up for B11:E11 that any value above the average is higlighted in red and the values below the average are in green. I'd like to set it so it excludes the $0.

**TMS** · 06-12-2012, 04:56 PM

You'd need something like: =AND(B2<>0,B2<$F2) rather than the "simple" Below Average.

Regards, TMS

**spikesplayfood** · 06-12-2012, 05:17 PM

Thank you for your quick response, maybe i'm not describing this right. I'm only worried about data in column B through E, lets say as an example B11 through E11. I'd like to have the conditional formatting set up so that any value in those four cells that's above the average is red, below is green, but that the '0' values are excluded in that conditional formatting. With F11, i was able to figure out the average and exclude the '0' values no problem, its just the conditional formatting that's the issue.

Thanks again,

- Matt

**spikesplayfood** · 06-12-2012, 05:24 PM

actually, i figured it out myself, thank you for your input, it helped me determine this, I just used the function in the cell =IF(SUM(B4:B10)=0,"",SUM(B4:B10)). No change to the conditional formatting was needed. Thank you for your help.

- Matt