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.
Last edited by waq07; 03-11-2009 at 03:06 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.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
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.
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?
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.
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?
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"
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.
No sorry my bad
You are right. Questions are in columns and pupils in rows.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks