CODEWITHSUNDEEP
excel
Stuart Patterson
Stuart Patterson

Reputation: 3

Colour cells based around average of column for whole sheet

I am attemping to colour cells which are pupils test results in excel.

I wish to colour them based on if they are more than 10% of the test total above average (green colour) or more than 10% of the test total below average (red colour) and those within 10% of the test total above or below average remain uncoloured.

eg, if a test is out of 30, green for those more than 3 above average and red for those more than 3 below average

eg if a test is out of 200, green for those more than 20 above average and red for those more than 20 below average

I have results in columns, each column is a different test.

Thanks!

Upvotes: 0

Views: 1739

Answers (2)

Excellll
Excellll

Reputation: 5785

Everyone is right; you can do this with conditional formatting.

I'm assuming you have a row that contains the max score for each test (row 2 in my example).

  • Select all the test scores (all columns) and click Conditional Formatting on the Home ribbon. Choose New Rule....
  • Choose Use a formula to determine which cells to format. Enter the following formula:
    =B3>AVERAGE(B$3:B$32)+0.1*B$2
    where the first full column of test scores is in B3:B32 and the max score for the first test is in B2.
  • Set your format for the cell (green). And you're done.

Repeat this for the red cells using this formula: 

=B3<AVERAGE(B$3:B$32)-0.1*B$2

enter image description here

Upvotes: 1

haz0rd
haz0rd

Reputation: 270

There is something called conditional formatting that can be used to accomplish what you want

Upvotes: 0

Related Questions