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

Forename	Surname	Target Grade	Assessment 1	Assessment 2	Assessment 3
Joe	BLOGGS	C/D	A	E	D
John	SMITH	A/B	B	B	D
Jane	DOE	C	E	B	C

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


</tbody>

 

[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