Copying Formulas and Functions

In many cases, you'll find that you need the same formula or function in several different cells. For example, in the spreadsheet shown in Figure 10.16, I really need to sum columns B, C, and D and rows 4, 5, 6, and 7. I can easily sum C4.C7 and then D4.D7 by clicking in C8 and then clicking the Function button and repeating the procedure in D8. However, if I had 12 months of data, even that process would be tedious.

Figure 10.16. This spreadsheet needs sums in row 8 and column E.

You can save time by copying formulas and functions, and you copy formulas and functions the same way you copy any other spreadsheet information. Refer to "Copying Information" in Chapter 4, "Editing in a Spreadsheet," for details. When you copy a formula or function, the results you get depend on whether you use relative or absolute cell addresses in your formulas or functions.

When you use relative cell addressingthe default in Quattro ProQuattro Pro adjusts cell references as you copy formulas or functions. Referring to Figure 10.16, when I copy the formula stored in B8 to C8, Quattro Pro inserts the formula @SUM(C4..C7) in C8, even though the formula in B8 is @SUM(B4..B7). In many cases, you'll be able to use the default of relative cell addressing.

When you use absolute cell addressing, Quattro Pro does not adjust cell references as you copy formulas or functions. When would you use absolute cell addressing? Suppose that you have a spreadsheet like the one in Figure 10.17 that compares sales by region. I've stored each region's percentage of total sales in row 9, using a formula that divides each region's salesstored in B7, C7, and D7by total salesstored in E7. The cell address for the total sales in each formula must be E7. The total sales cell reference must remain unchangedabsoluteto calculate each region's percentage of the total sales.

Figure 10.17. Use absolute cell addressing when you intend to copy a formula and one or more cell references in the formula should remain constant.

Suppose that I use relative cell addressing and store the formula +B7/E7 in B9. When I copy the formula to C9 and D9, Quattro Pro will adjust both cell references in the formula and the formulas in C9 and D9 will be wrong. However, if I use absolute addressing when I reference cell E7 in the formula, Quattro Pro will not adjust E7 in the formula when I copy the formula.

To use absolute cell referencing, you precede each portion of a cell address that you want to remain unchanged with a dollar sign ($). As you can see from the Input Line in Figure 10.18, I placed a dollar sign before both the column letter and the row number when referring to cell E7; when I copy the formula from B9 to C9, Quattro Pro changes only the first portion of the formula and uses cell E7 in the second part of the formula.

Figure 10.18. An absolute cell address contains a dollar sign before its column letter, row number, or both.

Tip

You can use a combination of relative and absolute cell addressing in a cell reference to force a portion of the cell reference to remain constant while the other part adjusts when you copy the formula or function. It is helpful to use a combination of relative and absolute cell addressing when you need a formula that always refers to the values in a specific row, but the values in the columns must change (or vice versa).

The Absolute Minimum

Spreadsheet programs are very powerful tools when you need to calculate values In this chapter, you learned how to calculate using raw numbers and cell references. You also learned how to:

• Create formulas on your own, using the Formula Composer, and using Quick Math.

• Use functions to sum or average a group of numbers.

• Copy formulas and functions using absolute and relative cell addresses to ensure accurate calculations.

In the next section of this book, we move into more advanced Quattro Pro topics; in Chapter 11, "Graphing Data," you'll explore creating graphs from Quattro Pro data.