If you are a teacher at any of the education levels, it will surely be very useful for you to carry out statistical studies that allow you to know the development of your students; Among the most common data you have, for example , the calculation of the average of students in Excel or of the section, and for this you can use this program.
Although it is not the only thing you can use, since this tool will allow you to do many other calculations to evaluate the performance of your students. In this post we will show you how to calculate in Excel the percentage of approved and failed correctly .
Index( )
- How to use the functions that allow knowing the number of approved and failed?
- IF function in simple format
- Formula COUNTIF (range:criteria)
- How to calculate the percentage of approved and failed students?
- How to apply a conditional format to easily identify the data of your students?
How to use the functions that allow knowing the number of approved and failed?
If you are from the old school, you will already know how to obtain the calculation as the average grade of the students and the section, as well as other variables manually; however, by using Excel, all calculations are simplified, making the process easier.
Allowing you to keep tables for electronic records of your students’ grades. In addition, you will have other functions available to know the number of approved and failed students , such as:
IF function in simple format
The “IF” function is very useful for many applications, to make use of this function you need to know the structure or syntax that it handles to place the correct argument. In principle, the “IF” function is a logical expression used to check the truth or falsity of a situation. For example, to evaluate if a student passed or failed a certain course, you can make use of this function.
Suppose the minimum passing grade for a student in a “Mathematics” course is 60 out of 100, then we can make use of this function to assess whether the student’s grade is pass or fail .
The “SI” function requires the following arguments, “Logic_Test” which is the cell to be evaluated next to the evaluation criterion, “Valor_Si_True” in this space you can place text to indicate the situation in true condition.
On the other hand there is “Valor_SI_Falso” which is where the text or function corresponding to the result evaluated as false in the function will be placed. Assuming that you have a student who qualified with 62 out of 100 points, then the student must have a “Passed” condition, for this you must place in the evaluation cell “=YES(C3>=60;”Approved”;”Failed”) “ .
With the previous expression you are telling Excel to evaluate the grade located in cell “C3” and if it is greater than or equal to “60”, then put the label “Approved”, if the grade is less than 60, then the label will be “Failed”. The result of the evaluation for the student “Pedro Pérez” will be “Approved”.
Consider that the “SI” function in a simple format can evaluate only the condition of a student, if you need to know the total number of approved and failed students, you must use the “Count.SI” function that we explain below.
Formula COUNTIF (range:criteria)
This function will allow you to count a number of elements that meet a certain condition, the structure of this “Count.If” function will ask you to enter the “evaluation range” which is all the cells where you want to evaluate the possible existence of a condition . In addition, you must enter the criterion, which is the condition that you will be evaluating.
For example, if you have a table with the results of the students’ grades and their respective individual statuses, you have to evaluate the total number of approved students. To do this, place the following in the evaluation cell: “=Count.Si(D3:D10 ;”Approved”)” , you’re telling Excel to look at the range of cells D3 through D10 and count the number of “Approved” labels that exist.
To evaluate the total number of failures, put the same expression, but change the label “Approved” for “Failed”, with this you will be able to evaluate both labels or conditions that the students may have.
How to calculate the percentage of approved and failed students?
Once you have obtained the total number of approved and failed students of the course you are teaching, you can obtain the percentage of students who passed the subject and the percentage of them who will have to repeat the course. To do this, you must know the total number of students in the course and then apply the rule of three to obtain said percentages.
In this first case, if you have 8 students in total, you know that those 8 students represent 100%, so the number of students who pass corresponds to the percentage of students who pass. The criterion of the rule of three is applied and you obtain the “percentage of approval = (No. Approved * 100%)/8” , assuming that there are 4 approved students, you will have a percentage of 50% approved.
If the list of students is very large, you can use the “Count” function and choose the range of cells where the names of the students appear , the formula will return the total number of students. Then you can apply the rule of three knowing the rest of the elements (failed, approved) and you will have your percentages.
How to apply a conditional format to easily identify the data of your students?
Another way to evaluate the results or data of the students is to use the conditional format offered by the Excel tool , there are many modes of operation for the conditional format.
However, this time we will be addressing how to change the color of the cells according to the students’ grades. To do this you must navigate in the top menu and follow these steps:
- Find “Start,” then proceed to choose “Conditional Formatting.”
- Choose the cells to which you want to apply conditional formatting, and click on “Manage Rules”that is displayed when you click on “Conditional Formatting”.
- Click on the “New Rule” button and in the pop-up window select “Apply formatting only to cells that contain”.
- Choose the second field that contains the list and navigate the list to “Greater than or equal to”, then put the value in the third field, in this case it is “60”.
- Click on the “Format” button and navigate to padding in the top menu.
- Choose the color that you consider appropriate for ratings greater than or equal to “60”, in this case we chose dark green.
- Click OK, then click OK again and you will have the first rule created.
- Now repeat the process and create a new rule, but instead of choosing “Greater than or equal to”, choose “Less than” in the list of the second field, similarly put 60 in the third field, and repeat the formatting process for colour. We choose red for ratings less than “60”.
- Finally, click on the apply button and you will see the changes that the cells undergo based on the established criteria.
As you may have noticed, the process to establish labels that allow you to identify approved or failed students in a certain course that you are teaching is easy to execute in Excel, in addition, you can learn how to make graphs in Excel in a simple way to show the results that were obtained when end of your course.