vert line

#NOT#

Logical NOT.

#NOT#arg

arg a number or true/false value

This is the logical NOT function, but it is not used the same way as the logical AND and OR. For example,#NOT# 1 will equal 0, while#NOT# 0 will equal 1.

A3 = +("Blue"="Red") , which returns 0 #NOT#A3 = 1 : The formula in cell A3 asks "is the string 'Blue' = the string 'Red'? The answer is 0 (false). The #NOT# function inverts this returning the answer 1 (true)

#NOT#1 = 0 (false)

@@

Evaluates a string as a cell address.

@@(string)

string any string that can be interpreted as a cell address

This function evaluates the stringstring as a cell address and returns that address. This can be useful for creating a cell address with string concatenation.

A3: Cheers

B3: A3

@@(B3) -> Cheers

A1: 10

A2: 20

A3: 30

B1: A1:A3

@SUM(@@(B1)) -> 60

ADDRESS or @ADDRESS

Returns a cell address for a given row and column number.

ADDRESS(row,col)

row the number of the row

col the number of the column

The ADDRESS() function returns the address for therow andcol specified. Row and column numbering starts at zero, i.e. ADDRESS(0,0) is cell A1. This function can be useful in MScript scripts.

B1: 15

=ADDRESS(0,1) -> 15

BEEPIF or @BEEPIF

Beeps if the specified condition is true.

BEEPIF(cond)

cond a conditional expression

The BEEPIF function beeps and returns 1 if the conditioncond is true, and returns 0 if the condition if false. This can be useful for alerting the user of certain conditions within the worksheet.

C7: 100

=BEEPIF(C7<200) : This formula will beep one time.

=BEEPIF(rand>0.5) : This formula will generate a random number between 0 and 1. If the number is > 0.5, the it beeps and returns 1. Otherwise it returns 0.

CELL or @CELL

Returns information about the attributes of a given cell.

CELL(atrb,cell)

atrb the cell attribute

cell the cell address

The CELL() function returns attribute information about a the cell at the addresscell. The type of information requested is specified by the argumentatrb, chosen from the list below.

U7: PI

=CELL("address",U7) -> $U$7

=CELL("row",U7) -> 7

=CELL("col",U7) -> 21

=CELL("contents",U7) -> 3.141593

ADDRESS - returns the address of the cell

ROW - returns the row of the cell

COL - returns the column of the cell

CONTENTS - returns the value of the cell

FORMAT - returns the format of the cell. Formats are:

G - General

Fn - Fixed Decimal n places Sn - Scientific Format n decimal places

Cn - Currency format n decimal places

,n - Comma format n decimal places

+ - Chart Format

Pn - Percent Format n decimal places

T - Text Format

H - Hidden Format

D1 - Day Month Year Format

D2 - Day Month Format

D3 - Month Year Format

D6 - Hours Minutes Seconds Format

D7 - Hours Minutes Format

D4 - International Date Format 1

D5 - International Date Format 2

D8 - International Time Format 1

D9 - International Time Format 2

PREFIX - The string prefix in Lotus Format

PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise

TYPE - b - blank cell, v - number value, l - string value

WIDTH - Returns the width of the column in characters

RANGE - Converts a single address to a range

CELLPOINTER or @CELLPOINTER

Returns information about the attributes of the currently active cell.

CELLPOINTER(atrb)

atrb the cell attribute

The CELLPOINTER function returns attribute information about a the currently active cell. The type of information requested is specified by the argumentatrb. See "CELL or @CELL" for information about attributes. The currently active cell is the cell on the worksheet whose contents is currently being displayed in the formula bar. Mesa worksheets always have one currently active cell. If a range has been selected, the currently active cell is the cell in the upper left corner of the range.

CHOOSE or @CHOOSE

Chooses a numbered item from a list.

