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.
- Above 20% - 0%to20% - -5%to0% - -20%to-5% - Below -20%
My first solution was
But I couldn't figure out how to get it to work for between.
Any help would be much appreciated! Thanks!