Wordperfect Quattro Pro

Reference: Working with formulas and functions

Rules of mathematics and syntax are applied when you enter or modify formulas and functions in spreadsheets. Using arguments, dates, and arrays in functions allow you to extend your calculations. As well, setting cell names and addresses simplifies entering formula and function expressions.

Setting cell addresses

When you create formulas that reference cells you can refer to the cells by their cell address. This address is the intersection of the column letter and the row number, such as A15 or B6. When referring to a cell on a different spreadsheet, first specify the name of the spreadsheet; for example, C:A15. You can also specify a block of cells in this way by creating a cell address range, such as A5..D16.

When you reference data in formulas, functions, or macros, you can refer to the cell by relative cell address, absolute cell address, or cell name.

Relative cell addresses

When you set a relative cell address, the formula calculates the data from cells relative to its current location in the spreadsheet. When you change the address of a cell that contains a formula, the formula calculates the address relative to its new location. For example, if you create a formula in cell A3 to add cells A1 and A2, and then you move the formula to cell D3, the formula adjusts to add cells D1 and D2.

By default, cell formulas calculate relative cell addresses.

Absolute cell addresses

When you set an absolute cell address in a formula, the formula always references the same cells regardless of their location on the spreadsheet. For example, if you create a formula in cell A3 to add cells A1 and A2, and then you move the formula to cell D3 on the same spreadsheet, the formula still calculates cells A1 and A2.

You can set an absolute address by typing a dollar sign before the part of the cell address you want to make absolute. For example,

In Edit mode, press F4 when a cell address is highlighted in the input line to make a cell absolute. Press F4 repeatedly to cycle through the eight absolute combinations.

Entering cell addresses

An easier way to enter cell references in formulas, instead of typing them, is to point to them. Make sure the cursor is after an operator character, and click the cell you want to include in the formula. You can also point to cells by dragging them or using keyboard commands. To point to noncontiguous selections, select the first group of cells, hold down Ctrl, then select additional cells. When every selection you want to refer to or act up on is highlighted, complete the formula or command.

You can also type the cell address directly into the formula cell. Depending on the cell selection you want to name, you must type the selection in a specific way.

Note that you can enter the addresses of any two cells in opposite corners in any order. The coordinates are rewritten to list the top-left cell followed by the bottom-right cell. As well, if you rename a sheet, you can use the new name on its tab in the cell reference or the sheet letter. And, if you group sheets, you can refer to them by their group name instead of by their individual sheet names.

Operator precedence

The result of a formula depends on the order in which arithmetic operations are performed. Each operator has a precedence, and the formula performs the operations in order of precedence. Operations with equal precedence are performed from left to right. For example, multiplication has higher precedence than addition, therefore the equation +5 + 1 * 3 equals 8, not 18.

The following table lists operators and the precedence assigned to each. Operators with the highest precedence (7) are performed first.

You can override operator precedence using parentheses. Enclose in parentheses the part of a formula to calculate first. When parentheses are nested inside other parentheses, the innermost part is calculated first.

Rules for moving formulas and referenced cells

When moving formulas and referenced cells, you should be aware of the following rules:

Rules for entering spreadsheet functions

Spreadsheet functions contain:

A spreadsheet function must follow these syntax rules:

•

Multiple arguments must be separated with a semicolon or the argument separator specified by clicking Tools Settings International Punctuation.

Entering arguments in functions

Arguments refer to the information required by a spreadsheet function to perform a calculation. Most spreadsheet functions need at least one argument. There are three types of arguments: numeric values, cell values, and string values.

Some spreadsheet functions accept a combination or choice of types for a single argument. For example, @SUM accepts cells in combination with numeric values:

@SUM(B10..C25,50) totals numeric entries in cells B10..C25 plus 50.

You can separate arguments with a semi-colon, or you can specify a setting for separating arguments in Tools Settings International Punctuation. In the example above, a comma separates arguments. If the message “Not enough arguments” appears after entering a spreadsheet function, use a semi-colon between arguments.

Entering numeric arguments

A numeric value can be used as a spreadsheet function argument in any of these for
ms:

Entering cell arguments

Cells can be used as spreadsheet function arguments in a number of ways:

Entering string arguments

A string value can be used as a spreadsheet function argument in the following ways:

Working with date functions

Date functions that require an integer date value as the date argument accept a valid date string, such as ’12/31/1991′ or ’31-Dec-1997′.

If you use arguments for date functions, Quattro Pro assumes the century based on the following year ranges:

The date functions @DATE, @LWKDAY, @MDAYS, @NWKDAY, and @YDAYS accept a standard year, such as 2001, in addition to the original year arguments of 0 (1900) and 199 (2099). For example:

@DATE (40,12,31) and @DATE (1940,12,31) both return 14976 (31-Dec-1940)

@DATE (140,12,31) and @DATE (2040,12,31) both return 51501 (31-Dec-2040)

Working with arrays

