How to Calculate Subject Wise Pass or Fail with Formula in Excel

This article will be helpful to you if you want to develop a spreadsheet in Excel that declares the students’ results depending on certain conditions. Calculating subject wise pass or fail manually is a very time consuming task. We can do this very quickly in Excel by using some functions. We usually use the logical formulas in Excel for this. Let’s see how to calculate subject wise pass or fail with the formula in Excel.


Download Practice Workbook

Download the practice workbook from here.


3 Quick Methods to Calculate Subject Wise Pass or Fail with Formula in Excel

Here, we will demonstrate 3 easy ways to calculate subject wise pass or fail by using logical formulas in Excel. Besides, we have used some beautiful examples to explain the methods. So without further delay, let’s get started.


1. Apply Excel IF Function to Calculate Subject Wise Pass or Fail

In Excel, the IF function assesses a specific condition. Depending on whether the condition is true or false, it returns one value in each case. Suppose, we have a dataset (B4:D9) in Excel that contains the Subjects and Marks of a student. Here, we need to calculate subject-wise Pass or Fail. In this method, we will use the IF function to do so. The steps are below.

Apply Excel IF Function to Calculate Subject Wise Pass or Fail

Steps:

  • First, we need to select the cell (D5) where we want to show the result.
  • Next, to find the result (Pass/Fail) of the first subject (Maths) type the following formula:
=IF(C5<70, "Fail","Pass")

Apply Excel IF Function to Calculate Subject Wise Pass or Fail

Here, C5 is the Marks of Maths. Here, this formula denotes if Marks is less than 70 then the result will be Fail otherwise Pass.

  • Afterward, click the Enter button to see the result. In this case, we can see that the result is ‘Pass’ as the Marks (82) is greater than 70.

Apply Excel IF Function to Calculate Subject Wise Pass or Fail

  • After dragging the Fill Handle, we can get the result of all the Subjects. Here, we can see the final output in the screenshot below.

Read More: How to Make Automatic Marksheet in Excel (with Easy Steps)


Similar Readings


2. Get Subject Wise Pass or Fail by Combining IF & AND Functions

When you want to verify more than one condition, use the AND function. If any of the requirements are not true, the AND function returns FALSE. On the contrary, it returns TRUE if all conditions are true. Suppose, we have a dataset (B4:E9) in Excel which contains the marks of two subjects of some Students. Here, we will combine the IF & AND functions to calculate Pass/Fail. The steps are below.

Get Subject Wise Pass or Fail by Combining IF & AND Functions

Steps:

  • In the beginning, select the desired cell (E5) to place the result.
  • Secondly, to find if the student has Passed or Failed in the two subjects, just type the formula:
=IF(AND(C5>=35,D5>=35),"Pass","Fail")

Get Subject Wise Pass or Fail by Combining IF & AND Functions

Here, C5 and D5 refer to the marks of Subject 1 and Subject 2 respectively. The formula means if the student gets equal to or greater than 35 marks in both Subjects then he will Pass, otherwise Fail.

  • Now, press Enter and see the result of the first student. In our case, the result is ‘Pass’ as the student got more than 35 marks in both subjects.

  • After dragging the Fill Handle we can find the result (Pass/Fail) of the rest of the Students. The screenshot below shows the final output.

Read More: How to Make a Grade Calculator in Excel (2 Suitable Ways)


3. Use Formula with COUNTIF Function to Find Pass or Fail in Excel

We can also use the COUNTIF function to find Pass or Fail in Excel. Excel’s COUNTIF function counts the number of cells in a range that satisfy a single condition. Cells with dates, integers, and text can all be counted with COUNTIF. Suppose, we have a dataset (B4:F9) in Excel which contains the marks in Maths, Chemistry and Physics of some Students. Here, if a student gets equal to or greater than 70 marks in at least 2 subjects then he will Pass, otherwise, Fail. In this method, we will use the COUNTIF function in Excel to do so. The steps are below.

Use Formula with COUNTIF Function to Find Pass or Fail in Excel

Steps:

  • Firstly, select the cell (F5) to keep the result.
  • Now, to calculate Pass or Fail for the first student, type the formula:
=IF(COUNTIF(C5:E5,">=70")>=2,"Pass","Fail")

Use Formula with COUNTIF Function to Find Pass or Fail in Excel

Here, the range C5:E5 refers to the marks of Maths, Chemistry and Physics successively.

  • Next, press the Enter button to get the result. In our case, the result is ‘Pass’ as it satisfies the requirements.

Use Formula with COUNTIF Function to Find Pass or Fail in Excel

  • Accordingly, drag the Fill Handle and find the result of the rest of the Students. We can see the final output in the screenshot below.

Read More: How to Compute Grades in Excel (3 Suitable Ways)


Conclusion

I hope the above methods will be helpful for you to calculate subject wise pass or fail with the formula in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.


Related Articles

Sagufta Tarannum

Sagufta Tarannum

Hi, I am Sagufta. I have completed my graduation in Civil Engineering from Bangladesh University of Engineering and Technology. I am very much interested about research and innovation in the field of Civil Engineering.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo