Forum Discussion
Need a Formula to Color a Cell Based on Data Trends
You may apply conditional formatting rules to range $A$4:$A$100 or so, one rule per each color, with formula
=SUMPRODUCT( --($N4:$S4<$N$2:$S$2) )= 22 for grey, 3 for next color, etc
thank you this has been very helpful!
I’m having issues using this formula for cells that have 4 numbers in a row under 65%, 1 above 65%, then 1 under 65%. Cell A7 I’m trying to make orange but when I input the formula and use 4 as orange (showing for 4 weeks in a row not meeting the metric) nothing happens. Could it be because there are technically 5 cells under 65%?
 
 
We apply the rule to entire range, not cell by cell. In first part of formula only columns are fixed, for the rows are relative reference. And we use firs row of the range. Other words $N4:$S4, not $N$4:$S$4
Please check in attached file.
