You can always add a calculation field that reflects a numerical value for the grade achieved... Grade being your Grade field
Case ( Grade = "Pass" ; 2 ; Grade = "Merit" ; 4 ; Grade = "Distinction" ; 6 ; 0 )
Finally average the GradeValue Fields and translate the result back to text. Hope this helps
Thanks for the reply, however I could do with a atep by step as I'm a bit of a newbie:-)
it would help to know how your database is designed? Do you have a record per each student with various fields for each grade?
Yes it has a record for each student with fields created for each grade they attain. It is set out as 6 fields for the 6 grades - These 6 grades add up to make a unit total.
Ok assuming that each field for the grades are called grade1,grade2,grade3 etc etc etc
Create a new calculation field called FinalGrade with the following calculation:
Div(ValueCount ( FilterValues ( List ( grade1; grade2; grade3; grade4; grade5; grade6) ; "Distinction" ) ) * 6 + ValueCount ( FilterValues ( List ( grade1; grade2; grade3; grade4; grade5; grade6) ; "Merit" ) ) * 4 + ValueCount ( FilterValues ( List ( grade1; grade2; grade3; grade4; grade5; grade6) ; "Pass" ) ) * 2; 6)
This will return the average score in a numeric value. Hope this helps
For proper structure, each grade should really be its own related record not individual field.
That being said, the calc for total score could probably just be ( as long as there arent any invalid values ):
Evaluate ( Substitute ( List ( G1; G2; G3; G4; G5; G6 ); [ "Pass"; 2 ]; [ "Merit"; 4 ]; [ "Distinction"; 6 ]; [ "¶"; "+" ] ) )
Thanks very much for your time on this, much appreciated:manhappy: