How do I higlight a cell certain colours depending if it's higher or lower than another cells value?

Question

I want to have a cell highlighted Green if it's above the average score, amber if it's within 1000 points less than the average score, and red for any value below that. Even excluding the 1000 point rule, I can't seem to have the cell be highlighted red if it falls below the average score, as all of them remain green.

If I do =$B2>8000 then the specific range will only highlight values above 8000, which I want, but if I do =$B2>B32, with B32 being the average then all of them are highlighted.

Answer 1

The rules depend on whether the average has already been calculated:

---

Data

---

Scenario#1 - the cell is part of the averaged range AND the average has been calculated

The average is in cell B8

---

Scenario#2 - the cell is part of the averaged range AND the average has NOT been calculated

Custom formulas are used in each case.

• Green: =D2>average(D2:D6)
• Amber: =AND(D2<=average(D2:D6),D2>average(D2:D6)-1000)
• Red: =D2<=average(D2:D6)-1000

---

Scenario#3 - the cell is NOT part of the averaged range AND the average has NOT been calculated

Custom formulas are used in most cases.

• Green: =average(G3:G6)
• Amber: =and(F2<=average(G3:G6),F2>=average(G3:G6)-1000)
• Red: =D2<average(G3:G6)-1000