Excel function to find the total score and ranking!

The prompt lit up. What's the problem?

a(sum(d2:h2)-max(d2:h2)-min(d2:h2))/3

brank(i2,i$2:i$11,0)

Both formulas are padded to line 11.

For example: a

BC1 name. Score.

Ranking: 2 cards and three.

John doe. Wang II.

.To calculate the position of the value of B2 in cells B2:B10000, enter the formula =rank(B2,$b$2:) in C2

b$10000). There are also many ways to do this, by typing :=countif($b$2:) in c2

b$10000,">"&b2) +1 or.

sumproduct(($b$2:$b$10000>b2)*1)+1 or.

sum(--b$2:$b$10000>b2))+1 This is the array formula, to press "shift+ctrl+enter.""key. Or.

count($b$2:$b$10000)-frequency($b$2:$b$10000,b2)+1

The final score formula: i2=(sum(d2:h2)-max(d2:h2)-min(d2:h2)) 3

Pull down and you're good to go.

The ranking formula: J2=Rank(J3,$j$3:$j$11) is also pulled down.

1. Enter the following formula in cell i2, and then fill the formula down.

round((sum(d2:h2)-max(d2:h2)-min(d2:h2))/3,1)

The formula says: sum d2:h2, subtract the maximum and minimum values, divide the result by 3, and keep 1 decimal place.

2. Enter the following formula in cell J2, and then fill the formula down.

rank(i2,i:i)

The formula indicates that the rank of i2 in column i is returned.

Use the sum function to sum and then sort with the rank function.

The specific steps are as follows:

1. Open Excel and click on the cell you want to calculate the total score. (The figure below is only demonstration data, and the rankings are ranked according to the total scores of individual Chinese and mathematics).

2. Enter the formula =sum(b2:c2) in cell d2.

3. Drop down the D2 cell to fill the D4 cell.

4. Click cell E2.

5. Enter the formula =rank(d2,$d$2:$d$4) in cell E2.

6. Drop down the E2 cell and fill it to the E4 cell.

In Excel**, the function formula rank is the formula for ranking the total score of the grade. The specific operation steps are as follows: 1. In the cell where you want to count the total score ranking, enter the function "=rank(value, quote wide Wang Qing, ranking method)", and after the enter key, you can see the ranking.

2. If you want to auto-fill the ranking, you need to add the absolute reference symbol "$" before each column mark in the mausoleum place cited by the formula, and then auto-fill it.

1. First of all, we use the computer to open the excel software and create a new **, as shown in the figure below.

2. Next, after we enter the data in **, click on any cell in the column (row) of the ranking, as shown in the figure below.

3. At this time, we enter "=rank(e2,e$2:e$5" in the cell, and then click ** anywhere, as shown in the figure below.

4. Finally, the total score ranking of all people comes out, as shown in the figure below.

The total score algorithm of excel functions: calculate one by one, function formula method.

1. Calculate them one by one

First of all, we click to open the excel on our computer and enter the data we need to calculate the overall score. On the right side is the overall score. For a simple summary of the total score, we can calculate them one by one, select the data that needs to be summed, and click Sum in the upper right corner.

When a cross appears below the first overall score, pull down to get the entire total score.

2. Function formula method:

According to the example in this article, enter =sum(e3:h3), which is to find the total score, take the total score of all subjects of the student, enter it in turn or pull down the flash cross arrow, you can get the total score. But for projects with a lot of data, both approaches are possible.

Some friends may feel that the dust function is not easy to remember, so they can roll smooth and bright to choose the sum selection.

Common excel function formulas:

1. Digital processing:

1. Take the absolute value = abs (number).

2. Round=int (number).

3. Rounding = round (number, number of decimal places).

2. Judgment formula:

1. Display the error value generated by the formula as an empty formula: =iferror(a2 b2,).

Note: If it is an error value, it will be displayed as empty, otherwise it will be displayed normally.

2. If multi-condition judgment of the return value.

