|
![]() |
![]() |
#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, 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. =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 |
||
|
||