Use Excel to help me with my student performance statistics

In the teacher's daily work, the student's achievement carries on the statistical analysis management is a very important also very troublesome work, if can take advantage of Excel powerful data processing function, can let each teacher quickly complete to the student's achievement each analytic statistic work. Here are some tips on how to use Excel to manage your students ' grades.

Quick change of students ' test grade

Sometimes, you will meet the test scores of the students to the actual test scores to the corresponding grade of the situation, such as the test results in 90 points above the results converted into a "A +" form, 85-89 points of the result converted into "A" form .... In general, the IF () function is used in an Excel table to design a formula for conversion, so that the formula that is designed becomes complex, and if the grade type of conversion exceeds the maximum nesting level of the if () function, the if () function is powerless. At this point we can use the following methods to simplify the operation.

1, open the Student Performance worksheet (Figure 1).

Figure 1

2, in the G2 to the I12 cell Input Examination score section and the examination grade comparison table.

3, in the D3 cell input formula "=index (I$3:i$12,match (1, (c3>=g$3:g$12) * (c3<=h$3:h$12), 0))", because the formula is an array of formulas, after entering the above content, you must also press the Ctrl +shift+enter "key, add an array formula label to the above formula, namely curly braces" {} ". The function of this formula is to automatically convert the result to the corresponding grade in D3 cell according to the student's score in the C3 cell.

4. Move the cursor to cell D3, drag the fill handle down to the D12 cell, and quickly copy the formula so that you can quickly complete the conversion of your student's grade (Figure 2).

Figure 2

5, can also according to their own preferences, the G2 to I12 cell range set to "hidden" to make the table more beautiful.