Formula: =if(and(a2<500,b2=undue), repayment).

Note: If two conditions are valid at the same time, use the AND function, and if any one is true, use the OR function.

3. Statistical formula:

1. Count the duplicate content of two **.

Formula: =countif(sheet15!a:a,a2)。

Note: If the return value is greater than 0, it exists in another table, and 0 does not exist.

2. Count the total number of non-duplicates:

Formula: =SumProduct(1 CountIf(A2:A8,A2:A8)).

Note: Use countif to count the number of occurrences of each person, divide by 1 to turn the number of occurrences into the denominator, and then add them up.

sum and rank functions, the steps are as follows:

Step 1: As shown below. A ranking of the total score for the entire class is required.

Step 2: First, calculate the student's total score and write the formula =sum(b2:e2) in F2 <>

Step 3: Then calculate the ranking. The rank function is used for ranking. Write the formula =rank(f2,f$2:f$16) in g2 The number one ranking here is definitely not right, don't worry, keep reading.

Step 4: Mouse select the F2 and G2 cells. Drop it in the lower right corner of the G2 cell, and when it becomes +, press and hold the left button to pull down to the bottom row of data. The total score and ranking data are all out.

<> Step 5: In order to see the rankings more clearly. Sort the rankings. Select G2 to G16, click Data, Sort (Figure 1), Expand Selected Area (Figure 2), Rank (Figure 3).

<> the sixth step: the main thing is to do a good job of ranking, look at the picture (Gangwon Province) students are the first, and they are ranked at the top.

How to find the ranking in ExcelStep 1: Find the excel you want in the folder, double-click to open excel, if not, create a new excel job**, and then process some data by yourself.

Figure 1 of the method of finding the ranking in ExcelStep 2: Stop the cursor in the blank space below 'Rank' and find the 'Formula' option in the menu bar at the top. Click on it, then find and click on the 'Insert Function' option on the far left.

Figure 2 of the method of finding the ranking in ExcelStep 3: In the pop-up box, enter rank in the first box, click 'Go', and select the rank function in the box below. Finally, click OK.

Figure 3 of the method of finding the ranking in ExcelStep 4: Select the sum of the first row in the first number input box. Select the sum of the columns in the second ref input box, that is, click and hold the first one, pull all the way down, and select all the data in the column.

Enter 0 in the third input box.

Figure 4 of the method of finding the ranking in Excel

Figure 5 of the method of finding the ranking in Excel

Excel often needs to use the skill of calculating rankings, how to calculate rankings? The following is a tutorial I brought about calculating rankings in excel, I hope you will be inspired after reading it!

Excel Ranking Tutorial:

Step 1: Calculate the rank in the folder.

Find the excel you want, double-click to open excel, if not, then create a new excel job**, and then do some data to process by yourself.

Step 2: Stop the cursor in the blank space below the 'Ranking' and find the 'Formula' option in the menu bar at the top. Click on it, then find it on the far left and click on the 'Insert Function' option.

Step 3: In the pop-up box, enter rank in the first box, click 'Go', and select the rank function in the box below. Finally, click OK.

Step 4: Select the sum of the first row in the first number input box. Select the sum of the columns in the second ref input box, that is, click and hold the first one, pull all the way down, and select all the data in the column. Enter 0 in the third input box.

Step 5: Then lock the footyards of the rows and columns in the blank space as shown in the sock puzzle diagram, that is, add a dollar sign in the previous position of the number. Then click on the hook symbol in front of it.

1. First of all, open an excel**.

2. Then select all **, don't select the title.

3. Then click [Data] in the upper toolbar, and then click [Sort].

4. After entering the sorting, select [Main Keyword] as [Total Score], and then set [Order] to [Descending].

5. Then the total score sorting will come out, and then add a column of [Ranking] after the total score. Prudence.

6. Widen the grip and then write below the ranking.

7. Then drag the sedan chair all the way down, drag it to the end, and you will see the ranking come out.