Formulas are mathematical equations that calculate a final value, such as the difference between two cells or the total of a column. Functions are a set of standard built-in formulas used to simplify complex calculations. Quattro Pro allows you to use both formulas and functions in developing spreadsheet projects.
In this section, you'll learn about
working with formulas
recalculating formulas
moving and copying formulas and referenced cells
composing formulas
working with spreadsheet functions
tracing errors in formulas
naming cells
For more information about working with formulas and functions, see "Reference: Working with formulas and functions."
Working with formulas
Using Quattro Pro, you can create math formulas. The following characters are used when creating a formula:
Character Result
. establishes the following data as a value
+ establishes the following data as a positive value
- establishes the following data as a negative value
( ) establishes the following data as a formula and calculates first anything contained in the parentheses
@ establishes the following data as a function
# establishes the following data as a logical equation
$ establishes the following data as a currency value
= establishes the following data as a value
As well, a formula can use any of the following arguments:
Argument Examples
Numbers 1948, -84, 43.23, 121/2
Cell coordinates B12, G29..G31, B:A3..D6, or [NOTEBK2]A:A1
Cell names EXPENSES (which may reference D5..D10)
Spreadsheet functions @SUM(B1..B24)
Text set off by " " "PROFIT" or "Dear Mr."
You create formulas using cell data, such as column totals. You can also create text formulas. Text formulas are any formulas that have a textual result. Quattro Pro also allows you to create logical formulas that evaluate true or false. For example, the result of the formula +C3<10 displays either 1 or 0 depending on the value of cell C3.
Displaying error sources directly in your cells shows you the cause of any problem with your formula. As well, you can convert formulas to values; the value appears in the cell and the input line, replacing the formula.
For additional information about creating formulas, see "Operator precedence", "Guidelines for solving errors in formulas", and "Working with arrays".
To create a math formula
1 Type a plus sign (+) in a blank cell.
2 Type a formula.
3 Press Enter.
Tip
To view formula results as you enter the formula data, press F9.
To create a formula using cell data
1 Type a plus sign (+) in a blank cell where you want to enter the formula.
2 Begin typing the formula
3 Click the cell that contains the data to insert in the formula.
4 Finish typing the formula.
Tip
If the cell containing the data has a cell name, you can type the name into the formula instead of clicking on the cell. Make sure the insertion point for the name is to the right of an operator or opening parenthesis in the formula.
To create a text formula
1 Type a plus sign (+) in a blank cell.
2 Type the formula using an ampersand (&) to combine text strings.
For example,+C4&"Review" enters the text in cell C4, then the word Review.
3 Press Enter.
Tip
If you want to add a space between strings, type it inside the quotation marks. For example,+C4&" Review" enters the text in cell C4, a space, then the word Review.
To create a formula that evaluates to true or false
1 Click a blank cell.
2 Type a formula using one of the logical operators.
3 Press Enter.
Notes
If the statement is true, a 1 displays in the cell. If the statement is false, a 0 displays.
For an explanation of operators, see "Operator precedence."
To display error sources in cells
1 Click Format Notebook properties.
2 Click the Recalc settings tab.
3 Enable the Audit errors check box.
Note
When you enable the Audit errors check box, each problem cell displays the sheet and cell reference where the problem started. The notebook name is included if the problem cell is in another notebook.
To convert a formula to a value
1 Select a cell containing a formula.
2 Click Edit Convert to values.
3 Type the destination cells in the To box.
Notes
To replace a formula in a cell with its value, specify the same cells in the To box as are in the From box.
To copy the value of a formula to another part of the notebook, specify the top-left cell to which you want to copy the value.
Tip
You can also specify the addresses of the destination cells by clicking the Range picker to the right of the relevant box and selecting a cell.
Recalculating formulas
You can set Quattro Pro to update calculations at specified intervals. By default, formulas are calculated when you enter them and recalculated each time you change the data. If a notebook contains many complex formulas, you may want to delay recalculation to save time.
You can set the order in which formulas recalculate depending on the nature of the formula. Available options are Natural, Column-wise, and Row-wise. You can also set the number of recalculation iterations.
To specify a calculation interval
1 Click Format Notebook properties.
2 Click the Recalc settings tab.
3 Enable one of the following options:
Automatic-recalculates formulas automatically, but pauses until recalculation is finished. Use Automatic if you have complex formulas that Background mode does not handle fast enough.
Manual-recalculates the formulas you enter or edit, but does not recalculate the entire notebook until you press F9. This setting is useful for large notebooks with many calculations.
Background-recalculates formulas between keystrokes. Background mode does not interrupt your work and recalculates automatically. Quattro Pro always finishes recalculating before saving, extracting, or printing the notebook.
Note
The recommended recalculation setting is either Background or Automatic; both ensure that your data is accurate.
Tips
If you choose to use Manual mode, you can also recalculate single cells by selecting the cell, pressing F2, then pressing Enter.
You can manually calculate all formulas by clicking the calculator in the application bar; the calculator is enabled only when a formula needs to be recalculated.
To set the recalculation order
1 Click Format Notebook properties.
2 Click the Recalc settings tab.
3 Enable one of the following options:
Natural-calculates cells in dependency order. Formulas without dependencies are calculated first, followed by formulas that depend on them.
Column-wise-starts recalculation in cell A1 of the first sheet and proceeds down column A, ignoring formulas in other columns. When column A is finished, recalculation continues in cell B1, and down column B. This continues to the end of the sheet (column ZZZ), and on to the last sheet in the notebook.
Row-wise-starts recalculation in cell A1, but proceeds across the rows, starting at row 1 and continuing through to row 1,000,000, to the last sheet in the notebook.
Note
If you enable the column-wise or row-wise options, you should set the number of iterations to at least two. Otherwise, the notebook may be inaccurate.
To set the number of recalculation iterations
1 Click Format Notebook properties.
2 Click the Recalc settings tab.
3 Type a value in the # of iterations box.
Notes
If the notebook has a formula with a circular cell reference, the formula's cell address displays on the Recalc settings tab to the right of the # of iterations box. Elaborate formulas are sometimes deliberately constructed to contain circular cell references, because the formulas require multiple evaluations to attain an acceptable degree of accuracy.
If the notebook contains no circular cell references and the Natural option is enabled, the iteration count is ignored. For information on setting the recalculation order, see "To set the recalculation order."
Moving and copying formulas and referenced cells
Formula references change in a logical manner when you move cells that contain formulas or cells referenced by formulas. For additional information, see "Rules for moving formulas and referenced cells."
You can move formulas to any spreadsheet in a notebook. When you move a formula, the formula readjusts to calculate the cells relative to its new location. If you want the formula to calculate the cells it referenced before the move, you must set the formula to calculate absolute cell addresses. For additional information, see "Setting cell addresses."
You can copy the formulas you create in Quattro Pro. When you copy data by dragging and dropping the cells, the formulas adjust to correspond with their new positions. When you copy formulas with copy and paste, the formulas adjust to the new location if it includes values to use in the formula. For example, you can type +C1+C2 in cell C3. Then, if D1 and D2 contain values, when you copy the formula to cell D3, the formula adjusts to +D1+D2.
However, if you copy the formula to D3, and D1 and D2 are empty, you are alerted that the formula is using an empty cell and provided with an option to fix it. Quattro Pro will find the original data associated with the copied formula and make the reference absolute. A red-lined arrow points to the location where Quattro Pro "guesses" you want to get the data to use in the formula. If this location includes empty cells, Quattro Pro reports an error.
You can also have Quattro Pro check your cell references while you work.
To move a formula
1 Select a formula.
2 Click Edit Cut.
3 Select the cell in which to paste the formula.
4 Click Edit Paste.
Tip
You can also move a formula by selecting the formula cell, moving the cursor to the edge of the cell until the pointer changes to a four-way arrow, and dragging the formula to a new location.
To copy a formula
1 Select a formula.
2 Click Edit Copy.
3 Click the cell in which to paste the formula.
4 Click Edit Paste.
5 In the Cell reference checker dialog box (if appearing), click one of the following buttons:
Close-keeps the adjusted formula
Fix it-uses the original formula
Note
By default, when you copy formulas containing cell names, the cell names in the new location use absolute cell referencing. To force Quattro Pro to use relative cell referencing, precede the cell name with a tilde (~). For example, AGE would be an absolute cell reference, while ~AGE would be a relative cell reference. For more information about cell names, see "Naming cells."
Tip
You can also copy a formula by selecting the formula cell, moving the cursor to the edge of the cell until the pointer changes to a four-way arrow, and holding down Ctrl while dragging the formula to a new location.
To check cell references
1 Click Tools Settings.
2 Click General.
3 Enable the Cell reference checker check box.
Composing formulas
You can create a formula or edit an existing one using Quattro Pro's Formula Composer.
Depending on the view you choose, you can see up to three panes that provide information about the formula you are creating:
Outline pane-lets you examine the structure of a formula, edit parts of the formula, and trace cell references and cell names. The outline pane shows the hierarchy of the formula you have created. You can expand or collapse parts of the formula to focus on part of the formula. The outline can consist of several levels, with each level containing a different expression.
Spreadsheet function description pane-provides a brief description of the spreadsheet function you have selected in the outline pane. When you type a spreadsheet function (for example, @SUM) in the Expression edit field, the spreadsheet function description pane appears to the right of the dialog box.
Argument pane-provides entry fields for the spreadsheet function arguments. Parentheses around an argument indicate an optional argument. When you type a value for an optional argument, you must also enter values for all preceding optional arguments. Clicking to the left of an argument gives you a short description of each spreadsheet function argument.
For a list of keyboard shortcuts available in the Formula Composer dialog, see "Formula Composer keys."
To create a formula in the Formula Composer
1 Select a blank cell.
2 Click the Formula Composer button.
3 Enable one of the following options:
Standard view-displays all three panes (spreadsheet function description, outline, and argument)
Argument view-displays the outline and argument panes
Outline view-displays only the outline pane
4 Type a formula in the Expression box.
Notes
If an formula is incomplete or syntactically incorrect (for example, a spreadsheet function may be missing an argument), a red question mark icon in the outline alerts you to the error.
Expressions that cannot be expanded, such as values, display a small yellow circle to their left.
Tip
You can expand and collapse selected parts of the formula outline by clicking Expand and Collapse.
To edit a formula in the Formula Composer
1 Select a cell containing a formula.
2 Click the Formula Composer button.
3 Select the first expression in the outline on the left.
4 In the Expression box, make changes to the formula.
Tip
To edit only part of a formula, select another, lower expression in the outline.
Working with spreadsheet functions
Instead of composing formulas, you can use spreadsheet functions. Spreadsheet functions are a set of standard built-in formulas used to simplify complex calculations. Quattro Pro offers more than 500 built-in spreadsheet functions. These spreadsheet functions cover a range of calculations, including database, financial, engineering, and statistical calculations. A common spreadsheet function is @SUM, which adds the cell values you reference. For example, typing @SUM(A1..A4,B1) is equivalent to typing +A1+A2+A3+A4+B1.
A listing of available functions can be found in Quattro Pro Functions Help, which is located in the reference information section of the Quattro Pro online Help.
You can enter spreadsheet functions in a cell. As you type a function, the most likely function and its syntax appear at the bottom right of the screen. The function's arguments will appear on the application bar.
Spreadsheet functions can also be used to create formulas. In a formula, the left parenthesis is red until you type the right parenthesis. Then, they both turn green.
For additional information, see "Rules for entering spreadsheet functions," "Entering arguments in functions," and "Working with date functions."
To enter a spreadsheet function in a cell
1 Select a cell.
2 Click Insert Insert function.
3 Choose a category from the Function category list.
4 Choose a spreadsheet function from the Function list.
5 Click Next.
6 Type the arguments of the function in the relevant boxes; optional arguments appear within angled brackets (<>).
Notes
All spreadsheet functions begin with the at sign (@). When typing a spreadsheet function, you can omit the @ if it is not the first item in the formula.
Enclose arguments in parentheses.
To create a formula using a spreadsheet function
1 Select a cell.
2 Click Insert Insert function.
3 Choose a category from the Function category list.
4 Choose a spreadsheet function from the Function list.
5 Click Next.
6 In the Expression box, type the rest of the formula.
Notes
The pane on the left shows a breakdown of the formula.
To identify a missing parenthesis or bracket, double-click the cell, and move the insertion point through the formula. When you reach an unmatched parenthesis or bracket, the character turns red.
Tracing errors in formulas
Tracing errors in formulas lets you locate the source of calculation errors. If you are looking at a formula cell, you can see all cells that provide data to that formula. It can also show all formulas that use the data from a particular cell.
You can locate a circular cell reference, which is a formula that relies upon its own result to calculate the formula. For example, placing the formula +A1+A2 in cell A1 produces a circular cell reference.
Tracing cell precedents lets you view all cells that provide data to or influence a formula cell. All first-level precedent cells are outlined in blue. A first-level precedent cell provides data directly to the formula being traced. Once you have identified the first-level precedent cells, you can find the cells that influence them and repeat the process until you find the cell causing the error.
You can also trace cell dependents. When you trace dependents of a cell, you see all formulas that use the data from the selected cell.
Finally, you can trace formula cell references using the Formula Composer dialog box.
For additional information about tracing errors see "Guidelines for solving errors in formulas."
To locate a circular cell reference
1 Click Format Notebook properties.
2 Click the Recalc settings tab.
The circular cell reference displays to the right of the # of iterations box.
Notes
If the Circular cell reference indicator button displays on the application bar, the notebook contains a circular cell reference.
Elaborate formulas are sometimes deliberately constructed to contain circular cell references, because the formulas require multiple evaluations to attain an acceptable degree of accuracy.
To trace cell precedents
1 Select a cell containing a formula.
2 Click Tools Auditing Show auditing toolbar.
3 Click the Trace precedents button to display the first level of precedents.
4 Continue to click the Trace precedents button until no more precedents display.
To trace cell dependents
1 Select a cell.
2 Click Tools Auditing Show auditing toolbar.
3 Click the Trace dependents button.
4 Continue to click the Trace dependents button until no more dependents display.
To trace formula cell references
1 Select a cell containing a formula.
2 Click the Formula Composer button.
3 Click the cell reference in the outline pane.
4 Click the Follow formula button on the Formula Composer toolbar.
Tip
Click the Back button on the Formula Composer toolbar to return to the standard display.
Naming cells
Instead of referring to a cell by its coordinates, such as B10, you can assign it a name. Creating a cell name has several advantages. First, if you move the contents of a named cell, the name is still associated with the same data, regardless of the new coordinates. Second, referencing names in a formula makes the formula easier to understand. For example, +PRICE - COST is more intuitive than B15 - D8. As well, using names increases accuracy. If you mistype a name, Quattro Pro alerts you to the error. If you mistype coordinates, you operate on the wrong cells.
You can change the cells assigned to a name. You can also name cells using adjacent labels.
Quattro Pro can name cells automatically. You can create a two-column table that lists all of the named cells in a notebook with their corresponding coordinates. And, if you no longer want to identify a cell or block of cells by a name, you can delete that name.
For additional information about naming cells, see "Guidelines for naming cells."
To create a cell name
1 Select a cell.
2 Click Insert Name Name cells.
3 Type a unique name in the Name box.
4 Click Add.
Note
Before inserting a name, check the labels you plan to use. If there are duplicate labels or labels that duplicate existing names, you will overwrite the previous assignments. Quattro Pro lists existing names in the list box on the left side of the toolbar directly above the spreadsheet window.
To change the cells assigned to a name
1 Click Insert Name Name cells.
2 Choose a name from the Name list.
3 Type the new coordinates in the Cell(s) box.
4 Click Add.
To name a cell from an adjacent label
1 Select a label.
2 Click Insert Name Name cells.
3 Click Labels.
4 In the Direction area, enable an option to indicate the position of the cell being named in relation to the selected label.
For example, if the cell you are naming is below the label, enable the Down option.
Notes
Each label in the specified cells (up to 64 characters) is used as a name. Numeric values in the selection are ignored. Later changes to original labels do not affect the cell names.
Any leading or trailing spaces in a label are included in the name.
To name cells from labels automatically
1 Click Insert Name Name cells.
2 Click Generate.
3 In the Generate cell names dialog box, type the cells to name, including label cells, in the Cells box.
4 In the Create cell names area, enable any of the check boxes.
If you want to have the cells named at their intersections, 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.
Notes
Each label in the specified cells (up to 64 characters) is used as a name. Numeric values in the selection are ignored. Later changes to original labels do not affect the cell names.
For additional information about automatically naming cells, see "Guidelines for naming cells."
To create a table of named cells
1 Click Insert Name Name cells.
2 Click Output.
3 In the Name table dialog box, type in the Cells box the top-left cell of the cells where you want the table to display.
Notes
Make sure there is enough room for a two-column table, with one row for each name. Existing data in the cells used for the table is overwritten by the table.
A named cell table is not automatically updated. If you add, change, or delete cell names, you must re-create the table to reflect the changes.
To delete a cell name
1 Click Insert Name Name cells.
2 Choose a name from the Name list.
3 Click Delete.
Note
Although clicking Delete deletes the cell name from memory, the notebook data is not affected. Formulas that reference the named cell change so that they refer to cell coordinates instead.
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,
typing $A$1 makes both coordinates of address A1 absolute
typing $A1 locks the address into column A, but lets the row coordinate change
typing A$1 locks the address into row 1, but lets the column coordinate change
typing $A:A$1 locks the address into sheet A and row 1, but lets the column coordinate change
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.
Press F4 Cell reference
starting reference B6
1x $A:$B$6
2x $A:B$6
3x $A:$B6
4x $A:B6
5x $B$6
6x B$6
7x $B6
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.
To specify Type
Range of cells Address of top-left cell, followed by one or two periods and the address of the bottom right cell
Example: C3..D6 (refers to cells C3, D3, C4, D4, C5, D5, C6, and D6) F4.F11 (refers to cells F4 to F11)
Cells on another sheet Sheet name and a colon, then the cell reference
Example: B:B4
Cell in a different notebook Filename in brackets, the sheet name, then the cell reference [SALES]C:B5
Named cells Name
Example: EXPENSES
Cells in different places Separate each selection with a comma
Example: A2..A5,B7,D5..E12
3-D selection The sheet references first, followed by the cell coordinates
Example: A..D:A2..B5 (refers to cells on sheets A, B, C, and D)
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.
Operator Precedence
Parentheses ( ) 7
Exponents (^) 7
Negative, positive (-, +) 6
Multiplication, division (*, /) 5
Subtraction, addition (-, +) 4
Greater than or equal (>=) 3
Less than or equal (<=) 3
Less than, greater than (<, >) 3
Equal, not equal (=, <>) 3
Logical NOT (#NOT#) 1
Logical AND (#AND#) 1
Logical OR (#OR#) 1
Text operator (&) 1
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.
When moving formulas and referenced cells, you should be aware of the following rules:
If you move a formula without moving the cells it references, the references remain intact, regardless of whether they are absolute or relative.
If you move both a formula and the cells it references, the references do adjust. For example, if cell A2 contains the formula +A1+1, and you move A2 to B1, the formula still reads +A1+1. But if you move both A1 and A2, the formula reads +B1+1.
If you move a formula to another notebook without moving the cells it references, the two notebooks link so that the formula still references the cells in the original notebook. If you move the formula back to the first notebook, the links are removed.
If you move a cell without moving formulas that refer to it, formulas update to refer to the cell's new location, even if you specified the reference as absolute. For example, if you move A:B4 into A:B6 of a notebook named TAX, the formula's reference changes to [TAX]A:B6.
If you move a cell from within a range without moving the formulas that refer to the cell, the formulas no longer refer to the moved cell. However, if you move one of the coordinate cells (the upper-left and lower-right cells), the references to the cells expand or contract to reflect the new location. For example, if you define BILLS as cells B1..B7, and then move cell B7 to B12, BILLS changes to B1..B12.
If you move a coordinate cell into another notebook, references to the cell are not adjusted.
If you move an entire named or referenced selection of cells, the cell name or the reference in affected formulas is updated.
If you move data into a coordinate cell of a referenced area or into a single referenced cell, the formula containing the reference becomes invalid. References in formulas to the single cell or selection of cells are replaced with ERR, and cells that contain those formulas display ERR. If you name a selection of these cells, the coordinates in the cell names list display as ERR.
Rules for entering spreadsheet functions
Spreadsheet functions contain:
the name of the spreadsheet function (such as @SUM or @AVG)
arguments (the values, cells, or text strings on which operations are performed)
commas that separate multiple arguments
parentheses around the arguments
A spreadsheet function must follow these syntax rules:
A leading at sign (@), plus sign (+), or equal sign (=) must be typed at the beginning of the formula.
The @function can be typed in either uppercase or lowercase letters.
Multiple arguments must be separated with a semicolon or the argument separator specified by clicking Tools Settings International Punctuation.
Arguments must be typed in the specified order.
Optional arguments must be typed within angle brackets, <>. If you specify an optional argument, you must also specify all preceding optional arguments.
A space must not be typed between the at sign (@) and the function name.
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 forms:
an actual value, for example @SIN(1.571)
the coordinates of a cell containing a numeric value, for example @SIN(B5)
the name of a cell containing a single numeric value, for example @INT(TOTAL)
a formula resulting in a numeric value, for example @INT(B4*10)
another @function resulting in a numeric value, for example @INT(@PI)
a combination of forms, for example @ABS(@INT(C4)+35-TOTAL)
Entering cell arguments
Cells can be used as spreadsheet function arguments in a number of ways:
the coordinates of cells, for example @SUM(A1..B3)
the address of a single cell, for example @SUM(B3)
a cell name, for example @SUM(JANUARY)
a combination of these in a list, for example, @SUM(JANUARY,C15..D25,F10)
Entering string arguments
A string value can be used as a spreadsheet function argument in the following ways:
an actual string, in double quotes, for example @PROPER("ACME Company")
the address of a cell containing a label, for example @PROPER(G13)
the name of a cell containing a label, for example @PROPER(COMPANY NAME)
a formula resulting in a string, for example @LOWER(+MONTH&"Sales")
another spreadsheet function resulting in a string, for example @LENGTH(@PROPER("ACME Industries"))
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:
Argument Year range
00-99 1900-1999
100-199 2000-2099
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, however, 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 2-D.
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:
If ERR displays in a cell instead of formula results, the formula contains an erroneous calculation. ERR also displays when data is missing or a cell address is incorrect.
If a function displays ERR, it may contain an unmatched parenthesis. To identify a missing parenthesis or bracket, double-click the cell, and move the cursor through the formula. When you reach an unmatched parenthesis or bracket, the character turns red.
If a cell does not calculate a formula, Quattro Pro may be interpreting the formula as a label. For example, Quattro Pro recognizes phone numbers and dates as labels and does not try to calculate them. When you enter a formula that might be interpreted as a label, type a plus sign (+) before the formula. For example, the following would be interpreted as formulas:
+202-555-3670
+12/12/94
If NA displays in a cell, some of the necessary information is not available. This could occur, for example, when a formula is linked to another source and the notebook has not been updated.
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
Names can be up to 63 characters long.
You can use any keyboard characters (A to Z, 0 to 9, spaces, punctuation marks, and some special characters such as %). Accented characters such as ┬, or ─, are also allowed if they are available on your keyboard.
Uppercase and lowercase letters are equivalent; for example, INCOME is treated the same as income. Names always appear in uppercase letters in formulas.
Names cannot be the same as a valid cell address. For example, you can't name a cell E30, or CAT3.
Names can define ranges of cells which overlap each other. For example, the following names are acceptable:
HOTEL B3..B7
TRANS C3..C7
MEALS D3..D7
TOTAL B3..D7
Names cannot contain only numeric characters. For example, 1234 is not a valid name.
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. For example, if you select A1..C4 in the next figure and enable the first two check boxes in the Create cell names area, five names are created: Jan., B2..B4; Feb., C2..C4; Housing, B2..C2; Utilities, B3..C3; and Food, B4..C4.
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:
