Contents Introduction File menu Edit menu View menu Format menu Tools menu Statistics menu Graphs menu Tests menu Sampling menu Window menu Help menu Spreadsheet Appendices

Logical functions

AND And function

AND(condition1,condition2,...) returns 1 (=TRUE) of all of its arguments are TRUE. If one of the arguments, either condition1, condition2, etc. is FALSE (or 0), the function returns 0 (=FALSE). If one of the conditions cannot be evaluated (for example because of missing data), the function returns an error.

(LENGTH>160) AND (LENGTH<170) can be written as:

AND(LENGTH>160,LENGTH<170)

CATEGORISE Categorise function

CATEGORISE("variable","condition1",value1,"condition2",value2,...,"conditionN",valueN[,defaultvalue]) recodes a variable into different categories. If condition1 is true then the function returns value1, else if condition2 is true then the function returns value2, and so on.

The first parameter is the variable name and must be placed between quotation marks. The following parameters are a serious of conditions and values. The conditions must be placed between quotation marks. The list of conditions is evaluated from left to right. If a condition is true, the value that follows is returned as the result of the function.

The last (optional) parameter (defaultvalue) of the function specifies a default value which is returned when none of the conditions is true.

CATEGORISE("VALUE","=0","Zero","<0","Negative",">0","Positive") returns the string value "Zero" when the variable VALUE contains the value 0, the string value "Negative" is returned when the variable VALUE is less than 0.

CATEGORISE("AGE",">60","old",">25,"middle-aged","young") returns the string value "old" when the variable AGE contains a value higher than 60, "middle-aged" is returned when AGE" is higher than 25. In all other cases the function returns "young".

IF If-then-else function

IF(condition,x,y) returns x if the condition is TRUE (=1), but returns y if the condition is FALSE (=0).

IF(C2<0,"NEG","POS") returns the string value "NEG" if the number in cell C2 is less than 0 (C2<0 = TRUE). If cell C2 contains a number equal to or more than 0, then this function returns the string value "POS").

IF(A1>1,25,33) returns 25 if cell A1 contains a value greater than 1. If A1 contains 1 or a value less than 1, then this expression returns 33.

This function is also useful for conversion of continuous data into discrete data.

ISEMPTY cell-is-empty function

ISEMPTY(var) results in the logical value 1 (=TRUE) if the calculation of var does results in a numeric or text value.

ISNUMBER Cell-is-number function

ISNUMBER(var) results in the logical value 1 (=TRUE) if the calculation of var results in a number.

ISNUMBER(25.6) returns 1

ISNUMBER(B2) returns 0 (=FALSE) if cell B2 contains e.g. the string value "SMITH"

ISSTRING Cell-is-string function

ISSTRING(var) results in the logical value 1 (=TRUE) if the calculation of var does not result in a numeric value.

ISSTRING(B2) returns 1 (=TRUE) if cell B2 contains a string value, e.g. "SMITH"

ISSTRING(25.6) returns 0

NOT Not function

The function NOT(x) reverses the value of its argument x. If x is 0 or FALSE then NOT(x) returns 1 (= TRUE). If x is 1 or TRUE then NOT(x) returns 0 (= FALSE).

ODD Odd number

ODD(x) returns 1 (=TRUE) when x is an odd number, else this function returns 0 (=FALSE).

OR Or function

OR(condition1,condition2,...) returns 1 (=TRUE) of at least one of its arguments is TRUE (or 1). If one of the conditions cannot be evaluated (for example because of missing data), the function returns an error.