Class Notes
(809,569)

Canada
(493,754)

York University
(33,568)

EECS 1520
(163)

David Langille
(3)

Lecture

# cse lab 4.doc

Unlock Document

York University

Electrical Engineering and Computer Science

EECS 1520

David Langille

Fall

Description

CSE 1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
Chapter 4:
More on Logical, Information, and Text Functions
Introduction
Logical functions are those that involve Boolean values. The Boolean values are TRUE and
FALSE. Some logical functions return a Boolean value as their result, others use the
Boolean result of a comparison to choose between alternative calculations.
There are six functions listed in the logical group in Excel 2003 – the functions AND,
FALSE, IF, NOT, OR, TRUE – and a seventh in Excel 2007 – the function IFERROR.
You’ll see the use of most of these in this lab. First, however, it’s worthwhile to become
familiar with the logical operators.
Logical Operators
TRUE and FALSE are common concepts. They are values which pertain to statements. For
example, the statement “It is morning.” is either TRUE or FALSE. We recognize that its
truth value may change, but at any particular time the statement is either TRUE or FALSE.
What may be hidden here is the existence of an implied comparison. To determine the truth
value of any statement we compare our understanding of the meaning of the claim with the
facts. Strictly speaking the statement “It is morning.” means the time of day is after
midnight and before noon. To decide if it’s TRUE we need to know the actual time of day
and compare it to our criteria.
It’s in these comparisons that we use Logical Operators:
Comparison Symbol
less than <
less than or equal to <=
equal to =
greater than or equal to >=
greater than >
less than or greater than <>
A comparison typically involves checking if two values are equal or if one is less than the
other, for example. To test if some cell that you havPricecontains a value that is
larger than some other cell that you have noldPriceyou would use the comparison
Price > oldPrice. If the value in cPrice was indeed greater than the value in cell
oldPrice the value of the comparison expressionPrice > oldPrice would be true,
otherwise the value would be false.
4-1 The Glade CSE 1520.03
Computer Use: Fundamentals
Laboratory Handbook
Logical Functions
As mentioned above, there are six or seven functions listed in the logical group in Excel.
The functions TRUE and FALSE really do not merit much discussion. They have no
arguments, and as such are no different that the Boolean values themselves. In other words,
entering the formula
= FALSE()
into a cell produces the display FALSE. This same display can be caused by simply
entering the word into the cell. So why is there such a function? Unfortunately, if there is a
good reason, it’s been lost. We can assume that historically there was a perceived need for
these functions and that there has never been a good reason to eliminate them. Whatever
the case, we will not use them.
The more traditional Boolean operators areAND , OR , NOT . These are used to build
complex Boolean expressions.NOT() is the inverter. It evaluates the Boolean expression
that is its argument and returns the opposite value. So
NOT (TRUE) = FALSE
NOT (FALSE) = TRUE
AND and OR have the meanings with which you are by now familiar, but note again their
implementation as functions rather than operators. We use AND as an operator when we
say “The hour is greater than midnight AND less than Noon.” However, in Excel, the
formula begins with the function name which is followed by the arguments in parentheses.
Assume there is a cell namedTimeOfDay which contains the hour portion of the current
time. To build an Excel function to determine if it’s morning we need a formula like this:
= AND (TimeOfDay >= 0, TimeOfDay < 12)
Of course, both of these comparisons must evaluate as TRUE in order for the formula to
return TRUE – that’s what AND means.
The OR function is implemented in a similar fashion. Suppose, for example, a spreadsheet
is used to determine if customers are eligible for an off-hours discount offered between the
hours of 10:00 p.m. and 7:00 a.m. It could use the following formula which takes
advantage of the fact that Excel stores times in 24-hour format:
= OR (TimeOfDay < 7, TimeOfDay >= 22)
4-2 CSE 1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
IS Functions
There is another group of useful functions in Excel called Information Functions because
they provide information about the cells to which they refer. Not all of these produce
Boolean results and are therefore of limited interest to us at this time. These others,
however, can be very useful:
Function Returns TRUE if
ISBLANK (Reference) Reference refers to an empty cell.
ISERR (Reference) Reference refers to any error Reference except #N/A.
ISERROR (Reference) Reference refers to any error Reference (#N/A, #REFERENCE!,
#REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL (Reference) Reference refers to a logical Reference.
ISNA (Reference) Reference refers to the #N/A (Reference not available) error
Reference.
ISNONTEXT (Reference) Reference refers to any item that is not text. (Note that this function
returns TRUE if Reference refers to a blank cell.)
ISNUMBER (Reference) Reference refers to a number.
ISREF (Reference) Reference refers to a reference.
ISTEXT (Reference) Reference refers to text.
These functions are often used as the Logical_test in an IF function. For example, it's
possible that the cell a formula will use as the divisor might be blank. The formula would
then produce an error because the blank cell has value 0, and division by 0 is impossible.
To prevent this from happening the formula can be included as one value of an IF, and one
of the IS functions can test to see if it should be performed. For example a column called
Average could be calculated as follows:
= IF (ISBLANK (Count), "", Total / Count)
This formula "looks" at the current value of Count to see if the cell is empty. If it is, the cell
is left blank because "" represents the NULL string, i.e. a string with no characters. Only if
the ISBLANK function returns FALSE will the calculation be attempted by Excel.
Of course it might happen that the contents of Count might not be a number. In such a case
the ISBLANK function will report FALSE, but the text will evaluate to 0 and the error
message will appear. So it might be better to use a different test:
= IF (ISNUMBER (Count), Total / Count, "")
Or if the number might be 0…
= IF (AND (ISNUMBER (Count), Count > 0), Total / Count, "")
Text Functions
4-3 The Glade CSE 1520.03
Computer Use: Fundamentals
Laboratory Handbook
Another group of Excel functions help manipulate strings of text. Of the twenty-seven
listed in Excel Help we are only interested in a handful at this time.
LEFT Returns the leftmost characters from a text value
RIGHT Returns the rightmost characters from a text value
MID Returns a specific number of characters from a text string starting at
the position you specify
LEN Returns the number of characters in a text string
EXACT Checks to see if two text values are identical
CONCATENATE Joins several text items into one text item
UPPER Converts text to uppercase.
LOWER Converts all uppercase letters in a text string to lowercase.
TEXT Formats a number and converts it to text
VALUE
Converts a text argument to a number
The LEFT , RIGHT , and MID functions are provided so that a formula can examine the
characters at specific positions within a string. For example:
= LEFT (“Hi ho”, 2)
returns the string “Hi”. It copies 2 characters from the left end of the string.
Similarly,
= RIGHT (“Hi ho”, 2)
returns the string “ho” because it copies 2 characters from the right end of the string.
The MID function has a different form.
= MID (“Hi ho”, 2, 3)
produces the string “i h”, i.e. 3 characters starting from position 2.
These can be combined in a variety of interesting ways. For example,
= LEFT (RIGHT (“Hi ho”, 2), 1)
returns 1 character from tLEFT end of the string produced by taking 2 characters from
theRIGHT end of the original string – the second-last character.
4-4 CSE 1520.03 The Glade
Computer Use: Fundamentals

More
Less
Related notes for EECS 1520