Schools to the middle-level cadres to carry out the appointment system, one of the evaluation criteria is the staff (more than 300) of more than 30 middle-level cadres to score (called the masses), the masses as a basis for evaluation of the pros and cons. Usually use Excel to input the score one at a, and need to get out of the scores, remove a certain proportion of the highest points, the lowest points, and finally use the function in Excel to find the average value of the remaining points as the final result. The specific implementation method is as follows.
The following is an example of a score of 10 teachers for five middle-level cadres (Figure 1), using the functions provided by Excel to meet the requirements described above. Where the ordinal number is for each ticket (prepare for proofreading), each column is a single fraction of that person's income.
One, the general method uses the average function
Each person's score (that is, each column) is sorted separately, the highest and lowest points are removed by proportion, assuming that the highest and lowest points are removed 10%, that is, remove the dark color data (such as Figure 2), and then use the average function to find the remaining fraction (ie b3:b10) average That is, enter "=average (B3:B10)" In the B12 cell, and return.
But this approach is too cumbersome for schools with more than 300 faculty. In fact, you can also use the Trimmean function one step implementation.
Second, using Trimmean function one-step
Or take the above data as an example, to get the final result, simply enter "=trimmean (b2:b11,0.2)" In the B12 cell, where 0.2 means the highest and lowest points are removed 10%, a total of 20% (that is 0.2), that is, the final average score of XX. Other use the fill handle to drag on it.
How, is this method simpler and quicker? The more data you have, the more you will be able to show its advantages.