Homework DiscussionHomework 4 (Glade Manual Chapter 4)

More on Logical, Information and Text Functions

Logical operators in Excel

Comparison Symbol

Less than <

Less than or equal to <=

Equal to =

Greater than or equal to >=

Greater than >

Less than or greater than <>

Logical functions in Excel

• Constants TRUE() and FALSE()• Traditional Boolean operators NOT(arg), AND(arg1,…),

OR(arg1,…)− For Boolean functions, the arguement arg needs to evaluate

to TRUE/FALSE

Boolean IS (information) functions in Excel

• Where Ref is a cell reference

Function TRUE if

ISBLANK(Ref) Empty cell

ISERR(Ref) Cell has any error except #N/A

ISERROR(Ref) Cell has any error

ISLOGICAL(Ref) Cell has logical (Boolean) value

ISNA(Ref) Cell has #N/A error (Reference not avail.)

ISNONTEXT(Ref) Cell is not text, TRUE for blank

ISNUMBER(Ref) Cell has a number

ISREF(Ref) Cell has a valid cell reference

ISTEXT(Ref) Cell has text

Text functions in Excel

• Where Ref is a cell reference and # is a number

Function Output

LEFT(Ref, #) # leftmost characters from a text value

RIGHT(Ref, #) # rightmost characters from a text value

MID(Ref, #1, #2) From position #1 in a text value, return #2 characters

LEN(Ref) Number of characters in a text string

EXACT(Ref1, Ref2) Checks it two strings are identical

CONCATENATE(Ref1, …) Joins several text strings into one

UPPER(Ref) Converts text to upper case

LOWER(Ref) Converts text to lower case

TEXT(#, format) Formats # and converts to text (see help for format)

VALUE(Ref) Converts text to a number

A note on the XOR gate

• Excel doesn’t have a function to simulate the XOR gate• You need to combine other functions to obtain this

functionality• Two possible ways:

− XOR is TRUE when either input is TRUE, AND NOT where both inputs are TRUE, i.e. in Boolean XOR = (A+B)(AB)’ In Excel: XOR=AND( OR(A,B), NOT(AND(A,B)) )

− XOR is TRUE only when the inputs are not equal In Excel XOR=A <> B