Conditional formatting of cells excel 2007 marks per question

**waq07** · 03-07-2009, 01:43 PM

see spreadsheet. columns a-f contain pupils details.g4-r23 contain marks for 12 questions. row 24 contains total marks for that question. questions are out of 2,3,4 and 5 marks. I would like to format the cells to reflect % for that question. At the moment I have to format column by column to do that. When I format the whole spreadsheet 0 and 1 show in red 2 and 3 in yellow and 4 and 5 in red. I want 2 when equalling 100% to show green not yellow . I hope that makes sense.

**NBVC** · 03-07-2009, 09:56 PM

It is not very clear what you are looking for.

You are currently using a gradient scale that colours in shades of green to yellow to red according to where the number to colour fulls within range 1 to 5.

So do you want to eliminate the scaling and just use the 3 solid colours of green, yellow, red to meet specific conditions? If so, please detail those conditions here.

**waq07** · 03-08-2009, 09:16 AM

yes that is correct. Each question is out of a different mark. I want to analayse marks per question using green, amber and red. red <33 yellow 34-60 green >60.

see attachment now. I have done it for each column one at a time. I am using conditional formatting. Is that the best way and Is there a quicker way to do that? I have had to place 0 at the top and full marks at the bottom to do that.

**NBVC** · 03-09-2009, 09:11 AM

Here attached is an alternative... which allows you to apply to whole table at once with one set of rules...

It uses solid colours instead of gradients.

You will still need the bottom row to show top scores, but you won't need the 0 row at the top.

Does this work for you?

**Andy Pope** · 03-09-2009, 09:32 AM

It could be wrong but I don't think you can use the bottom row for the conditional test.

Take the first column which has 4 as the last row value. That suggests for the 1st row the 1/4 = 25%. But the question only has 3 possible answers, which are 1, 2 and zero. So a mark of 1 is actually 33%.

I think you need a column of Max scores against which to test.
My Max is based on those actual scores so may need adjusting if no pupil got full marks.

**NBVC** · 03-09-2009, 09:58 AM

Excuse my early morning denseness, Andy.. how do you know that the question has only 3 possible answers and that they are 0, 1 or 2?

**Andy Pope** · 03-09-2009, 10:10 AM

I don't for fact.

As I mentioned the assumption is based on given results and that the OP states
"questions are out of 2,3,4 and 5 marks"

**NBVC** · 03-09-2009, 10:13 AM

Oh, Ok... I thought I was going crazy, but I think that is what the last row is showing...no?

We shall wait for OP to clarify.

**Andy Pope** · 03-09-2009, 10:19 AM

No sorry my bad

You are right. Questions are in columns and pupils in rows.

**waq07** · 03-11-2009, 03:06 PM

This is solved. Thank you very much for your help and time. I will use this spreadsheet as a basis for all my future data collections

Much appreciated

Waq07