Microsoft Office Tutorials and References

In Depth Information

**Copying Formulas**

Copying Formulas

3. Repeatstep2foreachargumentinthefunction.

As you enter the arguments, Excel updates the formula automatically.

4. Onceyou’vespecifiedavalueforeveryrequiredargument,clickOK.

Excel closes the window and returns you to your worksheet.

poWer Users’ CliniC

Functions That Return Arrays

A few exotic functions actually give you
multiple
results, so

rather than simply generating a single value, as a function

like SUM() would, these functions generate more than one

value. To use these functions properly, you need to create

a special type of formula called an
array formula
. (An array

is a group of numbers.) You can create an array formula

quite easily. Simply select all the cells that Excel will use to

display the results, type in the formula, and then end by

pressing Ctrl+Shift+Enter. It’s this final keystroke that

actually creates the array formula and links the cells together.

Usually, you’ll you see a different result appear in each cell.

Technically, when you create an array formula, you have

a single formula that’s shared between several cells. You

know that you have an array formula because the formula

appears in the formula bar with curly braces { } around it

whenever you move into any one of the linked cells.

However, you can’t edit the individual cells in the array formula.

(If you try, Excel warns you that you can’t change part of an

array.) To make a change to an array formula, you need to

select all the cells in the array, edit the formula, and then

press Ctrl+Shift+Enter to recreate the array formula.

Copying Formulas

Sometimes you need to perform similar calculations in different cells throughout a

worksheet. For example, say you want to calculate sales tax on each item in a

product catalog, the monthly sales in each store of a company, or the final grade for each

student in a class. In this section, you’ll learn how Excel makes it easy with
relative

cell references
. Relative cell references are cell references that Excel updates

automatically when you copy them from one cell into another. They’re the standard kind of

references that Excel uses (as opposed to absolute cell references, which are covered

in the next section). In fact, all the references you’ve used so far have been relative

references, but you haven’t yet seen how they work with copy-and-paste operations.

Consider the worksheet shown in Figure 17-12, which contains a teacher’s grade

book. In this example, each student has three grades: two tests and one assignment.

A student’s final grade is based on the following percentages: 25 percent for each of

the two tests, and 50 percent for the assignment.

The following formula calculates the final grade for the first student (Edith Abbott):

=B2*25% + C2*25% + D2*50%

The formula that calculates the final mark for the second student (Grace DeWitt) is

almost identical. The only change is that all the cell references are offset by one row,

so that B2 becomes B3, C2 becomes C3, and D2 becomes D3:

=B3*25% + C3*25% + D3*50%