Logical function
- IF( )Checks whether a condition is met, and returns one value if true, and a different value if false
- AND() Tests a number of user-defined conditions and returns TRUE if all of the conditions evaluate to TRUE otherwise it returns FALSE.
- OR() Tests a number of user-defined conditions and returns TRUE if any one of the conditions evaluate to TRUE otherwise it returns FALSE.
- NOT() returns TRUE value when the statements are FALSE otherwise return FALSE.
Ex: Calculate Grade of the student by using “if” function.
- First create the below data structure.
- Calculate Total by typing =sum(C2:E2) in Cell F2.
- Calculate Percentage of marks by typing =F2/G2*100 in Cell H2.
- Calculate grade based on the condition if percentage of marks >=30 the student will get Pass otherwise Fail.
- Type the formula in I2 Cell =IF(H2>=30,”Pass”,”Fail”)
Ex: Calculate Grade of the student by using “if & and” function as below grade formats:
If Percentage of marks >=70 and all individual subject marks>=50 then student will get Grade “A”.
If Percentage of marks >=60 and all individual subject marks>=50 then student will get Grade “B”.
If Percentage of marks >=50 and all individual subject marks>=50 then student will get Grade “C”.
Otherwise “F” Grade.
- First create the above data structure.
- Calculate Total by typing =sum(C2:E2) in Cell F2.
- Calculate Percentage of marks by typing =F2/G2*100 in Cell H2.
- Calculate grade type the formula in I2 Cell
=IF(AND(C2>=50,D2>=50,E2>=50,H2>=70),"A", IF(AND(C2>=50,D2>=50,E2>=50,H2>=60),"B", IF(AND(C2>=50,D2>=50,E2>=50,H2>=50),"C","F")))