0

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.

1 Answer 1

0

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


Data

data


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

The average is in cell B8

scenario1


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

scenario2


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

rule3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.