Compare actual grade to potential grade and highlight cells accordingly

  • 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

  • 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 is the top grade
    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.

  • 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..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!