#78 — Fill Aggregation Value in The First Row of The Same Group of Data

#programming #tutorial #beginners #productivity

Problem description & analysis:

Here below is a data table:

Task: Now we want to count the following information for each class: the number of students with scores above 90, the number of failed students, and the average score after removing the highest and lowest scores, and then fill in the results in the first row of each class (calculate in cell D1).

Solution:

Use SPL XLL and enter the following code:

 A
1 =E(‘A1:C19’).derive(:ScoreAbove90,:Failed,:AverageScore)
2 =A1.group(Class).run(~(1).ScoreAbove90=~.select(Score>=90).len(),~(1).Failed=~.select(Score<60).len(),~(1).AverageScore=~.sort(Score).m(2:-2).avg(Score))
3 return A1.new(ScoreAbove90,Failed,AverageScore)

The results are as follows: