Home

Logical Operations

 

Logical Operators

Introduction

A logical operator is one used to perform a comparison between two values and produce a result of true or false (there is no middle result: something is not half true or half false or "Don't Know"; either it is true or it is false).

 

Equality =

The equality operator is used to compare two values for similarity. The syntax of this operation is:

Value1 = Value2

If Value1 and Value2 hold the same value, then the comparison produces a TRUE result. If they hold different values, the comparison renders a FALSE value:

Once the comparison has been performed, it processes a Boolean value you can use as you see fit.

 

Inequality <>

To find out if two values are different, you can use the inequality operator which is represented by <>. Its syntax is:

Value1 <> Value2

This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a TRUE result. If they hold the same value, the comparison produces FALSE

The equality (=) and the inequality (<>) operators are opposite each other.

Less Than <

The "Less Than" operator uses the following syntax:

Value1 < Value2

If Value1 holds a value that is lower than that of Value2, the comparison produces TRUE. If Value1 holds a value that is greater than or similar to that of Value2, the comparison renders FALSE

 

Less Than Or Equal <=

When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal to" operator. It is represented by <=. Its syntax is:

Value1 <= Value2

If both operands (Value1 and Value2) hold the same value, then the comparison produces a TRUE result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a TRUE result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a FALSE result

Note that the > and the <= operators are opposite each other.

Greater Than >

The > operator is used to find out whether one value is "Greater Than" another. Its syntax is:

Value1 > Value2

The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces TRUE. Otherwise, the comparison produces FALSE. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces FALSE.

 

Greater Than Or Equal >=

If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is:

Value1 >= Value2

If both Value1 and Value2 hold the same value, then the comparison renders a TRUE result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces TRUE. If the value of Value1 is less than the value of Value2, the comparison produces a FALSE result

Therefore, < and >= are opposite.

Logical Functions

 

Introduction

Because Microsoft Excel is not a programming environment, it doesn't use conditional statements seen in traditional languages such as C/C++, Pascal, C#, Visual Basic, etc. Instead, Microsoft Excel provides functions that can perform the same types of tests on cells' values and produce the same types of results.

A logical function is one that evaluates an expression and returns a Boolean result. For example, imagine you have a series of cells that represent employees last name. If you are interested to know what cell doesn't have a value (a last name), you can use a function. On the other hand, imagine you have a cell that is supposed to indicate whether an item must receive a discount, you can use a conditional function to check it.

Most, if not all, logical functions check a condition and render a result. The condition is also called a criterion. A criterion can be something like "Check if the cell contains Male", or "Check if the salary is less than $12.35".

IF

To check whether a criterion is true or false before taking a subsequent action, you can use the IF() function. Its syntax is:

IF(ConditionToTest, WhatToDoIfConditionIsTrue, WhatToDoIfConditionIsFalse)

When it is called, the IF() function checks the truthfulness or negativity of the ConditionToTest argument. If the result is true, then it will execute the first expression, WhatToDoIfConditionIsTrue in our syntax. If the result is false, it will consider the second option, the WhatToDoIfConditionIsFalse parameter in our syntax.

Nested IF

The IF() function we have introduced above is used to check one condition and then take one or the other action. In some cases, you will need to check more than one condition. In other words, you may want to check a first condition. If that condition is false, you may want to yet check another condition. The pseudo-code used for such a scenario is:

If Condition1 is True
Execute Statement1
Else If Condition2 is True
Execute Statement2
Else
Execute ElseStatement

Consequently, you can check as many conditions as you need to. The truthfulness of each condition would lead to its own statement. If none of the conditions is true, then you can execute the last statement. To implement this scenario, you can include an IF() function inside of another. You can also include as many IF() functions inside of other IF() functions.

We are going to use the IF() function to find out whether a student passes the mark or fails the class, based on the overall average of the student's grades. We will set the mark at 12.50. If a student's average is above 12.50, then he goes to the next grade, otherwise,

Practical Learning: Using the IF Functions

  1. Open the Red Oak High School workbook from Lesson 11
  2. In cell B13, type Pass/Fail?
  3. In cell C13, type =IF(
  4. Click cell C12 and type >=12.50,"Pass","Fail")
  5. Press Enter
  6. Click cell C13 to give it focus
  7. Drag its Fill Handle to cell H15. 
    You can also put an IF function inside of another, this is referred to as nesting
  8. In cell C14, type =IF(B14>16.5,"A",IF(B14>14.5,"B",IF(B14>12.5,"C",IF(B14>12,"D","F")))) and press Enter

Checking For Cell Emptiness

To check whether a cell is empty, you can call the Boolean ISBLANK() function.

Conditional Counting

In some cases you will need to count the frequency of (the number of times that) a value repeat itself in a series of cells. For example, suppose you have a list of students recognized by their gender using a certain column. Provided the genders are, for example, Male or Female. You may be interested in knowing the number of boys. In this case you can count the number of occurrences of Male in the range of cells.

The count the number of occurrences of a value in a series, you can use the COUNTIF() function. Its syntax is

COUNTIF(Range, Criteria)

The functions include MDETERM, MINVERSE, MMULT, PRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, and SUMXMY2.

 

Previous Copyright 2002-2008 FunctionX Next