Excel average score

The average score is a normal operation in Excel. Using Excel not only allows you to calculate a simple average score, but it is easy to calculate the average score even if you want to remove the highest score and lowest score.

I. Calculate the average score directly

If you want to calculate the average score for the specified data directly, it is obviously the simplest. 1. Assume that the data that requires the average score is in cell B2: B20, then we only need to enter the formula: = average (B2: B20) in cell B21, and then press enter to get the average score.

 

 

 

2. Remove the specified score and calculate the average score

There are two ways to achieve this requirement.

The following example shows how to calculate the average score after removing the highest score and the lowest score.

We can enter the formula = (sum (B2: B20)-large (B2: B20, 1)-small (B2: B20, 1) in cell B22 )) /(count (B2: B20)-2). Press enter to get the result, as shown in 2.

 

 

 

 

Sum (B2: B20) calculates the sum of all data; large (B2: B20, 1) returns the maximum value of B2: B20 cell data; small (B2: B20, 1) return the minimum value in the B2: B20 cell data, while count (B2: B20) returns the number of values in B2: B20. In this way, you can use this formula to calculate the average score after the highest score and the lowest score.

 

If you want to deduct more high and low scores, you only need to increase the number of large and small functions in the above formula, however, you need to replace "1" with "2" or "3" in the formula to obtain the second high (low) score or the third high (low) score. Of course, the number after the count function should also be changed accordingly.

 

This method is simple, but the formula is a little troublesome. In Excel, there can be simpler methods. The trimmean function is used. 3. Enter the formula = trimmean (B2: B20, 0.2) in cell B23 and press enter to obtain the result.

 

 

 

The trimmean function can calculate the average value of the data after the extreme values are intercepted. That is, trimmean removes a certain percentage of data from the header and tail of the dataset, and then calculates the average value. In the above formula, "0.2" indicates the percentage of data to be removed.

 

It should be noted that the trimmean function rounds down the number of excluded data to a multiple of 2 closest to the nearest one. In this example, there are 19 data records and the removal percentage is 0.2, then 20% of 19 is 3.8, And the rounded down is a multiple of 2, A data is removed from the header and tail of the data set in symmetry.

Therefore, the formula for using this function is simple, but you should be careful when selecting the percentage to avoid calculation errors.

　

 

3. Calculate the average score that meets the specified condition

If we require the average score that meets the specified condition, for example, the average score of data below 80 in the above example, the above method is obviously not working. However, in Excel, we naturally have another solution.

 

Enter the following formula in cell B24: = average (if (B2: B20 <90, B2: B20), and press the "Ctrl + Shift + enter" shortcut key, we can see that a pair of braces are added to the outer part of the formula (you cannot manually enter the braces), and the results we need are also displayed, as shown in figure 4.

 

 

 

 

 

 

References: http://www.haoxiai.net/bangongruanjian/exceljiaocheng/112209.html