CHOOSE(num, item1[, item2[, ...])

num the number of the chosen item

item the members of the list

Chooses thenum item from the list of itemsitem1,item2, etc. This function counts the listed items starting at zero. You may list any combination of numbers, cell addresses, and ranges as arguments foritem.

=CHOOSE(2,"first","second","third","fourth") -> third

=CHOOSE(MOD(INT(NOW),7),"Thu","Fri","Sat",Sun","Mon","Tue","Wed") : This function returns the current day using NOW to provide the current date.

COLS or @COLS

Returns the number of columns in a range.

COLS(range)

range any range

The COLS function returns the number of columns contained in the rangerange.

=COLS(A1:H20) -> 8

If RANGE1 is the name assigned to the range B1:CC200, =COLS(RANGE1) -> 80

DIDCHANGE or @DIDCHANGE

Determines if the sheet has changed since it was last saved.

DIDCHANGE

DIDCHANGE returns a 1 if the sheet has changed since the last time it was saved, and a 0 if nothing has changed. Since the act of adding this function changes the sheet, it will only show as true when the sheet has been recalculated right after it has been saved. This can be useful in MScript scripts.

FORMAT or @FORMAT

Formats the number in the predefined format

FORMAT(num,fmt-const[,prec])

num any number

fmt-const any format constant

prec any integer [optional]

This function returns the given numbernum in the format specified by the format constantfmt-const. A list of the format constants can be found in "Format Constants". You may optionally specify the precisionprec, the number of decimal places in the number.

=FORMAT(18.9, SCIENTIFICFORMAT) -> 1.89e+01

GETINPUT or @GETINPUT

Returns the user-typed input string.

GETINPUT(cell)

cell any cell

The GETINPUT function returns the string typed into the cellcell by the user, rather than its value. This can be useful for MScript scripts.

A1 : =INT(3.2)

=A1 is 3

=GETINPUT(A1) is the string =INT(3.2)

HISTOGRAM or @HISTOGRAM

Creates a histogram for a given range.

HISTOGRAM(range, divs, anchor[, min, max])

range any range

divs the number of divisions in the histogram

anchor the starting cell of the histogram

min the [optional] minimum

max the [optional] maximum

The HISTOGRAM function groups the values inrange intodivs divisions and counts the number of items in each division. The formula should appear indivs cells, starting atanchor. It returns the number of items in the division that is offset from the anchor point.Min andmax are optional parameters that set the minimum and maximum values of the histogram range, so that the distribution will only be calculated for values betweenmin andmax.

A B

1 Name GPA

2 Joe 2.6

3 Sue 3.7

4 Robert 3.2

5 Steve 3.0

6 Ann 3.8

7 John 3.5

8 Mary 2.9

9 Chris 3.5

To divide the students into 5 divisions by GPA and determine how many fall into each division:

A10: =HISTOGRAM($B$2:$B$9,5,$A$10) -> 1

B10: =SAME($A$10) -> 2

C10: =SAME($A$10) -> 1

D10: =SAME($A$10) -> 2

E10: =SAME($A$10) -> 2

HLOOKUP or @HLOOKUP

Looks up a code in a horizontal table.

HLOOKUP(code, table, offset)

code the code to be looked up

table a range containing the lookup data

offset the number of rows offset from the top of the table.

Returns the celloffset cells from the top of the first match. If the first row contains numbers andcode is less than the first value in the lookup row, !ERR is returned. Ifcode is greater than the first value but no exact match is found, HLOOKUP returns the value from the last column. If the first row contains only strings and no exact match is found, it returns !ERR.

A B C

1 1 2 3

2 $200 $40 $500

=HLOOKUP(2,A1:C2,1) -> $40

=HLOOKUP(0.5,A1:C2,1) -> !ERR

=HLOOKUP(5,A1:C2,1) -> $500

IF or @IF

Returns a conditional result.

IF(exp,value1[,value2])

exp the conditional expression for evaluation

value1 the first possible result

value2 the [optional] second possible result

The IF function evaluatesexp. If it is a true or non-zero value, the result isvalue1. If it is false, zero, or a string, optionalvalue2 or an empty value is the result.

=IF(5>10,"Something is wrong!","Everything is normal.")

-> Everything is normal.

A1: 3400

A2: 4000

A3: 5000

=IF(SUM(A1:A3)>=12400,"Full Capacity") -> Full Capacity

IFELSE

Returns a conditional result.

IFELSE(exp, value1, value2)

exp the conditional expression for evaluation

value1 the first possible result

value2 the second possible result

The IFELSE() function evaluatesexp. If it is a true or non-zero value, the result isvalue1. If it is false, zero, or a string,value2 is the result.

=IFELSE(5>10,"Something is wrong!","Everything is normal.") -> Everything is normal.

A1: 3400

A2: 4000

A3: 5000

=IFELSE(SUM(A1:A3)>40000,"Full Capacity","Okay") -> Okay

INDEX

Returns a specified cell from a range of cells.

INDEX(range, row, col)

range any range

row the row offset from the upper left of the range

col the column offset from the upper left of the range

The INDEX function returns the cell that iscol columns androw rows offset from the upper left corner of the rangerange.

A B C

1 10 20 30

2 3942 4932 5929

3 4920 5929 9294

=INDEX(A1:C3,2,2) -> 9294

A10: 1

A11: 0

=INDEX(A1:C3,A10,A11) -> 3942

@INDEX

Returns a given cell from a range of cells.

INDEX(range,col,row)

range any range

col the column offset from the upper left of the range

row the row offset from the upper left of the range

The INDEX() function returns the cell that isrow rows andcol columns offset from the upper left corner of the rangerange.

A B C

1 10 20 30

2 3942 4932 5929

3 4920 5929 9294

@INDEX(A1:C3,2,2) = 9294

A10: 0

A11: 1

@INDEX(A1:C3,A10,A11) = 3942

INDEX and @INDEX differ slightly. INDEX() specifies the offset in row then column order. @INDEX() specifies the offset in column then row order.

ISEMPTY or @ISEMPTY

Determines if the cell at the given address is empty.

ISEMPTY(adr)

adr a cell address

This functions tests whether or not the specified cell is empty. Mesa functions that return a True or False result return 1 for True, and 0 for False.

A1 : 5.2

B1 :

=ISEMPTY(A1) is 0

=ISEMPTY(B1) is 1 because cell B1 has no contents.

ISERROR or @ISERR

Determines if the given expression returns an error other than @NA.

ISERROR(exp)

exp an expression

This function tests whether or not the given expression results in an error. In Mesa functions that return a True/False result, 1 corresponds to True, and 0 corresponds to False. See "ISNA or @ISNA".

=ISERR(ERRORCONST) -> 1 (true)

=ISERR(@VALUE("ABCD") -> 1 (true) : This formula tried to convert "ABCD" to a value using @VALUE(). This returns !ERR, which causes ISERR() to returns 1.

ISFORMULA or @ISFORMULA

Determines if the cell at the given address contains a formula.

ISFORMULA(adr)

adr a cell address

This function tests to see if the cell at the given address contains a formula. In Mesa functions that return a True/False result, 1 corresponds to True, and 0 corresponds to False.

A1 : 3.2

B1 : =A1+1

=ISFORMULA(A1) is 0

@ISFORMULA(B1) is 1 because cell B1 contains a formula that adds 1 to the contents of cell A1.

ISNA or @ISNA

Determines if the given expression returns an NA error.

ISNA(exp)

exp an expression

This function tests to see if the given expression returns the NA error. In Mesa functions that return a True/False result, 1 corresponds to True, and 0 corresponds to False.

=ISNA(@NA) -> 1 (true)

A1: 34

A2: 49

A3: =NA

=ISNA(SUM(A1:A3)) -> 1 : This formula returns 1 (true) because cell A3 contains NA, which causes SUM(A1:A3) to evaluate to NA also.

ISNUMBER or @ISNUMBER

Determines if the given expression returns a numeric result.

ISNUMBER(exp)

exp an expression

This function tests to see whether or not the given expression results in a number. In Mesa functions that return a True/False result, 1 corresponds to True, and 0 corresponds to False.

=ISNUMBER(FIXED(300,0)) -> 0 : This formula converts the number "300" to a string using FIXED(). It then uses ISNUMBER() to test if that result is a value and returns 0 (false).

=ISNUMBER(34) -> 1

A3: +394 - 100

=ISNUMBER(A3) -> 1

ISREF or @ISREF

Determines whether or not the given expression is a cell or range reference.

ISREF(exp)

exp an expression

This function tests to see whether or not the given expression is a cell or range reference. In Mesa functions that return a True/False result, 1 corresponds to True, and 0 corresponds to False.

=ISREF(A1) -> 1 (true)

If RANGE1 has been assigned A1:C10, =ISREF(RANGE1) -> 1

ISSTRING or @ISSTRING

Determines whether or not the given expression results in a string.

ISSTRING(exp)

exp an expression

This function tests whether or not the given expression will result in a string when evaluated. In Mesa functions that return a True/False result, 1 corresponds to True, and 0 corresponds to False.

=ISSTRING(VALUE("340")) -> 0

=ISSTRING(LEFT("Goodbye!",4)) -> 1

=ISSTRING("Some text") -> 1

@N

Returns the given expression if it is a number, 0 otherwise.

@N(exp)

exp an expression

If the given expression is a number, @N returns the expression, otherwise @N returns 0. @N is guaranteed to return a number and is used in functions that require numbers as parameters.

@N(@STRING(300,0)) -> 0

@N(34) -> 34

NEXT or @NEXT

Returns the next logical value for a given value

NEXT(value[, inc])

value a number, string, or cell reference

inc the [optional] number by which to increment the value

The NEXT function returns the next logical value aftervalue. The value is incremented by 1 unless an optional incrementinc is specified. For example, ifvalue is "Mon", then NEXT(value) returns "Tue". Values for months, days, and quarters are built in. Ifvalue is a number, NEXT adds 1 orinc to the number. For quarterly information, it will increment the quarter. When the quarter reaches 4, the next quarter is Q1 of the following year. For example, NEXT("Q4 '92") is "Q1 '93".

=NEXT("Q4 '92") -> Q1 '93

=NEXT("January") -> February

=NEXT("Region 1") -> Region 2

NOT or @NOT

Logical NOT.

NOT(num)

num any number

Ifnum is zero or not a number, the NUM() function returns 1; otherwise, it returns 0.

=NOT(34) -> 0

=NOT("Text") -> 1

RANGE or @RANGE

Gives the address for a numerically specified range.

RANGE(ur, lc, lr, rc)

ur number corresponding the upper row of the range

lc number corresponding the left column of the range

lr number corresponding the lower row of the range

rc number corresponding the right column of the range

RANGE returns the address for the range specified by the upper row, left column, lower row, right column. RANGE(0,0,1,1) is the range A1:B2. This can be useful in MScript scripts.

A1 : 0

B1 : 1

A2 : 5

B2 : 7

=SUM(RANGE(0,0,1,1)) is 13. This is the sum of the values in the range A1:B2

=SUM(RANGE(A1,A1,B1,B1)) is also 13. Because cell A1 contains 0 and cell B1 contains 1, this is the same as the above example.

ROWS or @ROWS

Returns the number of rows in a range.

ROWS(range)

range the address of a worksheet range

This function counts the number of rows in the rangerange and returns the number.

=ROWS(A1:H20) -> 20

If RANGE1 is the name assigned to the range B1:CC200, =ROWS(RANGE1) -> 200

@S

Returns the given expression if it is a string, or a zero length string otherwise.

@S(exp)

exp an expression

If the given expression is a string, this function returns the expression; otherwise it returns a zero length string. This function is used to guarantee that an expression is a string.

@S(343) -> "" (blank string)

A1: 2000

A2: Orders

@S(A1)&@S(A2) = Orders

SAME or @SAME

Executes the same function as in the given cell

SAME(adr)

adr the address of the cell containing the desired formula

Takes the formula in celladr and executes it as if it had been copied and then pasted into the current cell. This is a powerful and valuable function. You can have a master function and a series of functions that are the same. Where you change the master function, the method of calculation used by the other functions is changed. You can use SAME() in conjunction with INDEX(), HLOOKUP(), VLOOKUP(), and to define a formula used in a calculation rather than having a complex IF() statement. An application for this is an employee pay calculation that depends on an employee type.

A1: 1

A2: 2

If B1: =A1 * 5 and B2: =SAME(B1), then B2 = 10 (A2*5).

If B1: =A1/5, then B2 changes to 0.4 (A2/5)

If B1: =SIN(A1), B2 changes to 0.9093 (SIN(A2))

SETALTCOLOR or @SETALTCOLOR

Returns the given value and sets the alternate color to the specified color.

SETALTCOLOR(exp, colorconstant [, pattern]), or SETALTCOLOR(exp, r, g, b [, pattern])

exp any expression

colorconstant any color constant

r a number from 0 to 1, indicating the amount of red

g a number from 0 to 1, indicating the amount of green

b a number from 0 to 1, indicating the amount of blue

Sets the alternate color of the current cell and returns the given expression.Colorconstant is one of the colors listed in the "Constants" section (see "Color Constants"), orr, g, andb are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.

=SETALTCOLOR(A1,IF(A1 < -100,1,0),0,0) : This sets the alternate color to red if the number is less than -100, otherwise it sets the alternate color to black.

=SETALTCOLOR(A1,MAGENTACOLOR) : This sets the alternate color of cell A1 to Magenta.

SETBKGCOLOR or @SETBKGCOLOR

Returns the given value and sets the background color to the specified color.

SETBKGCOLOR(exp, colorconstant [,pattern]), or

SETBKGCOLOR(exp, r, g, b [,pattern])

exp any expression

colorconstant any color constant

r a number from 0 to 1, indicating the amount of red

g a number from 0 to 1, indicating the amount of green

b a number from 0 to 1, indicating the amount of blue

Sets the background color of the current cell and returns the given expression.Colorconstant is one of the colors listed in the "Constants" section (see "Color Constants"), orr, g

=SETBKGCOLOR(A1,IF(A1 > 50 && A1 < 100,1,0),0,0) : This sets the background color to red if the number is greater than 50 and less than 100, otherwise it sets the background color to black.

=SETBKGCOLOR(A1,CYANCOLOR,56) : This sets the background color of cell A1 to Cyan and the pattern to pattern number 56.

SETCOLOR or @SETCOLOR

Returns the given value and sets the text color to the specified color.

SETCOLOR(exp, colorconstant [,pattern]), or

SETCOLOR(exp, r, g, b [,pattern])

exp any value

colorconstant any color constant

r a number from 0 to 1, indicating the amount of red

g a number from 0 to 1, indicating the amount of green

b a number from 0 to 1, indicating the amount of blue

Sets the color of the current cell and returns expression. Colorconstant is one of the colors listed in the "Constants" section (see "Color Constants"), orr, g, andb are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.

=SETCOLOR(A1,IF(A1 > 100,1,0),IF(A1 > 100,0,1),0) : This sets the color to red if the number is greater than 100, otherwise it sets the color to green.

=SETCOLOR(A1,BLUECOLOR) : This sets the color of cell A1 to Blue.

SIGNAL or @SIGNAL

If the condition is true, sends range of cells to the named Mach port.

SIGNAL(cond,port,range)

cond this function only sends data if this parameter is TRUE.

port a string containing the name of the port to send the data to.

range the range of cells to send to the named port.

This function works in conjunction with the MesaListen class of objects to allow a spreadsheet to send a range of cells to a custom application in when a certain event takes place. The MesaListen object in the custom application can then take the appropriate action. An example is that a signal could be sent if an arbitrage opportunity presented itself. The parameters of the opportunity could be programmed into the worksheet. When the parameters became true, perhaps based of real time data being fed into the worksheet, the worksheet could send a signal to a custom application that would perform a securities trade.

=SIGNAL(GOLD_PRICE > 50 * SILVER_PRICE,"BUY_GOLD",A1) - send a message to the "BUY_GOLD" object when the price of gold is 50 times higher than the price of silver.

VLOOKUP or @VLOOKUP

Looks up code in the vertical table.

VLOOKUP(code,table,offset)

code a string or number identifying the desired values

table the range of the lookup table

offset the offset of the relevant column

Returns the celloffset cells from the left side of the first match. If the first column contains only strings and code is a number, it returns the value from the last row. Ifcode is also a string, but doesn't match any of the table entries, !ERR is returned.

A B

1 A 5.0

2 B 4.0

3 C 3.0

4 D 2.0

5 F 0

=VLOOKUP("A",A1:B5,1) -> 5.0

=VLOOKUP("D",A1:B5,1) -> 2.0

=VLOOKUP("Incomplete",A1:B5,1) -> !ERR

=VLOOKUP(3.0,A1:B5,1) -> 0

Also go to:

Index help

Contents help