Conditional Formatting using average function in Google Sheets/Excel

Question

I am trying to figure out how to conditionally format a particular cell depending on the average value of a number of other cells. So I've tried a variety of solutions all either ERROR-ing or not working correctly.

I would like to have between values, and the values in the cells are %s that are pulled in and converted from another sheet.

So for example Sheet1 is my working sheet. Sheet1 pulls in values from Sheet2 (which is populated automatically from analytics API). Sheet1 has a column with theses values which have been converted into percentages.

I'd like to average these values in the formula and then return a conditional format depending on whether it sits between two values.

EG.

 - Above 20%
 - 0%to20%
 - -5%to0%
 - -20%to-5%
 - Below -20%

My first solution was =IF(AVERAGE(E8:E11,E15:E16,E21:E22,E25)>20,"true","false")

But I couldn't figure out how to get it to work for between.

Any help would be much appreciated! Thanks!

Answer 1

To create a conditional format for an Average of those ranges you would need the below rule:

=AVERAGE(E8:E11,E15:E16,E21:E22,E25)>=20%

You do not need to wrap it in an IF function.

For -20 to minus -5, you effectively need two rules, and for both of them to be TRUE. For this you can use AND.

=AND(AVERAGE(E8:E11,E15:E16,E21:E22,E25)>=-20%,AVERAGE(E8:E11,E15:E16,E21:E22,E25)<=-5%)