comparing text fields

[jimmy474]

Hi,

I am in the process of developing a Level Tracking System for my school where we enter levels for students in a subject. The problem is, for some subjects, the levels are one of either: Fail, Pass, Merit or Distinction.

Now, each student is set a Target Level as shown in the attached JPEG.
For each 'AO', the result is matched to their target level and a conditional format should then check whether the result for the 'AO' is lower than, same as, or above the target level. If its lower, then a conditional format shades the cell with Red, if the same then its shaded with Amber, if above then with Green.

However, I can't seem to compare the two values (AO and Target Level). Is there a way of doing this? The user should only be able to choose F, P, M or D using a drop down list.

I tried to assign each grade a number but this didnt work.

Please help...

[ratcat]

G'day Jim and welcome to the forum,

If you supply a working copy of the spreadsheet, someone in the forum maybe help you out. I know it will be easier for me.

My two cents worth, make the user select F, P, M or D using a drop down list.

With a few helper columns to calculate the average mark for the final grade.

Then you can compare the Target grade to the Final grade.

Hope that helps


Cheers

[jimmy474]

I have attached an extract from the file that I am working on.

Just to clarify the problem - the mark entered in AO should then be checked against the target grade and the AO cell should change colour accoring to the conditional format set.

Any help would be really appreciated.

[ratcat]

G'day Jim,

Sorry for the slow responds, the kids are unstoppable tonight.

The conditional formatting is working the way you ask it. But not the way you like.

Think alphabet the first letter is A. A = 1

So....

F = 6

P = 16

M = 13

D = 4

P is greater than M and D.

That why the conditional formatting returns the wrong colours for you.


I'm out of options at the moment. I think the kids have fry my brain.

Cheers