Logical and Information Functions
IF and its partners
| Function | Syntax | Example | Result |
|---|---|---|---|
| IF | =IF(logical_test, value_if_true, value_if_false) | =IF(C2>=40,"Pass","Fail") | Pass |
| Nested IF | =IF(C2>=90,"A+",IF(C2>=75,"A",IF(C2>=60,"B",IF(C2>=40,"Pass","Fail")))) | for 78 | A |
| IFS (2019+) | =IFS(C2>=90,"A+",C2>=75,"A",C2>=60,"B",TRUE,"C") | for 78 | A |
| AND | =AND(C2>=60,D2>=3000) | Aamir (78, 2500) | FALSE |
| OR | =OR(C2>=90,D2>=3500) | Eram (88, 3500) | TRUE |
| NOT | =NOT(C2>=60) | for 78 | FALSE |
| XOR | =XOR(TRUE,TRUE) | FALSE (TRUE only if an odd number are TRUE) | |
| SWITCH (2019+) | =SWITCH(2,1,"Mon",2,"Tue","Other") | Tue | |
| IFERROR | =IFERROR(value, value_if_error) | =IFERROR(VLOOKUP("Zoya",A2:D9,3,FALSE),"Not found") | Not found |
| IFNA | =IFNA(value, value_if_na) | Hides only #N/A. |
Combined conditions: =IF(AND(C2>=60,B2="Jammu"),"Merit","") tests two things; AND needs all true, OR needs any one. The IF function allows 64 nested levels.
Information (IS) functions
All return TRUE or FALSE.
| Function | Tests whether the cell is... |
|---|---|
=ISBLANK(A1) | empty |
=ISNUMBER(A1) | a number (a date is a number) |
=ISTEXT(A1) | text |
=ISERROR(A1) | any error |
=ISERR(A1) | any error except #N/A |
=ISNA(A1) | the #N/A error |
=ISEVEN(A1) / =ISODD(A1) | even / odd |
=ISFORMULA(A1) | holding a formula |
=ISLOGICAL(A1) | TRUE or FALSE |
Other: =TYPE(A1) gives 1 for number, 2 text, 4 logical, 16 error. =N(A1) converts to a number. =CELL("address",A1) and =INFO(...) describe the cell and the system.
TRUE counts as 1 and FALSE as 0 in arithmetic, which is why =SUMPRODUCT((B2:B9="Jammu")*(C2:C9>=70)) counts the Jammu students with 70 or more: it returns 2.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.