Excel asks for the highest and lowest points according to the class number and total score

How can Excel calculate the highest and lowest points according to the class number and total score in the original score?

Now, let's take a look at the table first.

In the table above, the selected part is the original score, a total of six columns, including class number, name and three subjects and scores.

Now, what we have to do is based on the part of the blue coil, give the class number, and then, to calculate the class of students in the total score of the highest and lowest points.

method is not difficult, using the array formula and Max, min with the IF function, you can get results.

First, according to the class number to calculate the highest score

The following figure. First select B12, in the FX formula input box, first enter the formula

=max (IF (b12= $A $: $A $, $F $: $F $))

Then, don't worry, then, press the Ctrl+shift+enter key combination on your keyboard.

In this way, we get the array formula as shown above.

Second, according to the class number to calculate the lowest points

With the above foundation, it is easy to calculate the lowest score according to the class number.

Computer Tutorials

Enter the formula first

=min (IF (b12= $A $: $A $, $F $: $F $))

Then press the Ctrl+shift+enter key combination, you can draw the result.

Third, knowledge expansion

① a high score and low score based on the class number across worksheets

If the name of table one is Sheet1, the name of table two is Sheet2.

So, how do I get the highest score by table two, based on the class number of table one, and fill it to the top of table one?

First select the B2 cell in table one, then the code in the FX function entry box: =max (IF a2=sheet2! $A $: $A $100,sheet2! $F $: $F $)

After entering the code, do not be impatient, this is not equivalent to the previous operation, input good, press Ctrl+shift+enter key combination to confirm, to ensure that the input code is an array of functions, rather than the use of ordinary functions!

After the combination of the above confirmation, the two sides of the code automatically add braces, note oh, curly braces are automatically joined, rather than manually tapping keyboard input!

② Function Formula Description

a2=sheet2! $A $: $F $ represents the value of the A2 cell, which is the class name of Class 1, whose comparison range is the A2 cell of table two to the A100 cell, where the scope can be modified according to need, generally, the data range is larger than the predetermined range!

sheet2! $F $: $F $ represents the data area of the total score of the test table, that is, the data of the F2 cell to the F100 cell, where the range can be modified according to need, generally, the data range is larger than the predetermined range!

The above two codes, in the case of If, refer to the class name in the table two set data region according to the class names of the specific cells in the table, while the results of other classes are not used as reference objects!

The Max function of the periphery is to seek the highest score of the Class!

③ several hypotheses

Suppose one: If we are looking for the lowest score, we can change the Max function to a mix function!

such as: =mix (if (a2=sheet2! $A $: $A $100,sheet2! $F $: $F $))

If we are looking for a class that is not the highest score, but the highest score for all classes, then simply remove the IF function name and its corresponding braces!

such as: = MAX (a2=sheet2! $A $: $A $100,sheet2! $F $: $F $)