Office hacks: operators mixed with quick check edge raw reach

When dealing with student performance, students whose total score is within a few points of the score line are often referred to as marginal students for this exam. How can you see how the marginal students from the last exam have achieved their scores in this exam? You can do it quickly by using logical operators and logical operators.

1. Determining score achievement

Take the language subject as an example (Figure 1). Assume that the science line 110 points, the arts line 139 points, in cell L1 enter the column heading “language up to”, in cell L2 enter the following formula and fill down to the last line.

= ($A2=”Science”)*(E2>=110)+($A2=”Arts”)*(E2>=139)

where the logical operator “*” means with operation, which requires both to hold; “+” means or operation, which requires only one to hold; “=” determines whether they are equal. “> =” to determine the size of the value, the same below. The formula is used to determine the language score of science is greater than or equal to 110 or arts is greater than or equal to 139.

1813A-CJDC-1

Similar formula for other subjects.

Math achievement formula: =($A2=”Science”)*(F2>=145)+($A2=”Arts”)*(F2>=108)

English achievement formula: =($A2=”Science”)*(G2>=73)+($A2=”Arts”)*(G2>=75)

Total score attainment formula: =($A2=”Science”)*(H2>=327)+($A2=”Arts”)*(H2>=320)

2. Judgment of grade achievement

Select the grade requirements up to B, to “select a” for example, in cell P1, enter the column heading “a up to”, in cell P2 enter the following formula and fill down to the last line.

= (I2 = “A”) + (I2 = “B”)

The role of the formula is to determine whether to choose a grade of A or B.

“Choose two” similar, the formula: = (J2 = “A”) + (J2 = “B”)

1813A-CJDC-2

3. Calculate the difference of the last test score line

Mix with the arithmetic operators to calculate the last test score line difference. Assuming that the last test score line 322 science, 315 arts, enter the column heading “line difference” in cell R1, enter the following formula in cell R2 and fill down to the last line.

= ($A2=”Science”)*(K2-322)+($A2=”Arts”)*(K2-315)

where the arithmetic operator “-” calculates the difference between the total score and the score line; “*” means that the two are multiplied to find the product. The role of the formula is to calculate the total score of science or arts and the difference between the score line.

1813A-CJDC-3

4. Check the attainment of marginal students in each class

Sort by primary keyword class ascending order, secondary keyword line difference descending order, and then filter the line difference, and select the students who are greater than or equal to -5 and less than or equal to 4 for each subject reached as shown in the figure (Figure 4).

1813A-CJDC-4

Leave a Comment