The spreadsheet function ARRAY and other features help you use data arrays, which are selections of data you work with as a group. Instead of entering several formulas, you can work with arrays and enter just one formula. Working with arrays also saves computer memory, although it can lengthen recalculation times. Array formulas can contain block arrays or array constants.

A block array is a selection of rows and columns. With block arrays, you can type one formula that enters results in many cells. An array constant is a group of numbers used as an argument in a formula that can produce one or many results.

Using block arrays in formulas

The array B2..D3 below contains data in three columns and two rows that can be worked with independently, or one selection.

To total columns, you could enter @SUM(B2..B3) in B4, then copy it to C4 and D4. To take advantage of the array, ho
wever, you could select B4 then type an array formula: @ARRAY(B2..D2+B3..D3).

The spreadsheet ARRAY(B2..D2+B3..D3) contains the same information as three @SUM formulas. The array directs Quattro Pro to add the values in each column and show the results in the formula cell and the two cells to the right. The number of columns and rows in the output array depend on the dimensions of the arrays in the array formula.

You can use any mathematical or logical operator in an array formula: +, -, *, /, ^, =, <>, <=, >=, <, >, #AND#, #OR#, and &.

As with other formulas, calculations are performed according to operator precedence: multiplication and division first, then addition and subtraction. To change the calculation order, use parentheses.

Formula syntax

To create a formula using block arrays, type a selection followed by an operator, followed by the next selection or value. For example,

@ARRAY(B2..D2-B3..D3)

@ARRAY(B2..D2*5)

The operator indicates the operation to perform on matching cells of each block or the relationship between them. For example, – (minus) means “subtract each cell of the second block from the equivalent cell of the first block.” When the second formula above is calculated, it expands the 5 to a 1 X 3 array to match the first array. So, it calculates B2*5, C2*5, and D2*5, then enters three formulas.

When you are working with rows, array results appear in the ARRAY formula cell. For columns, the results appear beside the ARRAY formula cell. You can mix columns and rows. Block arrays can be contiguous or noncontiguous, but they must be 2D.

You can enter more than two row or column blocks and use multiple operators. For example, the formula @ARRAY(B2..B4+C2..C4*D2..D4) means “multiply C2 by D2 and add B2, multiply C3 by D3 and add B3, then multiply C4 by D4 and add B4.”

You can use cell names instead of references to specify arrays. If you later change the selection identified by the cell name, all ARRAY formulas containing the cell name automatically change to the new selection.

For example, you can make EXPENSES in the array @ARRAY(EXPENSES*5), the selection C2..E4. You can also type +B2..D2+B3..D3 in any of the above examples, to add ARRAY spreadsheet function and parentheses.

Using array constants in formulas

An array constant is a group of numbers used as an argument in a formula. You can use an array constant to provide values for a formula instead of typing the values in a notebook. The numbers in the array are enclosed in braces {} and separated by semicolons. For example, an array is used in the formula @SUM({3;4;5}*5). The formula expands to @SUM({3;4;5}*{5;5;5}) as it is calculated. The formula calculates 3 * 5 + 4 * 5 +5 * 5, and displays 60 as the total.

You can specify different rows in an array constant. To do this, separate rows with the pipe symbol (|). For example, the values below are indicated by the array constant {1;2;3;4|5;6;7;8}:

The array constant {5;6;7;8} specifies values in the second row above. The array constant {3|7} specifies values in the third column.

Working with arrays as functions and macro arguments

You can use block arrays and array constants as arguments for spreadsheet functions other than ARRAY. Any spreadsheet function that accepts block arguments can handle array operations, although different types of spreadsheet functions handle them differently.

@ABS, @SQRT and many other spreadsheet functions normally take one argument, which is a single value or an expression that results in a single value, not a block. If you enter a block or array constant for these spreadsheet functions, the spreadsheet function statement is converted into an argument for ARRAY, and a value displays for each row or column of the array.

Statistical functions

@SUM, @AVG, and other statistical spreadsheet functions always return a single value, no matter how large an array you specify. For example, @SUM(C4..C8*D4..D8) means “multiply each value in C4..C8 by the equivalent value in D4..D8 and add the results.”

Database functions

The database functions, such as @DSUM, take three arguments: a database block, the column to operate on, and a search criteria block. The first and third arguments must be blocks, not array constants. The second argument can be an array. In this case, the spreadsheet function is calculated once for each value in the array.

Guidelines for solving errors in formulas

The following guidelines cover some common causes of errors:

+202-555-3670

+12/12/94

Guidelines for naming cells

Setting cell names and addresses simplifies entering formula and function expressions. When naming cells in Quattro Pro, the following guidelines should be considered.

Cell naming syntax

Automatic cell naming

If you select more than one location in the Generate cell names dialog box, a set of names is created for each set of labels.

If you enable Name cells at intersections, each cell has an additional name created by combining the two sets of labels. Each name can have up to 64 characters. The following is a table of cell names for this example:

Reference: Working with formulas and functions