The spreadsheet information functions return some type of information about a cell or block of cells. The @CELL, @CELL-POINTER, and @CELLINDEX functions take an attribute (en-closed in quotation marks) as part of their parameters.
The following attributes can be included:
• Address: Cell address
• Col: Column number of the cell
• Contents: Contents of the cell
• Format Cell's numeric format
• Prefix: Label prefix of a text entry
• Protect. Cell's protection status
• Rwidth: Width, in characters, of a block of cells
• Row: Row number of the cell
• Type: Type of cell entry
• Width: Width of cell's column
Table 4.3: Statistical Functions
FuNCTION RETURNS
@AVG(list) Average of the numeric values in the list.
@COUNT( list) Number of nonblank cells.
@MAX(list) Largest numeric value.
@MIN(list) Smallest numeric value.
@STD(list) Population standard deviation of the numeric values.
@STDS(list) Sample standard deviation of the numeric values.
@SUM(list) Total of the numeric values.
@SUMPRODUCT(blockl,block2) Multiplies each cell in blockl by the corresponding cell in block2, and then sums all the resulting products.
@VAR(list) Population variance of the numeric values in the list.
@VARS ( list) Sample variance of the numeric values.
CH.4
Table 4.4: Table Lookup Functions
FUNCTION RETURNS
@CHOOSE (n, list) The item from the list in position n (the first item in the list is in position·O).
@HLOOKUP(x,block,offset) Looks for x (closest numeric match or exact string match) in the first row of block and returns the value found in the cell below x in the row specified by offset.
@INDEX( block, col, row) Returns the value found in block at the junction of col and row.
@VLOOKUP(x, block, offset) Looks for x (closest numeric match or exact string match) in the first column of block and returns the value found in the cell to the right of x in the column
specified by offset.
For example, the formula
@CELLPOINTER("address")
returns the cell address at the cell selector's location.
Table 4.5 summarizes the spreadsheet information functions.
USING STRING FUNCTIONS
You can't add one string of text to another, but you can con-catenate them, or join them together. Performing this type of operation is known as string arithmetic.
The ampersand, &, is the operator you use to join two text strings. Because you are creating a formula, the text you join must be text values, not just plain text. In other words, no label prefixes are involved.
Table 4.5: Spreadsheet Information Functions
FuNCTION RETURNS
@@(cell) Contents of the cell whose address is specified
in cell.
@CELL(attribute,block) Status for the given attribute of a cell or upper-left cell in block.
@CELLINDEX( attribute, block, col, row) Status for the given attribute of the cell found in block at the junction of the column and row.
@CELLPOINTER( attribute) The status for the given attribute of the cell at
@COLS(block)
@ROWS(block)
the cell selector's location.
Number of columns in block.
Number of rows in block.
As you saw in Chapter 3, text within a string formula must be enclosed in quotation marks, as in
+"This is a simple text value"
The formula can also reference a cell that contains text (either a text value or plain text). For example, if the first name, Pat, is in cell AI, and the last name, Pilz, is in cell BI, then
+AI&BI returns PatPilz +AI&" "&BI returns Pat Pilz +AI&" E. "&BI returns Pat. E. Pilz +BI&", "&AI&" E." returns Pilz, Pat E.
There are quite a few functions that allow you to manipulate strings. Some functions, such as @LEFT and @MID, extract
CH.4
pieces from a text string. Other functions, such as @LENGTH and @FIND, return a number based on the contents of a text string.
Just remember that all text must be enclosed in quotation marks, and you are not allowed to mix numeric and text values in the same formula. The @STRING and @VALUE functions are provided so that a formula can either be all text or all numeric. For example, if a name were in cell A5 and that person's age were in cell G16, you could combine the two into one text formula in this way:
+"Did you know that "&A5&" was "&@STRING(G16,O)&"
years old?"
String formulas can grow quite long and complex. Always test your formula as it grows, instead of writing the whole thing and then trying to figure out why it doesn't work. Or, as recom-mended for long logical expressions, split the formula among several cells.
Although the formula shown above is not very long or com-plex, it can serve as an example of how you can break down long string formulas. You could enter the various pieces in separate cells, and then concatenate them all together in one formula.
You could layout the pieces this way:
D 1: 'Did you know that D2:' was
D3: @STRING(G16,O) D4: ' years old?
Then, in another cell, you could write the formula that ties all the pieces together, such as this:
+Dl&A5&D2&D3&D4
Table 4.6 summarizes the string functions.
Table 4.6: String Functions
FUNCTION RETURNS
@CHAR(n) The ASCII character corresponding to the value of n.
@CLEAN(s) Removes all nonstandard ASCII codes from string s.
@CODE(s) The ASCII code of the first character in string s.
@EXACT(s1,s2) Compares string s1 with string s2 and returns 1 if they are exactly alike. Unlike the expression s1*s2, this function is case sensitive.
@FIND(s1,s2,n) Position number of the first occurrence of s1 within s2, starting from position n in s2.
@HEXTONUM(s) Converts to a decimal value the string s, which looks like a hexadecimal value.
@LEFf(s,n) First n characters in string s.
@LENGTH(s) Length of string s.
@LOWER(s) Forces all letters in string s to lowercase.
@MID(s,n,n1) Returns n1 characters from string s, starting with character number n (the first character is number 0).
@N(block) Numeric value in the cell at the upper-left corner of block.
@NUMTOHEX(n) Converts the numeric value n to a string that looks like the hexadecimal equivalent of n.
@PROPER(s) Forces all words in string s to initial capitals.
@REPEAT(s,n) A string value made by repeating n times the string s.
CH.4
Table 4.6: String Functions (continued)
FUNCTION RETURNS
@REPLACE(sl,n1,n2,s2) Puts string s2 into string sl, replacing n2 characters,
@RIGHT(s,n)
@S(block)
@STRING( n1, n2)
@TRIM(s)
@UPPER(s)
@VALUE(s)
starting at position nl.
The last n characters in string s.
The string value in the cell at the upper-left corner of block.
Converts the numeric value n1 into a string, and rounds it to n2 decimal places.
Trims all leading or trailing spaces and any multiple internal spaces from string s.
Forces all letters in string s to uppercase.
The numeric value of string s, which looks like a number.