using IF function in excel

The IF function in Excel take three parameters. here is the general form of the function.
=IF(logical_test, value_if_true, value_if_false)
Now we are going to take an example to solve the function along with other functions if necessary

Here we have taken some students marks and you can see their average. If I want to specify the students who passed or who failed by considering their average we can set if function like this

=IF(E4>=50,"PASS","FAIL"). here we have calculated the Pass/Fail Status for Abdullah then by auto fill we calculate for others.

But here we did not get the expecting result for row no. 5. In row 5 you can see Sheuli got 45 from Math and 34 from ICT and her average is 52 and her status is Pass but her status should be Fail as she failed from 2 subjects. We have to find a solution  for this problem.
Let set the condition again. It was only the average either 50 or more then show pass, now we are giving the condition for checking the particular subjects and a nested if function.

=IF(OR(B2<50,C2<50,D2<50),"FAIL",IF(E2>=50,"PASS","FAIL"))

by using another if function inside(nested if) and inside the logical test by adding OR function to check all the subjects, we have solved the problem for row 5 but you see another problem arisen here for row 3. Ahmed is not taking bangla and his average is pretty good but our calculation shows his status as fail. so we have change it again. 

No, let analyze on Ahmed's result and why our functions show him fail. here we used OR for checking the particular subjects, and while checking his numbers Math is OK, ICT is OK but bangla is blank and OR function take it as 0 which is less than 50, that's why we got his status as Fail.  

So we have to check the cells are occupied or not along with the cell value is less than 50, for this we can use the following function

=IF(OR(AND(B2<50,COUNT(B2)=1),AND(C2<50,COUNT(C2)=1),AND(D2<50,COUNT(D2)=1)),"FAIL",IF(E2>=50,"PASS","FAIL"))

here we used AND & COUNT function. we are checking the cell value is less than 50 & this cell has a value or not, this time we will get the correct solution. 

Finally we got the correct solution. After the condition of the logical test set up properly, we can play with our function as we want. now we are going to give grades for the students based on their average. we can solve this part by using nested if or any other lookup functions. 

let give grades for this table by using nested IF function.

=IF(OR(AND(B2<50,COUNT(B2)=1),AND(C2<50,COUNT(C2)=1),AND(D2<50,COUNT(D2)=1)),"F",IF(E2>89,"A",IF(E2>79,"B",IF(E2>69,"C",IF(E2>59,"D",IF(E2>49,"E","F"))))))

The criteria for grading we have followed here is 

Range	Grade
>89	A
>79	B
>69	C
>59	D
>49	E
Otherwise	F

Later on we will solve this problem with look up functions. 

stay fine :-)