Question

Edit and copy a formula with mixed references. Edit the formula in cell D5 to show D$4 instead of "D4." The formula should multiply one plus the percentage value in cell D4 by the current rate on the Fleet sheet ($H5). With an absolute reference to row 4 and column H on the Fleet sheet, you can copy the formula down the column (Figure 2-105). FIGURE 2-105 MIXED REFERENCE IN THE EDITED FORMULA Copy the formula in cell D5 to cells D6:D19. Select cells D5:D19 and drag the Fill pointer to copy the formulas to cells E5:E19. Click cell E6. The formula is adjusted to use the percentage value in cell E4 in place of cell D4. Note also that the reference on the Fleet sheet ($H6) is adjusted to show the correct row.

Answer 1

Original Formula in D5:

`= (1 + D4) * Fleet!$H5`

Modified Formula in D5 (with D$4):

`= (1 + D$4) * Fleet!$H5`

To edit and copy a formula with mixed references in Microsoft Excel, you can follow these step-by-step instructions:

1. Open your Excel worksheet where you have the formula in cell D5 that you want to edit.

2. Edit the formula in cell D5 to include mixed references. In this case, you want to replace "D4" with "D$4" and keep the reference to cell H5 on the Fleet sheet as it is. The formula should multiply one plus the percentage value in cell D4 by the current rate on the Fleet sheet ($H5). Here's the modified formula in cell D5:

3. Copy the modified formula in cell D5 to cells D6:D19. You can do this in several ways:

- Select cell D5, then hover your cursor over the small square at the bottom-right corner of the selected cell (the fill handle). Your cursor will change to a black cross.

- Click and drag the fill handle down to cell D19 to copy the formula to the desired range.

4. Release the mouse button to complete the copy.

5. Select cells D5:D19 to ensure they are all selected.

6. Drag the Fill pointer from cell D5 to cell E5. This action will adjust the formula to use the percentage value in cell E4 instead of cell D4, and the reference on the Fleet sheet ($H5) will be adjusted to show the correct row based on the relative position of the formula.

7. Release the mouse button to copy the formulas to cells E5:E19.

Now, you have successfully edited and copied the formula with mixed references from cell D5 to cells D6:D19 and then to cells E5:E19, as requested. The formulas in these cells will adjust based on the row and column references as you copy them to different cells in your worksheet.