Forum Discussion

svicario394's avatar
svicario394
Copper Contributor
Sep 30, 2024

Need a Formula to Color a Cell Based on Data Trends

I work in supermarket retail and have to report on scheduling metrics. I show on a separate chart how many weeks in a row a store missed scheduling goals by coloring the store a certain color that represents the number of weeks. Right now I color each store cell manually.

I’m looking for a formula to color in the store certain colors based on how many weeks in a row they missed the metric goal. Example, store 473 missed meeting 65% 4 weeks in a row out of 6 weeks. I want store 473 to be automatically colored orange instead of manually coloring in orange. Store 70 missed meeting 65% 2 weeks in a row out of 6 weeks, I want to color that store gray.


Any help will be appreciated!

  • svicario394 

    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) )= 2

    2 for grey, 3 for next color, etc

    • svicario394's avatar
      svicario394
      Copper Contributor

      SergeiBaklan 

       

      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%?