I work for a school district and I was asked to calculate the average of the best 4 final grades of a list of students grades.

I need to calculate this for close to 3000 students. Some of these students have 10 final grades. Any advice on how to do this task? The following information is in a portal for one of the students. Portal is sorted by Final Grade in descending order.

Course_Name | Final Grade |
---|---|

A | 100 |

B | 99 |

C | 97 |

D | 95 |

E | 93 |

F | 93 |

If I understand correctly, you want to take only the top 4 grades for each student, and the work out the overall average based on those top 4 for each student (assuming also if a student has less than 4, then it averages however many they have, e.g 3).

There are a few different ways to approach this. One would be to write a script that you execute, that just tells you what the average is once it has calculated. This might be a slower method, and not as good as you would not be able to use the value live on-screen if need be.

The method I would probably go with is for each student, do a calculation which lists their 4 (or however many they have less than 4) top scores. Then, another calculation/script that adds up the value in this calculation across all of the students, to determine an average.

So, Assuming here you have a Student table, and a Grade table, linked by student id . I'm going to make up the field names but I'm sure you have equivalents, first, the calculation on Student record that I'll call "TopGrades"

ExecuteSQL ( "SELECT gradeScore FROM grades WHERE student_id = ? ORDER BY gradeScore DESC FETCH FIRST 4 ROWS ONLY" ; "" ; "" ; Students::Student_ID )

So, what this does is select for the given student, a list of their scores (only the first top 4, when sorted by the score in descending order). This should return a list of the top 4 (or if less than 4, the top X) grades, e.g. in your example you'd get:

100

99

97

95

-----

Next, to get your average you could write a script to loop through all student records and combine this calculation into one big list of scores. Then once you've done that, do a ValueCount function over it to find out how many total grades you have. You could then replace all carriage returns with a "+" symbol, and evaluate it to determine the overall total (Sum function unfortunately doesn't work with a return delimited list of values)

Evaluate ( Substitute ( TopGrades ; "¶" ; "+" ) )

This will give you the overall total of scores, then just divide by the total number of scores you have and there's your average)

---

Now I'm sure some smart cookie will be able to give you the answer in the form of a single ExecuteSQL query