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(1^st argument) and returns 2^nd argument if condition is true and returns 3^rd 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:

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