cse lab 4.doc

12 Pages
Unlock Document

York University
Electrical Engineering and Computer Science
EECS 1520
David Langille

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

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.