Shulou(Shulou.com)11/24 Report--

The original title: "Don't just know Average anymore!" Find the average, this fairy function many people do not know! "

Hello everyone, I am digging deep into the function of Xiao Shuang ~

In some competitions, sometimes in order to avoid the occurrence of extreme values, the average results are adversely affected.

We usually calculate the average after removing one of the highest and one lowest scores from multiple judges.

So, let's take "Wulin Singer" as an example to explain how to get the average score after removing the highest score and the lowest score, that is, the final score, how to achieve it in Excel.

Here we introduce two methods:

❶ traditional mathematical logic method.

❷ TRIMMEAN function method.

The basic operation of traditional mathematical logic method ▋ is to enter the following formula into J3 cell and fill it down.

= (SUM (C3:I3)-MAX (C3:I3)-MIN (C3:I3)) / (COUNT (C3:I3)-2)

Analysis of ▋ function Formula SUM function is a summation function, MAX function is a maximum function, MIN function is a minimum function, and COUNT function is a function that counts the number of regions.

According to mathematical logic, we can know:

The average value after excluding data = the sum of the total score after excluding one highest score and one lowest score / the total number after excluding the highest and lowest scores

So, in the formula,

= (SUM (C3:I3)-MAX (C3:I3)-MIN (C3:I3)) / (COUNT (C3:I3)-2) ❶ (SUM (C3:I3)-MAX (C3:I3)-MIN (C3:I3)), is the molecular part, in order to calculate the sum of the highest and lowest scores

❷ (COUNT (C3:I3)-2), which is the denominator part, is used to calculate the total number after the highest and lowest scores are removed.

In this way, according to the operational logic, we can directly write out the formula and calculate the result.

Of course, in addition to the conventional practice, in fact, there is a function in the Excel function that is used to average after eliminating the maximum and minimum values in pairs.

This function is the TRIMMEAN function.

PS: pairs refer to the maximum and minimum values of N pairs

We can use the TRIMMEAN function to prune the average, and then let's take a look at it.

The basic operation of TRIMMEAN function method ▋ enter the following formula into J3 cell and fill it down.

= TRIMMEAN (C3purl I3Magne2max count (C3:I3))

The ▋ function syntax parses the TRIMMEAN function to return the internal average of the dataset.

That is, a certain percentage of data points are removed from the head and tail of the data set, and then the average is calculated.

The syntax is as follows:

= TRIMMEAN (array,percent)

= TRIMMEAN (array, percentage)

❶ array: an array or region of values that needs to be sorted out and averaged, such as C3:I3.

❷ percentage: the score that excludes data points from the calculation.

General formula for percentage parameter:

Percent = total number of strips / total number of data.

Such as 2 / COUNT (C3:I3).

Note:

The percentage parameter area needs to be in [0re1], and if it is a multiple of 2, not a multiple of 2, it will be automatically rounded down to the multiple closest to 2.

For example:

In a set of 10 data points, we want to remove two data points: one from the head and one from the tail. The second parameter should be 2mp 10, 1max 5, 0.2.

So the function formula in the case is written as follows:

= TRIMMEAN (C3Glue I3Magne2 count (C3:I3)) in order to facilitate your understanding, I have specially sorted out a general formula routine for you. When you encounter this problem, you can apply the formula directly!

▋ General Formula calculates the general formula of the mean excluding n maximum and n minimum values:

= TRIMMEAN data region, 2*n/COUNT (data region)-- n is n pairs of maximum and minimum ▋ general formula parsing = TRIMMEAN data region, 2*n/COUNT (data region) if we need to calculate the total score after excluding a set of maximum and minimum values, then the corresponding percentage is 2 percent 1 / COUNT (data region)

For example, the data area is as follows, and the formula is directly applied:

Function formula:

= TRIMMEAN (B2 F2jiggle 2mm 1max count (B2:F2))

A total of 5 numbers, that is, the COUNT (data region) is 5.

Correspondingly, the percentage parameter is 2 impulse 5, or 0.4, which is equivalent to removing 2 out of 5 digits.

What if we want to eliminate the two groups of maximum and minimum values and calculate the score? What should the percentage parameter be?

The percentage parameter should be 2, 2, 2, 5, 4, 5, 0. 8 (n is 2, that is, 2 groups), and so on.

In this case, the data area is C3:I3, because we remove a pair of maximum and minimum values, so n is 1, we directly apply the formula, done!

The TRIMMEAN function is easy to use, but it can only be eliminated in pairs. in reality, the highest and lowest scores are removed asymmetrically.

For example, when bidding, we often see the removal of two highest points and one lowest score, so as to remove asymmetrically and calculate the average value.

At this point, the TRIMMEAN function cannot be done, but it still needs to be done by traditional mathematical logic, such as:

= (SUM (summation region)-LARGE (summation region, 1)-LARGE (summation region, 2)-SMALL (summation region, 1) / (COUNT (summation region)-3) well, here, we are done with the two methods! Next, let's briefly summarize ~

To sum up, this article introduces two methods of averaging by removing the highest and lowest values:

❶ 's traditional mathematical logic practice: although the formula is lengthy, it can flexibly eliminate unpaired data.

❷ uses the TRIMMEAN function: it can only be removed in pairs.

A general formula for using the TRIMMEAN function:

= TRIMMEAN data area, 2*n/COUNT (data area)) this article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Audit: Madge Goose, Editor: Tian Guoguo, Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.