Blog

Logical Function in Excel (And,Or,If,IFERROR,and IFNA)

Logical Function in Excel  (And,Or,If,IFERROR,and IFNA)


 

        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")

 




Click Here

Video Link: Watch Video

Phone : +91 8606648045
Email : info@excelskillcorner.com
Excel Skill Corner
Kerala, India.