2 Replies Latest reply on Oct 23, 2013 4:02 AM by Jax

    selecting 3 best grades and calculating average

    Jax

      Title

      selecting 3 best grades and calculating average

      Post

           Dear all,

           I'm stuck on the following problem. Students can take 3 or more courses. I need to collect grades using the student ID, discard the worst ones if they took more than 3 and calculate the average. I'm stuck. Hopefully, somebody more knowledgable than me can crack this problem for me.

           All the best,

      student_courses_db.png

        • 1. Re: selecting 3 best grades and calculating average
          philmodjunk

               I read that to mean that you want the average of the three best values in allocation::module grade for a given student. I am further assuming that Module grade is a number, A = 4, B = 3, C = 2, ...

               First, double click the line between student and allocation. Select a sort order for allocation that sorts by module grade in descending order.

               Let ( [Best3 = LeftValues ( List ( allocation::module grade ) ; 3 ) ; // get up to best 3 grades in return separated list
                        Best3 = Left ( Best3 ; Length (Best3) - 1 ) ;               //Strip off extra return added by LeftValues function
                        cnt = ValueCount ( Best3 )
                       ] ;

                       Evaluate ( "sum ( " & Substitute ( Best3 ; ¶ ; " ; " ) & " ) " ) / cnt
                      )

               Note: if there are only two grades in Best3, the average is computed by dividing by 2

          • 2. Re: selecting 3 best grades and calculating average
            Jax

                 Wow! The perfect solution and a description even I, as an FM novice, could follow. Thanks a lot, PhilModJunk. A life saver.