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%
Search JabSto ::




Custom Search