conditional formatting for a full column based on the average value in that row?

Question

I'm going through a sheet with multiple different data points across different rows (for different versions of the same action each on their own row).

In order to easily compare from one row to the next, I would like to use conditional formatting to on a simple level as follows: - highlight cell green if a value in that cell is equal to or greater than average for that column - highlight cell red if a value is less than average for that column

So for example, I need all cells in column F to be green if the value in each cell is equal to or greater than the average of all values in that column or red if it's less than the average of all values in that column.

But I'm struggling to get that working.

Does anyone have any ideas?

I tried in 2 ways, trying to get a custom formula that calculates this average and merely doing greater than or less than in reference to a specific cell in another column (I have a separate column where I only have the average of each column calculated as follows: =Average(E3:E18) but neither is working consistently, it's highlighting some cells green that should be red and vice versa.

Answer 1

green color:

=IF(AVERAGE(F:F)<=F2, 1)

red color:

=IF(AND(AVERAGE(F:F)>F2, F2<>""), 1)

Answer 2

Maybe what you want is this CF formula rule:

=D2<average(D:D)

applied to the whole of D2:Rx (where x is last row) for red, and a corresponding rule with < replaced by >= for green (or simply format all range with 'standard' fill of green).