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!
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.