Build a Risk Index That Colors Itself

When this workbook is finished, you can change one number and watch the whole thing follow. Move a cut-off from 65 to 70 and every row re-bands, every fill recolors, every count updates, and the legend still matches the map. Nobody can color a cell by hand, because no cell has a color of its own. That is the whole trick, and it takes about twenty minutes to build. The example here is a security risk index across twenty sites. The same shape works for vendor scoring, lead scoring, incident triage, or any list where a number has to turn into a label and a color. The fault, and where it actually comes from You have met this file. A scored list, colored by hand, that nobody quite trusts any more. Look closely and the same faults turn up every time: Two rows score 61.4. One is amber, one is yellow. The same band is drawn in two shades, because two people picked from the palette on two different days. A row sits below the cut-off and is colored red anyway, because somebody knew that site was a problem. A score lands exactly on 65, which appears in two bands, so the answer depends on who typed it. One row has no band at all. It quietly drops out of every count. These look like five separate mistakes. They are one mistake, five times. The rule lives in the formatting instead of in a column. A color is not a value you can test. You cannot write a formula that asks "is this row the right shade of amber," so nothing checks it, and it drifts. The test: can you sort by band? If the band is only a color, you cannot sort it, count it, or filter it, and neither can anybody else. That is the tell. The chain: score, then band, then color Everything below is one idea applied three times. Each thing is derived from the thing before it, and only the first one is typed. Layer Where it lives Who decides it Sub-scores Four columns, one per category Your source data. Typed once. Composite score A formula, from the sub-scores and the weights The weights row Band A formula, from the score The