Just wondering if there's a way without nested substitutions or helper columns, both of which I can of course do!
This year our GCSE results are a mixed economy: legacy GCSE qualifications are still using the A*-G grading system, whilst new GCSEs are using 9-1.
The old grades equate to the new grading system thus:
Excel 2016 (Windows) 32 bit
AD AE 1 Grade Value 2 A* 8.5 3 A 7 4 B 5.5 5 C 4 6 D 3 7 E 2 8 F 1.5 9 G 1 10 U 0
Sheet: Sheet1
How do I easily substitute letter grades for numbers to find the best six results for each pupil? Pupils may have fewer than six results (as is the case for Person 14 in the attachment).
Here's a snippet from the workbook:
Excel 2016 (Windows) 32 bit
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB 1ID Name Ar Bi BS Ch ClasCi Dr DT EAL En EnL Fr FT GCSE PE Ge Gm Hi La Ma MS Mu Ph RS Sc Sp Best 6 Sum Required 2 1Person 1 C C 3 3 4 5 3 3 2Person 2 6 7 B 5 6 7 5 9 7 6 4 3Person 3 A 8 8 6 7 6 A 8 6 5 4Person 4 7 B 6 6 4 5 5 5 6 5Person 5 B 7 7 7 7 8 8 7 7 7 6Person 6 C 3 3 4 6 5 4 2 26 =4+3+4+6+5+4
Sheet: Sheet1
I'll be very grateful for any bright ideas!
Bookmarks