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.