Hi all,

I am a teacher hoping to design a spreadsheet to track our new lower years assessment. Basically, we have 4 possible grades (F<D<S<E) that a student could achieve and need some way of identifying which grade is the most common and then conditionally formatting a cell based on a comparison between their working at grade for an assessment and their target.

Click here for a copy of the spreadsheet.

In my head this is the way it would work for AO1 - Knowledge:
  1. The formula works out the highest number in the 4 columns under AO1 (2)
  2. It then finds the Grade (letter) to the column which it belongs (D)
  3. It then compares this letter with the grade in the students target column (S)
  4. As this grade is less than the target grade it would change the end column containing the most common grade to red. If it was the same, amber, and if it was above then green.
  5. This would then be repeated for each of the AO's.
  6. It would then take the average of these letter grades and display this final grade in the last column labelled "Working at Grade" which is conditionally formatted to compare with the target grade in the same way.

I would be enormously grateful for any help that you can offer or light that can be shed on how to write a formula that does this, I am far from an expert on this sort of thing.

Thanks,

Jay