Logical functions used in the excel for checking various scenarios where our results is “True†or “False†based on the conditions, here we discussed widely used functions in our routine job in excel
And,or,If,Iferror and IfNa
And Formula
Why “And†function in Excel File , Some case we need to check whether the all the conditions are passed or not.
Eg:
|
CONDITION-1 |
CONDITION-2 |
|
100 |
200 |
|
50 |
75 |
|
58 |
65 |
And function will give “True†only if the two conditions are met otherwise it will give “Falseâ€
|
RESULT |
FORMULA SYNTAX |
|
TRUE |
=AND(B12>50,C12<300) |
|
FALSE |
=AND(B13>60,C13<80) |
Or Formula
It checks any one of the conditions is Ok then it will return True and otherwise it will bring False
|
CONDT-1 |
CONDIT-2 |
RESULT |
FORMULA SYNTAX |
|
100 |
200 |
TRUE |
=OR(B12>50,C12<300) |
|
50 |
75 |
FALSE |
=OR(B13>60,C13>80) |
|
58 |
65 |
TRUE |
=OR(B14<60,C14<55) |
IF Formula
This function tests the conditions which are written and return the value “True†if it met the conditions otherwise it will return “False†. And also we can also write the text or Formula instead of True of False
|
TARGET |
SALES |
RESULT |
FORMULA SYNTAX |
|
100 |
80 |
ACHIEVED |
=IF(C12>B12,"ACHIEVED","NOTACHIEVED") |
|
110 |
115 |
NOT ACHIEVED |
=IF(C13>B13,"ACHIEVED","NOTACHIEVED") |
|
115 |
120 |
NOT ACHIEVED |
=IF(C14>B14,"ACHIEVED","NOTACHIEVED") |
IF Error Formula
This function used to replace the errors #DIV/0!, #NAME?, #REF!, #VALUE! ,#N/A etc..
into blanks or meaningful text which is useful in report creation and if write Ҡinstead of text it will return blank .
|
Cell to test |
Result |
FORMULA SYNTAX |
|
|
5 |
5 |
=IFERROR(B14,"MISTAKE") |
|
|
#DIV/0! |
MISTAKE |
=IFERROR(B15,"MISTAKE") |
|
|
#NAME? |
MISTAKE |
=IFERROR(B16,"MISTAKE") |
|
|
#REF! |
MISTAKE |
=IFERROR(B17,"MISTAKE") |
|
|
#VALUE! |
MISTAKE |
=IFERROR(B18,"MISTAKE") |
|
|
#REF! |
MISTAKE |
=IFERROR(B19,"MISTAKE") |
|
|
#N/A |
MISTAKE |
=IFERROR(B20,"MISTAKE") |
IFNA Formula
IFNA function used for checking the results of the formula which brings #N/A and we need to get the text or Blank Space by writing Ҡwhich is widely used in the Vlookup Formula
|
Cell to test |
Result |
FORMULA SYNTAX |
|
#N/A |
NOT AVAILIABLE |
=IFNA(B14,"NOT AVAILIABLE") |
|
15 |
15 |
=IFNA(B15,"NOT AVAILIABLE") |
Video Link: Watch Video