Conditional Formatting - A*-F Student Alpha Grade(s)

[yoplumpy]

Hi All

I am trying to set up the conditional formatting for a spreadsheet that contains grades achieved against a target value - some students have a target grade over the boundary of 2 grades - for example A/B - so either is "good".

• Any ideas on how can I format grade cells so that if it contains the target grade (or either of the 2 if over the boundary) or an earlier letter (as in exceeds the target) - as Green for "good" or if it's a later letter in the alphabet it's Red for "bad".

So it may look a bit like the table below - I hope this makes sense!

Many thanks if anyone can help.

Paul

[TABLE="width: 288"]


<colgroup><col style="width: 48pt;" span="6" width="64">
<tbody>[TR]



[TD="class: xl65, width: 64, bgcolor: transparent"]Forename[/TD]

[TD="class: xl65, width: 64, bgcolor: transparent"]Surname[/TD]

[TD="class: xl68, width: 64, bgcolor: #DAEEF3"]Target Grade[/TD]

[TD="class: xl66, width: 64, bgcolor: transparent"]Assessment 1[/TD]

[TD="class: xl66, width: 64, bgcolor: transparent"]Assessment 2[/TD]

[TD="class: xl66, width: 64, bgcolor: transparent"]Assessment 3[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent"]Joe[/TD]

[TD="class: xl67, bgcolor: transparent"]BLOGGS[/TD]

[TD="class: xl69, bgcolor: #DAEEF3"]C/D[/TD]

[TD="class: xl70, bgcolor: #C4D79B"]A[/TD]

[TD="class: xl71, bgcolor: #E6B8B7"]E[/TD]

[TD="class: xl70, bgcolor: #C4D79B"]D[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent"]John[/TD]

[TD="class: xl67, bgcolor: transparent"]SMITH[/TD]

[TD="class: xl69, bgcolor: #DAEEF3"]A/B[/TD]

[TD="class: xl70, bgcolor: #C4D79B"]B[/TD]

[TD="class: xl70, bgcolor: #C4D79B"]B[/TD]

[TD="class: xl71, bgcolor: #E6B8B7"]D[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent"]Jane[/TD]

[TD="class: xl67, bgcolor: transparent"]DOE[/TD]

[TD="class: xl69, bgcolor: #DAEEF3"]C[/TD]

[TD="class: xl71, bgcolor: #E6B8B7"]E[/TD]

[TD="class: xl70, bgcolor: #C4D79B"]B[/TD]

[TD="class: xl70, bgcolor: #C4D79B"]C[/TD]





[/TR]


</tbody>[/TABLE]

 

[NBVC]

Assuming table is in A1:F4, with Assessment grades starting in D2...

Select from D2:F4, the go to Home|Conditional Formatting, select to use a formula to determine which cells to format...

Then enter formula:

=D2<=RIGHT($C2) Note: If it is possible to have blanks, then use formula: =AND(D2<>"",D2<=RIGHT($C2))

click Format and choose Green from the Fill tab.

Click Ok, then click New Rule and repeat above with formula:

=D2>RIGHT($C2) and select Red

 

[yoplumpy]

That is perfect - Thank you so much!

Paul