A formula in Quattro Pro can be as long as 254 characters and may consist of any of the following parts:
• Value: Numeric (19.5) or string ("hello")
• Cell address: B5, A3 .. DI2, or a block name (THIS_CELL)
• Function: @SUM, @STD, @COUNT, and so on
• Operator: +, -, *, >, =, and so on
• File name: Such as [MYFILE.WQl], to create links
• Comments: Optional, to add a comment at the end of the formula
Together, the parts of a formula produce a result, and it is that result which Quattro Pro retains as the value for the formula's cell.
For example, if you enter the formula 5*2 into cellA2, Quattro Pro automatically calculates and displays the result of 10. Whenever you refer to this cell in another formula, such as +A2, or a command, such as Edit-Value, the result, 10, will be used in the calculation.
You have already learned that a cell's contents are not the same as the cell's display. Now you have an intermediate rule to remember:
• If the cell contains a formula, its result is the final con-tents of the cell.
In the example above, the result of 10 is, in this sense, the con-tents of cell A2.
CH.4
• You can use upper-. upper-. case or lowercase letters in the address.
Mter you have entered the formula into a cell, all the letters in it are made uppercase.
INCLUDING VALUES
The formula 2+3 contains two numeric values, 2 and 3, and an operator, +. The result, 5, is numeric. You can include any numeric values in a formula. Just remember to include only valid characters, which are limited to the decimal point and a trailing percent sign if you want to enter a number as a percent.
A value may also be a string value. The simplest string formula consists of a single string value, such as:
+"Hello, I'm"
The beginning + signifies a value or formula, and the text is enclosed in quotation marks.
It is important to remember that numeric and string values can never exist as such in the same formula, just as you cannot mix text and numbers in a cell entry.
You can, however, convert text that looks like a number into an actual number, and then use that in a numeric formula. You can also convert a number into a string and use that in a string formula. Either way, the result of a formula can be either numeric or string, not both.
INCLUDING CELL ADDRESSES
A formula can refer to other cells in the current spreadsheet, as well as to cells in any other spreadsheet in memory or on disk.
It is this expansive capability that gives Quattro Pro its real power.
There are several ways to refer to one or more cells in a for-mula: type the address, point to the cell, or use a block name . TYPING THE CELL ADDRESS
You can refer to a cell in a formula simply by typing its ad-dress. For example, you could enter the formula:
25+A3-B2+ 12
If the values 10 and 6 were in cells A3 and B2, respectively, the result of the formula would be 41. Remember that if the for-mula begins with a cell address, you must preface the address with a +, -, or an opening parenthesis:
+A3+25-B2+ 12 (A3+25-B2+12)
POINTING TO THE ADDRESS
An easier method of including an address in a formula is pointing. You can simply move the cell selector to the cell you want, and its address will be entered into your formula. To use the pointing method, the cursor must be at the end of the input line and follow a valid formula operator.
As an example, let's enter a formula using this technique. Fol-low these steps:
1. Enter the value 10 in cell A3.
2. Enter 6 in cell B2.
3. Move the cell selector to cell AI, and type 25+ to begin the formula. Note that the mode indicator displays Value.
4. Move the cell selector down two rows to cell A3. Notice that the mode indicator now shows that the spread-sheet is in Point mode, and that your formula on the input line now looks like this:
2S+A3
If you have a mouse, you can simply click on the cell you want, and its address will be placed in your formula.
5. Your cell selector is still on cell A3. Type - (minus) to continue the formula.
6. The cell selector returns to cell AI, and the mode in-dicator displays Value again.
CH.4
7. Point to cell B2.
8. Type + to continue the formula.
9. Type 12, and then press .J to complete the formula.
The input line shows 25+A3-B2+12
The result of 41 appears on the display.
REFERRING TO A BLOCK OF CELLS
A block of cells is any rectangular set of contiguous cells. You will frequently refer to blocks of cells when using various Quat-tro Pro commands, such as Print, Copy, or Fill, and also when you are working with functions in formulas.
You describe a block by specifying two of its diagonally op-posite corner addresses, most typically its upper-left and lower-right corners. When specifying a block, you include two periods between the two addresses.
For example, the reference B3 .. D7
refers to the block of cells that has cell B3. at its upper-left corner and D7 at its lower-right. The block contains 15 cells, 5 cells in each of the columns B, C, and D.
You could also refer to this block with any of the three other pairs of corners: D7 .. B3, D3 .. B7, or B7 .. D3. However, the upper-left and lower-right corners are the standard frame of reference.
Just as you point to a single-cell address when writing a for-mula, you can also point to a block of cells. Move the cell selec-tor to one corner of the block, press the period key, and then point to the remaining cells in the block. If you are using a mouse, you canjust point and drag to highlight the block of cells.
You can try this method now, using the Mileage spreadsheet we created in Chapter 2. We'll use the @SUM function to total a group of numbers (this function is discussed along with the others later in the chapter).
• You can specify . . the block using any of the other refer-ences, such as BIO .. B5, and the result will be the same.
1. Recall the Mileage spreadsheet.
2. Move the cell selector to the bottom of the numbers in column B (Miles Driven), cell B12.
3. Type @SUM( to begin the formula (be sure to include the opening parenthesis). The mode indicator will dis-play Value .
4. Point to the cells you want to sum by moving the cell selector to the top of the column, cell B5. The mode indicator will display Point, and your formula will look like this:
@SUM(8S
5. Press the period key once to anchor the corner of the block at cell B5. This is the beginning of the block, and you will extend the block from this cell. The for-mula in the input line will now look like this:
@SUM(8S .. 8S
If you pointed to the wrong cell by mistake, you can press Escape to unanchor the block. Then move the cell selector to the cell you want and press the period key to anchor the block there.
6. Point to the remaining cells in the column until you have highlighted the block B5 .. B10. The formula will now include this newly defined block:
@SUM(8S .. 81 0
7. Type) (a closing parenthesis) to finish the formula, and then press~. The result of the formula in cell B12 is 2065.
8. Repeat these steps to create this formula in cell C10:
@SUM(CS .. C1 0) The result is 63.9.
Along with typing the address or pointing to the cells, another way to specify a cell within a formula is by using a block
CH.4
Ir:ill
When you want a formula to refer to a block name, instead of typing the name, you can select it from the Names menu, displayed by pressing F3.name. A block name is simply a name you specify to describe the address of a single cell or block of cells.
For example, using the Edit-Names-Create command, you could specify the name MYCELL for cell BIg. Thereafter, when-ever you need to refer to cell BIg, you could use the name MYCELL. The following formulas would be equivalent:
20*B19 20*MYCELL
Formulas are easier to write when you use block names be-cause you don't have to remember the exact cell address. They are also easier to read because the block name appears in the formula in place of the address it represents.
Using block names can be very helpful for many spreadsheet operations. They can be used in any Quattro Pro command that requests a block of cells, for printing, copying, moving, data input, and so on. The use of block names is described in detail in the next chapter.