How to Use AND, OR Functions with IF Function in MS Excel

 

We have defined to prepare results of students through IF Function in Microsoft Excel. During this function we had results of students getting overall marks even any student was failing in any one subject. But we have to pass the students getting pass marks in every subject. To get results of students passing in every subject, we shall use “AND FUNCTION” in Microsoft Excel

Definition of AND Function

AND Function is logical function or formula which gives conditions for describing true or false. AND Function is supportive to other functions of MS Excel. We can use "AND FUNCTION" with IF FUNCTION. Place cursor infront of cell in result column of 1^st student and type formula as under:-=IF(AND (D3>=50, E3>=50, F3>=50,G3>=50, H3>=50), "Pass", "Fail")

We have given condition to IF Function through AND Function to pass only those students who have gotten 50 marks or above in each subject.

Suppose we have a condition that if students getting passing marks in any subject should be passed then we will use another conditional function with IF Function. This function is called “OR FUNCTION” in Microsoft Excel.

Like AND Function, "OR Function" is logical function giving conditions for describing true or false. OR Function is also supportive to the other functions. Subsequently, we can use OR Function with IF Function.  We shall insert following formula in front of first student in result column, which will give our desired result.

 =IF (OR (D3>=50, E3>=50,F3>=50,G3>=50,H3>=50), "Pass", "Fail")

We can use IF Function for many other purposes. Suppose we have to give bonus to teachers who achieved appreciable progress during their work. Since we have decided to give the bonus to teachers, whose 60% or above students have passed. But we shall give 5% bonus of salary to those teachers, whose 80% and above students have passed and 2% to those teachers whose students have passed less than 80%. For this condition we shall apply IF Function as under:-

	A	B	C	D	E	F	G
1	Name	Designation	Total Salary	Total Students	Passed	Pass %	Bonus & Formula
2	Asad	Teacher	6300	24	20	83.3	315 =IF (G2>=80,D2*5%, D2*2%)
3	Ghani	Teacher	6300	28	22	78.6	126 =IF (G3>=80,D3*5%, D2*3%)