Average score for a specific percentage of results in WPS Table 2007

If you want to ask for the average score of the top 30% students in each subject, then we will definitely think of using the SUMIF function. The newly added function in WPS Table 2007 is very handy for solving this type of problem. However, if our table of records already exists, and we cannot change the structure of the performance sheet, such as increasing the number of assists in the academic rankings. So, what should be done now? One possible way to calculate the average score of the first 30% students in each subject is to sort each subject separately, and then to calculate the number of the top 30% students in the subject (given the potential for ranking and juxtaposition), so the number of the first 30% in each subject is different, Even the same class is the same), and then calculate the average points, this method can certainly solve the problem. However, this operation is quite cumbersome, especially when the number of such tables is relatively large.

In the WPS table, we can solve this problem with the help of the SUM function and the array formula to get rid of the tedious operation.

The score table is shown in Figure 1. The scores of each subject are distributed in the C3:g95 cell area, each classified as a subject.

We first asked for the number of the first 30% students in the Chinese language subject C. Click C96 Cell, enter the following formula in the cell =sum (1* RANK (c$3:c$95,c$3:c$95) <=50*0.3), and then position the mouse over the edit bar, press the "Ctrl+shift+enter" shortcut key, You can see that the tag of the array formula is automatically added to the outer layer of the formula-a pair of curly braces. Of course, the number of qualified people can already be seen in cell C96. As shown in Figure 2.

The method of finding the total score of eligible students is basically the same as that of the number. As long as you enter the following formula in cell C97 =sum ((c$3:c$95) * (RANK (c$3:c$95,c$3:c$95) <=c96)), then you must remember to press the "Ctrl+shift+enter" shortcut key to get the corresponding score.

As for the average score, it is much simpler. Just enter the formula "=c97/c96" in the C98 cell and press ENTER to get the score you want.

Select cell C96:c98. Drag the fill handle of the range of cells to the right to copy the formula to the G98 cell. After releasing the mouse, you can easily get the number of qualified people, total score and average score for all disciplines, as shown in Figure 3.