Excel calculates the maximum and minimum values in a column, which are expressed in red and green, and calculates the average value of the number except the maximum and minimum values There is a group of numbers from A1 to a10. Among these numbers, if it is the largest, red will be displayed, if it is the smallest, green will be displayed, and the average value of A1 to A10 will be displayed on a11. However, the average value is the average value after removing the maximum value and the minimum value. That is: add the other 8 numbers and divide by 8 Is it impossible? Wodaxiaopengyo: please explain the meaning of "= $A1" in "= $A1 = max ($a $1: $a $10)", because I can get the correct result only by inputting "= max ($a $1: $a $10)" In addition, the formula behind you is really wonderful. I use the following formula: obviously not as good as yours =(SUM(A1:A10)-MAX(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-2) Thirdly, after entering the above formula, if you use Ctrl + Shift + enter to end, a synthesis bracket will appear, but the result will not be affected,

Excel calculates the maximum and minimum values in a column, which are expressed in red and green, and calculates the average value of the number except the maximum and minimum values There is a group of numbers from A1 to a10. Among these numbers, if it is the largest, red will be displayed, if it is the smallest, green will be displayed, and the average value of A1 to A10 will be displayed on a11. However, the average value is the average value after removing the maximum value and the minimum value. That is: add the other 8 numbers and divide by 8 Is it impossible? Wodaxiaopengyo: please explain the meaning of "= $A1" in "= $A1 = max ($a $1: $a $10)", because I can get the correct result only by inputting "= max ($a $1: $a $10)" In addition, the formula behind you is really wonderful. I use the following formula: obviously not as good as yours =(SUM(A1:A10)-MAX(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-2) Thirdly, after entering the above formula, if you use Ctrl + Shift + enter to end, a synthesis bracket will appear, but the result will not be affected,

Select A1: A10 menu bar, format, conditional format, formula, input = $A1 = max ($a $1: $a $10) format, font, set red color to add, condition 2, formula, input = $A1 = min ($a $1: $a $10) format, font, set green color to add

How to automatically remove the maximum and minimum values of a group of numbers in Excel, and then calculate the average number

Assuming that the data is in A1: A10, the formula can be used:
=(SUM(A1:A10)-MAX(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-2)

How to mark the maximum and minimum values of a group of numbers in Excel with different colors

Use the conditional format, if you mark the maximum and minimum values in column a
Select column a → format → conditional format →
Condition 1 → cell value → equal to → = max ($a: $a) → format → set background color
Add → condition 2 → cell value → equal to → = min ($a: $a) → format → set background color → confirm