• Hello, I wonder if some expert could help me out here.

I work as a teacher and I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.

I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.

One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat. Could anyone give me some pointers for the code?

Many thanks

• Re: Compare actual grade to potential grade and highlight cells accordingly

you could probably use a lookup to change to a value in some helper columns
=LOOKUP(A11,{"A","B","C","S"},{2,3,4,1})
and then compare the helper columns and conditional format the grades
(note the letters need to be ascending )

how would you compare the A/B S/A
not sure how that would work in the array

EDIT
if you putin alpha order based on first character it will work with the array
so
=LOOKUP(A11,{"A","A/B","B","C","C/D","D","S"},{1,10,2,3,11,4,100})
assigns the values
and then simple enough to compare
you need the helper columns because conditional format wont do the array lookup

see the attached example - with conditional formating
green for higher or equal to
red for lower

## Files

ETAF

• Re: Compare actual grade to potential grade and highlight cells accordingly

Many thanks, I will give it a go

• Re: Compare actual grade to potential grade and highlight cells accordingly

added an example sheet to the post above with the formula and better values for A,B etc
and also used the conditional formatting to highlight the two grades

ETAF

• Re: Compare actual grade to potential grade and highlight cells accordingly

Hiya

Thanks for the help

I have attached my work in progress ... looking at columns K to N I want to highlight Column M red if the progress grade is less than the potential

It doesn't quite seem to work and I suspect it has something to do with the order Excel chooses for the dual grades.

In our school

S/A
A
A/B
B
B/C
C

and so on down to the bottom grade

So

S should be HIGHER grade than S/A

and A is a LOWER grade than S/A

I cant quite make out the formatting properly... any clues?

(assuming I have explained myself well enough)

Many thanks for your help, you have saved me an awful lot of time already.

## Files

• Re: Compare actual grade to potential grade and highlight cells accordingly

looks OK from an assignment of values
this is the issue
=\$N2>\$L2

in M4 when you goto the cond format is referencing to row 2 and not row 4

high light the M range from row 4
change
=\$N2>\$L2
to
=\$N4>\$L4

also apply to \$M\$4:\$M\$34 - to cover the complete column

## Files

ETAF

• Re: Compare actual grade to potential grade and highlight cells accordingly

Thank you kindly... that is brilliant.. I can now see and understand what to do..

• Re: Compare actual grade to potential grade and highlight cells accordingly