Hi, I a trying to replace my excel gradebook with a FM solution. What I am really struggling with is how to do the equivalent of a look up.

so students have a series of grades marked A-G

in excel I give each grade a numeric so G=1, F=2 etc

I can then highlight students who are at less than their target grade through conditional formatting. I am sure I am being thick but I cant get this to work.

Help for newbie gratfully recd..

Create a field of type Calculation and name NumberGrade.

If you have a text field called Grade,

Give NumberGrade the following definition

Case(

Grade = "A"; 7;

Grade = "B"; 6:

Grade = "C"; 5;

Grade = "D"; 4:

Grade ="E"; 3;

Grade = "F"; 2;

Grade = "G"; 1;

0

)

Another way is to calculate it with this shorter expression:

Position("GFEDCBA"; Grade; 1; 1)

This brevity works, but doesn't let you easily add spontaneous other entries in the Grade field such as "I" for Incomplete or "A-". The Case() lets you add a wider variety of entries.

Edited July 6, 201015 yr by Guest

Fantastic, thank you works perfectly.