Logical Functions
The result of the logical functions is either True or False.
AND()
Returns true if all arguments of it returns true, otherwise returns false.
Syntax: =AND(cond1,cond2,…)
Each argument of AND function is a condition that can be evaluated as True or False. The condition is an arithmetic expressions consisted comparison operators and operands.
Comparison operators are =, >, <, >=, <=
Example of Condition:
5>7
A1>=B1
OR()
Returns true if any one of its arguments of it returns true, and returns false only all the arguments returns false.
Syntax: =OR(cond1,cond2,…,cond255)
NOT()
Returns true if its argument returns false, and returns false if its argument returns true.
Syntax: =NOT(cond1)
IF()
It takes 3 arguments. Checks the condition(1st argument) and returns 2nd argument if condition is true and returns 3rd argument if condition is false.
Syntax: =IF(condition,Value_if_True,value_if_false)
Examples:
=IF(A1>B1,”A1 is big”,”B1 is big”)
=IF(F4<250,”Fail”,”Pass) //F4 cell has Total marks of a Student
If function can be used with the combination of other logical functions
Example:
=IF(AND(c4>=35,d4>=35,e4>=35),”Pass”,”Fail”) //C4,D4,E4 cell have the marks in different subjects
Nested IF()
One if statement can have another if statement in it. Upto 64 if functions can be nested.
Example: =IF(cond, ValueIfTrue, if(cond,Truevalue,falsevalue))
=IF(cond, if(cond,Truevalue,falsevalue), ValueIfFalse)
Example:
=IF(A1>B1,IF(A1>C1,”A1 is big”,”C1 is big”),IF(B1>C1,”B1 is big”,C1 is big”))
=IF(AND(A1>B1,A1>C1),”A1 is big”,IF(AND(B1>A1,B1>C1),”B1 is big”,”C1 is big”))
Marks Range Grade
0 – <35 Fail
>=35 – <=50 D
>=51 – <=60 C
>=61 – <=75 B
>=76 – <=90 A
>=91 – <=100 A+
=IF(F4<35,”FAIL”,IF(AND(F4>=35,F4<=50),”D”,IF(AND(F4>=51,F4<=60),”C”,IF(AND(F4>=61,F4<=75),”B”,IF(AND(F4>=76,F<=90),”A”,IF(AND(F4>=91,F4<=100),”A+”,”INVALID PERCENTAGE”))))))
SUMIF()
adds the cells specified by a given criteria. This functions belongs to Math&Trig functions category.
Syntax: SUMIF(range,criteria,[sum_range])
Range is the range of cells that are to be checked for criteria
Criteria is the condition
Sum-range is the actual cells to add if the criteria satisfied. If it has been omitted, the values in the range will be used.
Example:
|
Property Value |
Commission |
|
100000 |
7000 |
|
200000 |
14000 |
|
300000 |
21000 |
|
400000 |
28000 |
Sum of the commission for property values over 160000
=SUMIF(a2:a5,”>160000”,b2:b5)
Sum of property values over 160000
=SUMIF(A2:A5,”>160000”)
COUNTIF()
returns the number of cells matching the specified criteria. This is also belongs to Math & Trig Functions Category.
Syntax: =COUNTIF(range,criteria)
RANK()
returns the size of value relative to other values in the list.
Syntax: =RANK(number,ref,order)
where number is the values to be compared
ref is the range of values in which number is a member
order can have values either ‘0’ (descending) or ‘1’ (ascending)
Unique ranking when having duplicate values (if the data is existed from A1 to A7)
=RANK(A1,$A$1:$A$7,0)+COUNTIF($A$1:A1,A1)-1