These exercises build on the Example1 worksheet created in Lesson 1. If you do not have access to that worksheet, you should begin by creating the worksheet as shown in the figure in step 1.
Recall that our worksheet Example1 computes the total sales for a six month period (see figure below). In the Lesson 2 activities, we will expand this table to include sales from three separate sales regions and then compute
- six-month totals for each region,
- totals from all regions for each month, and
- an overall total sales figure
We begin by changing the column heading to reflect our intended addition of data for sales in three regions. To do this we simply click in the appropriate cell (B2 in our example), then edit the text. The figure below illustrates.
Notice in the figure that we need to expand the available space for text in column B to accommodate the new label. To do that, we simply place the cursor at the boundary between column B and C in the column label row so that it becomes a double arrow icon, as illustrated in the figure. Then we just drag to the right to expand the column width.
The following figure shows more text and numerical data entered into our example worksheet. Notice that columns C and D have been widened (as was column B in Step 1) to accommodate the label text in row 2. Notice also that the newly entered numerical data has been formatted as currency (review Lesson 1 if necessary for this).Now we wish to compute the six-month sales totals for Regions 2 and 3. We could enter formulas in a way similar to the way we entered the formula to compute the total in column B (in Lesson 1). However, there is a much easier and less error-prone way to accomplish this.
Note that the formulas for cells C10 and D10 are very similar to the formula in cell B10. The calculation to be performed is the same except for the column in which it occurs. The spreadsheet makes it easy to exploit this similarity. We can simply duplicate the formula in cell B10 within cells C10 and D10, with appropriate changes to reflect the change in column. This process is called formula replication, and it is one of the most important and fundamental spreadsheet operations.
To accomplish the required formula replication (to adjacent cells to the right), we first select the cell where the formula resides, then drag to the right selecting the "target" cells. This is illustrated in the figure below.
Once the source cell and target cells are selected as in the previous figure, it is an easy task to replicate the formula. With the appropriate cell selection active, we choose the Fill/Right commands under the Edit menu, as illustrated below.
The following figure illustrates the results of the formula replication described above. Cell D10 is selected there so you can observe the formula as it was "copied" to that cell. Notice that the basic structure of the formula is exactly the same as the original formula in cell B10, but the cell references have been changed to reflect the move from column B to column D.These changes in cell references are what makes such formula replication possible. The cell addresses in the original formula (= B3 + B4 + B5 + B6 + B7 + B8) are called relative addresses (this is the default reference mode -- we'll discuss the alternative mode in Lesson 4). This means that the formula in which they appear can be copied to another location with the new cell references having the same relative relationship to the new formula's home cell as did the original references to the original formula home cell.
Hence when a formula is moved two cells to the right, all relative cell addresses in that formula will have their column label increased by two. If we moved a formula four cells downward, the relative cell addresses would have their row number increased by 4, and so on.
Study this example carefully to make sure you understand the important principle of replicating formulas employing relative cell addresses.
Next, we'll add formulas to calculate the total monthly sales for all three regions. The following figure illustrates entering the appropriate formula for computing the January total.
Mimic the procedure given in Steps 2-4 for replicating the formula in cell B10 to cells C10 and D10 to replicate the formula just entered in cell E3 to cells E4 through E8. Use the Fill/Down command under the Edit menu to accomplish this.Now repeat this process to replicate the formula in cell D10 to cell E10 to compute the grand total of all sales.
The completed worksheet should have the following figures in it. Chec k your work against these numbers and correct any errors.
*** End of Lesson 2 ***