Chapter 7 continued ...
Using F4 twice is annoying, but there is a way to avoid this repetitive task. Copy and paste the formula from the formula bar back to the formula bar, instead of from cell to cell.
For example: Cell A1 contains the formula =A$1. Select the text from the formula bar (that is, select the formula) and press Ctrl+C (copy). Leave the formula bar by hitting Esc or by clicking Enter or Cancel (click the √ or the X to the left of fx on the formula bar). Select another cell in the sheet and press Ctrl+V.
| |
Tip – Copy and paste a range of cells containing formulas without changing the relative referenceSelect a range of cells containing formulas. Press Ctrl+H to replace the = sign with the # sign. After pasting the cells in a different location, replace the # sign with the = sign. |
Nesting is the term for a formula within a formula.
For example:
In Cell A1, type the number 100. In Cell A2, type the number 200.
In Cell B1, enter the formula =SUM(A1:A2) (the result = 300).
In Cell B2, enter the formula =A2-B1 (the result = -100).
In Cell C1, enter the formula =IF(A1>A2,B1,B2).
When the formula in Cell C1 is calculated, the result depends on the values in Cells A1:B2.
In this case, the result in Cell C1 is –100.
In the figure above, note the formula in Cell D1. The IF formula links two formulas.
Excel’s method of nesting formulas is not particularly easy if you are interested in using complex formulas.
For example: For Cell E1, type =IF in the formula bar, and press Ctrl+A.
In the first edit box for the IF formula, select the relevant cells to enter the formula A1>A2.
Notice the Name box to the left of the formula bar. The Name box has changed to Paste Function. Click the little arrow on the right to open the list of formulas, and select the formula SUM. In the first line of the SUM formula, select Cell A1. In the second line of the SUM formula, select Cell A2. Click OK.
The IF box has disappeared. In the formula bar, click between the two right-hand parentheses and type a comma (,); this brings back the IF formula palette. Then type the formula A2-B1, and click OK.
This is clearly a tedious process. Chances are you will not manage to nest complex formulas by this method without lots of practice.
Copying and pasting a formula within a formula
This technique is easy. Copy and paste one formula into another by copying the first formula from the formula bar. Then select a new cell, click on the formula bar, and paste using Ctrl+V. See Copying a Formula, Relative and Absolute Reference; the technique is similar.
For example:
In Cell D1, enter the formula =SUM(A:A)
In Cell E1, enter the formula =SUM(B:B)
In Cell F1, enter the formula =SUM(D1+E1)
Copy and paste all the formulas into a single formula in a single cell, instead of three formulas in three cells. For Cell D1, select in the formula bar SUM(A:A) without the = sign. Press Ctrl+C and click the X to exit edit mode for the cell. In the formula bar for Cell F1, select the reference D1, and press Ctrl+V.