Build a Risk Index That Colors Itself

Step 1: weights in one row

Put the four category weights in four cells on a Rules sheet, and nowhere else. Name the sheet Rules so it is obvious which tab holds the decisions.

Rules!B7:E7   0.30   0.30   0.20   0.20

Now the composite score on each row is one formula, pointing at that row:

=ROUND(SUMPRODUCT(E6:H6, Rules!$B$7:$E$7), 1)

SUMPRODUCT multiplies each sub-score by its weight and adds the results. The dollar signs lock the weights row, so you can fill this formula down twenty rows and every one still reads the same weights.

The temptation is to write =E6*0.3+F6*0.3+G6*0.2+H6*0.2 instead. Do not. That buries the weights in twenty formulas, so changing them means twenty edits, which means it never happens, which means the weights in the file stop matching the weights in the policy.

Step 2: cut-offs in one table

Five bands, five rows, with a lower bound and an upper bound written down. The bounds must climb in order, and they must touch with no gap and no overlap.

An overlap is the fault that produced the 65 problem earlier. If High starts at 65 and Elevated ends at 65, the band depends on which rule got evaluated first, which is not a policy anyone wrote down.

Where these five numbers come from is its own question, and a bigger one than the spreadsheet. If you picked them because they were round, read how to choose cut-offs from the data before you defend them to anyone.

Step 3: look the band up, never type it

The band column is a formula. It reads the score and finds the last cut-off the score clears.

=INDEX(Rules!$B$12:$B$16, MATCH(I6, Rules!$C$12:$C$16, 1))

Read it right to left. MATCH with a third argument of 1 means "find the largest value that is less than or equal to mine, and tell me its position." A score of 61.4 clears 0, 25 and 45, but not 65, so it stops at 45, the third row. INDEX then returns the third band name, Elevated.

The one rule this depends on: the lower bounds must be sorted ascending. MATCH with a 1 walks the list assuming order. Out of order, it returns a wrong answer confidently and without an error, which is the worst kind of wrong.

The gain is bigger than saving typing. Once the band is text in a column, you can sort by it, count it, filter it, and pivot on it. It became data.

Step 4: let the color follow the band

Select the whole data area, columns and all, then add one conditional formatting rule per band using a formula rule:

=$J6="Severe"

The single dollar sign is the entire mechanism. $J locks the column, so every cell across the row asks the same question. The bare 6 stays relative, so row 7 asks about $J7, row 8 about $J8, and so on down. One rule colors a whole row, and twenty rows need no extra work.

Repeat for the other four bands. Five rules, five fills, and that is every color in the workbook. There is no sixth place a color can come from, which is exactly why the two-ambers problem cannot happen again.

Anchor the rule to the row that starts your data. Write it while sitting on row 6 and the reference is $J6. Write it while sitting somewhere else and every row will be off by the difference, which looks like the colors slipped by one.

Step 5: two checks that catch the rest

Two formulas on the Rules sheet, in plain sight, doing the checking nobody remembers to do.

=IF(ROUND(SUM(B7:E7),4)=1, "Weights balance", "Fix the weights, they must total 100%")

Weights that total 96% still produce a score. It just quietly understates every row, and nothing looks broken.

=IF(F17=COUNTA('Risk Index'!$A$6:$A$25), "Every site lands in one band", "Banding gap, check the cut-offs")

Here F17 totals the per-band counts, each one a COUNTIF against the band column. If the two numbers disagree, a row fell through a gap in your cut-offs. That is the row that used to vanish from the report with nobody noticing.

Both checks read as a sentence, not a number. A reader who has never opened this file can tell whether it is healthy without knowing how it works.

Step 6: the change log

One tab, three columns: what I found, what I changed, why. Every correction gets a row.

This is the tab that turns a rebuild into something a client can approve. Without it, you hand back a file that looks different and they have to take your word for it. With it, they can disagree with any single decision without reopening all of them.

One entry type matters more than the others. When two source files disagree, the change log is where you say so rather than quietly picking a winner. "This site appears on two maps with two scores, flagged for your decision" is a better line than any silent fix, because which source wins is a policy call, not a spreadsheet one.