Exam grade conditional formatting

[Liammi]

Hi. I am trying to analyse our students' results against their forecast grades - A* A B C D E F G etc

I have a worksheet showing the student names. Then there is a column for each subject with the corresponding forecast grade column adjacent to it.

I need to apply conditional formatting so that if the result is equal to the forecast grade then the result cell is amber, if it is above then it is green, if it is below then it is red. Also, if there is no forecast grade then the result cell should be blank.

I can apply the formatting to the first cell of the first subject, but how do I copy it to the rest of the column and to the other subjects? I have tried format painter, but it compares the results to the first cell only.

Hope this makes sense and you can assist. Many thanks

 

[squidgeny]

Drag it down using AutoFill, then click the Paste options icon and select "fill formatting only". You may need to make sure no $ signs are using in the conditional formatting.

 

[Liammi]

Many thanks for your quick response, removing the $ did the trick. How can I tell it to ignore the formatting if the forecast cell is blank. it currently sees it as being equal??

thanks again

 

[R Pelle]

Excel Workbook
	A	B	C
1		grade	forecast
2	joe	A	B
3	jane	D	C
4	pete	C	C
5	holly	A
Sheet3

Excel 2010

Rules:
1.Formula: =if(c2="",true,false) - applies to $c2:$c5 - color none - stop if true= Yes
2. Cell Value = B2 - applies to $c2:$c5 - color amber - stop if true= No
3. Cell Value< B2 - applies to $c2:$c5 - color red - stop if true= No<B2 true="No<B2" if stop - red color $c2:$c5 to applies>4. Cell value >B2 - applies to $c2:$c5 - color green - stop if true= No

Does this help?

Rob

 

[Liammi]

That works great, thank you very much. The only thing now is that it is seeing an A* as lower than an A when it fact is is higher. Is there any way to solve this one??

Thank you so much
Denise

 

[R Pelle]

Change the "green" one to Formula: =if(or(c2= "A*", C2 < B2), true , false)

Rob

 

[Liammi]

That works perfectly, thank you so much

 

[R Pelle]

Thanks for the feedback

Rob

 

[Liammi]

Hi Rob

I think I'd been looking at the spreadsheet for too long because the A*/A problem is still there. The formatting so far is:

Rules:
1.Formula: =if(B2="",true,false) - applies to $B$2:$B$285 - color none - stop if true= Yes
2. Cell Value = C2 - applies to $B$2:$B$285 - color amber - stop if true= No
3. Cell Value > C2 - applies to $B$2:$B$285 - color red - stop if true= No
<B2 applies to $c2:$c5 color red - stop if true="No<B2">4. Formula: =if(or(B2="A*",B2<C2,true,false) - applies to $B$2:$B$285 - color green - stop if true= no

With these rules it is mostly OK except if the student got an A* result (col B) and a forecast of A (col C) then the result grade is red, when it should be green.

If the result was A and Forecast A*, the result is green, when it should be red.

I have tried to make it right, but to no avail so am now asking for your help yet again. Is the * the problem, should all A* be changed to something else???

Thanks, Denise

 

[R Pelle]

Hi Denise, Just saw your post and I'm working on it.
I think I mixed up the resutl and the forecast.
Re-reading post 1:
a)
If Result is better than forecast then green.
If Result is equal to forecast then amber.
If Result is less than forecast then red.
(I read it the other way around)

b)
A* is highest score, followed by ABCDEFG.

c)
If forecast is blank - don't apply cellcolouring.

That's what Ill work on....
Rob