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

 

[R Pelle]

Excel Workbook
	A	B	C	D	E
1	Name	.Grade	.Forecast		IF GRADE = FORECAST THEN AMBER
2	joe 1	A	A		IF GRADE > FORECAST THEN GREEN
3	jane 1	B	B		IF GRADE< FORECAST THEN RED
4	henry 1	C	C		IF FORECAST='' THEN NO COLOUR
5	peter 1	D	D
6	lisa 1	E			HIGHEST = A*
7	silvia 1	F	F		FOLLOWED BY A,B,C,D,E,F,G,H
8	john 1	G	G		H IS LOWEST
9	jenny 1	H	H
10	joe 2	A	A*		<===
11	jane 2	B	C
12	henry 2	C	D
13	peter 2	D	E
14	lisa 2	E
15	silvia 2	F	G
16	john 2	G	H
17	jane 3	B	A
18	henry 3	C	B
19	peter 3	D	C
20	silvia 3	F	D
21	john 3	G	E
22	jenny 3	H	F
23	joe 4	A*	A*		<===
24	jane 4	A*	A
25	henry 4	A*	B
26	peter 4	A*	C
27	silvia 4	A*	D
28	john 4	A*	E
29	jenny 4	A*	F
30	ben 4	A*	G
Sheet2

Excel 2010




CF Rules

Excel Workbook
	must be in this order
	Rules	Format	Range	Stop if true
1	Formula =IF(C2="",TRUE,FALSE)	blank	=$C$2 : $C$30	Yes
2	Formula =IF(AND(C2="A*",B2="A"),TRUE,FALSE)	red	=$C$2 : $C$30
3	Cell value = B2	amber	=$C$2 : $C$30
4	Cell value > B2	green	=$C$2 : $C$30
5	Formula=IF(B2="A*",TRUE,FALSE)	green	=$C$2 : $C$30
6	Cell value< B2	red	=$C$2 : $C$30
...

Excel 2010

Hope I got it right this time.

Rob

 

[R Pelle]

To see the effects of rule 2 and 5 assign a slightly different shade of red and green. If happy change to same red and green as the others.
Rob

 

[R Pelle]

Just learned something new on the forum.
This works too and its shorter:

Formula =C2=""
Formula =AND(C2="A*",B2="A")
Cell value = B2
Cell value > B2
Formula=B2="A*"
Cell value < B2

 

[Liammi]

Many thanks for this Rob, apologies for the delay but I have been out of the office this morning. I the meantime I had figured out a workaround based on the previous rules you gave me. I moved the amber rule to number 2, then added another rule (as rule 3) for results equalling A* to be green with stop if true. Then changed the other "green" to a straightforward less than value.

But as you say, I think the one you have just given me is more straightforward.

Thanks again for all your help, I have learnt a lot in the last couple of days.

Regards
Denise