Contents
Chapter :Lesson 4: Entering Worksheet Formulas: What Are Formulas? 2
What Are Formulas? Page 2
Entering a Formula Pages 2
Step-by-Step 4.1 3
Order of Evaluation 4
Step-by-Step 4.2 5
Editing Formulas Pages 6
Step-by-Step 4.3 7
Comparing Relative, Absolute, and Mixed Cell References Pages 7
TECHNOLOGY CAREERS 9
Step-by-Step 4.4 9
Creating Formulas Quickly Pages 10
Using the Point-and-Click Method 10
Step-by-Step 4.5 11
Using the Sum Button 12
EXTRA FOR EXPERTS 13
Step-by-Step 4.6 13
Previewing Calculations Pages 15
Step-by-Step 4.7 17
Showing Formulas in the Worksheet Page 18
TIP 18
Calculating Formulas Manually Pages 18
Step-by-Step 4.8 19
Lesson 4: Entering Worksheet Formulas: Summary 20
Lesson 4: Entering Worksheet Formulas: Vocabulary Review 22
Lesson 4: Entering Worksheet Formulas: Review Questions 22
TRUE / FALSE 22
WRITTEN QUESTIONS 22
FILL IN THE BLANK 23
Lesson 4: Entering Worksheet Formulas: Projects 23
PROJECT 4–1 23
PROJECT 4–2 24
PROJECT 4–3 24
PROJECT 4–4 25
PROJECT 4–5 26
Lesson 4: Entering Worksheet Formulas: Critical Thinking 27
ACTIVITY 4–1 27
ACTIVITY 4–2 28
Chapter :Lesson 4: Entering Worksheet Formulas: What Are Formulas?
What Are Formulas? Page
One of the main advantages of Excel is that you can use numbers entered in cells to make calculations in other cells. The equation used to calculate values based on numbers entered in cells is called a formula. Each formula begins with an equal sign (=). The results of the calculation appear in the cell in which the formula is entered. The formula itself appears in the Formula Bar. For example, if you enter the formula =8+6 in cell A1, the value 14 appears in the cell, and the formula =8+6 appears in the Formula Bar when cell A1 is the active cell, as shown in Figure 4–1.
FIGURE 4–1 Formula and formula results
Entering a Formula Pages
Worksheet formulas consist of two components: operands and operators. An operand is a constant (text or number) or cell reference used in a formula. An operator is a symbol that indicates the type of calculation to perform on the operands, such as a plus sign (+) for addition. Table 4–1 shows the different mathematical operators you can use in formulas. Consider the formula =B3+5. In this formula, the cell reference B3 and the constant 5 are operands, and the plus sign (+) is an operator. This formula tells Excel to add the value in cell B3 to the value 5. After you finish typing a formula in a cell, you enter it by pressing the Enter key or the Tab key or by clicking the Enter button on the Formula Bar.
TABLE 4–1 Mathematical operators /OPERATOR / OPERATION / EXAMPLE / MEANING /
+ / Addition / B5+C5 / Adds the values in cells B5 and C5
− / Subtraction / C8−232 / Subtracts 232 from the value in cell C8
* / Multiplication / D4*D5 / Multiplies the value in cell D4 by the value in cell D5
/ / Division / E6/4 / Divides the value in cell E6 by 4
^ / Exponentiation / B3^3 / Raises the value in cell B3 to the third power
TABLE 4–1 Mathematical operators
Bookmark Title:
Top of Form
Bottom of Form
In editing mode, each cell reference in a formula appears in a specific color. The corresponding cell in the worksheet is outlined in the same color. You can change a cell reference in a formula by dragging the outlined cell to another location in the worksheet. You can also change which cells are included in a reference by dragging any corner of the colored outline to resize the selected range.
Step-by-Step 4.1
1. Open the Formula.xlsx workbook from the drive and folder where your Data Files are stored.
2. Save the workbook as Formula Practice followed by your initials.
3. Click cell C3. You'll enter a formula in this cell.
4. Type =A3+B3 and then press the Enter key. The formula result 479 appears in the cell. Cell C4 is the active cell.
5. In cell C4, type =A4−B4 and then press the Enter key. The formula result –147 appears in the cell. Cell C5 is the active cell.
6. In cell C5, type =A5*B5 and then press the Enter key. The formula result 13166 appears in the cell. Cell C6 is the active cell.
7. In cell C6, type =A6/B6 and then press the Enter key. The formula result 18 appears in the cell. Compare your results to Figure 4–2.
TIP
You can type cell references in uppercase (A1) or lowercase (a1) letters.
FIGURE 4–2 Formulas entered in worksheet
8. Save the workbook, and leave it open for the next Step-by-Step.
Bookmark Title:
Top of Form
Bottom of Form
Order of Evaluation
Formulas can include more than one operator. For example, the formula =C3*C4+5 includes two operators and performs both multiplication and addition to calculate the value in the cell. The sequence used to calculate the value of a formula is called the order of evaluation .
Formulas are evaluated in the following order:
1. Contents within parentheses are evaluated first. You can use as many sets of parentheses as you want. The innermost set of parentheses is evaluated first.
2. Mathematical operators are evaluated in the order shown in Table 4–2.
3. If two or more operators have the same order of evaluation, the equation is evaluated from left to right. For example, in the formula =20−15−2, first the number 15 is subtracted from 20, then 2 is subtracted from the difference (5).
TABLE 4–2 Order of evaluation /ORDER OF EVALUATION / OPERATOR / SYMBOL /
First / Exponentiation / ^
Second / Positive or negative / + or −
Third / Multiplication or division / * or /
Fourth / Addition or subtraction / + or −
TABLE 4–2 Order of evalution
Step-by-Step 4.2
1. Click cell D3, and then type =(A3+B3)*20. This formula adds the values in cells A3 and B3, and then multiplies the result by 20.
2. Press the Tab key. The formula results in the value 9580, which appears in cell D3. Cell E3 is the active cell.
3. In cell E3, type =A3+B3*20. This formula is the same as the one you entered in cell D3, but without the parentheses. The lack of parentheses changes the order of evaluation and the resulting value.
4. Press the Enter key. The formula in cell E3 results in the value 6901. This differs from the formula results in cell D3 because Excel multiplied the value in cell B3 by 20 before adding the value in cell A3. In cell D3, Excel added the values in cells A3 and B3, and then multiplied the sum by 20.
5. Save the workbook, and leave it open for the next Step-by-Step.
EXTRA FOR EXPERTS
If you start typing an entry that matches another cell entry, which happens in Step 1 after you type A and again after you type B, the AutoComplete features displays the complete contents of the existing entry in ScreenTip. If you want to insert the complete entry, press the Enter key. If not, continue typing.
Editing Formulas Pages
If you attempt to enter a formula with an incorrect structure in cell, Excel opens a dialog box that explains the error and provides a possible correction. You can accept that correction or choose to correct the formula yourself. For example, if you enter a formula with an opening parenthesis but no closing parenthesis, a dialog box appears, as shown in Figure 4–3, indicating that Excel found an error and proposing a correction that adds a closing parenthesis to the formula. Click Yes to accept the proposed correction. Click No to see a description of the error in another dialog box, and then click OK to return to the formula. You can correct the formula by editing it directly in the cell or by clicking in the Formula Bar.
FIGURE 4–3 Formula error message
Although Excel checks the formula for the correct structure, it does not check the formula for the correct values or cell references. If you discover that you need to make a correction, you can edit the formula. Click the cell with the formula you want to edit. Press the F2 key or double-click the cell to enter editing mode or click in the Formula Bar. Move the insertion point as needed to edit the entry. Then, press the Enter key or click the Enter button on the Formula Bar to enter the formula.
Step-by-Step 4.3
1. Click cell E3. The formula is displayed in the Formula Bar.
2. In the Formula Bar, click after = (the equal sign).
3. Type ( (an opening parenthesis). You will intentionally leave out the closing parenthesis.
4. Press the Enter key. The dialog box shown in Figure 4–3 appears, indicating that Excel found an error and offers a possible correction.
5. Read the message, and then click No. You will correct the error yourself. A dialog box appears, describing the specific error Excel found, as shown in Figure 4–4.
FIGURE 4–4 Formula error description message
6. Read the message, and then click OK.
Bookmark Title:
Top of Form
Bottom of Form
7. Move the insertion point in the Formula Bar between the 3 and the *.
8. Type ) (a closing parenthesis).
9. Press the Enter key. The value in cell E3 changes to 9580.
10. Save the workbook, and leave it open for the next Step-by-Step.
Comparing Relative, Absolute, and Mixed Cell References Pages
Excel has three types of cell references: relative, absolute, and mixed. A relative cell reference adjusts to its new location when copied or moved to another cell. For example, when the formula =A3+A4 is copied from cell A5 to cell B5, the formula changes to =B3+B4, as shown in Figure 4–5. How does Excel know how to change a relative cell reference? It creates the same relationship between the cells in the new location. In other words, the formula =A3+A4 in cell A5 instructs Excel to add the two cells directly above it. When you move this formula to another cell, such as cell B5, Excel uses that same instruction: to add the two cells directly above the cell with the formula. Notice that only the cell references change; the operators remain the same.
FIGURE 4–5 Relative cell references
Absolute cell references do not change when copied or moved to a new cell. To create an absolute cell reference, you insert a dollar sign ($) before the column letter and before the row number. For example, when the formula =$A$3+$A$4 in cell A5 is copied to cell B7, the formula remains unchanged, as shown in Figure 4–6.
FIGURE 4–6 Absolute cell references
TECHNOLOGY CAREERS
Engineers use Excel worksheets to perform complex calculations in areas such as construction, transportation, and manufacturing. For example, Excel worksheets are used to fit equations to data, interpolate between data points, solve simultaneous equations, evaluate integrals, convert units, and compare economic alternatives.
Cell references that contain both relative and absolute references are called mixed cell references . When formulas with mixed cell references are copied or moved to another cell, the row or column references preceded by a dollar sign do not change; the row or column references not preceded by a dollar sign adjust to match the cell to which they are moved. As shown in Figure 4–7, when the formula =A$3+A$4 is copied from cell A5 to cell B7, the formula changes to =B$3+B$4.
FIGURE 4–7 Mixed cell references
Step-by-Step 4.4
1. Click cell D3. The formula =(A3+B3)*20 (shown in the Formula Bar) contains only relative cell references.
2. Drag the fill handle to cell D4 to copy the formula from cell D3 to cell D4.
3. Click cell D4. The value in cell D4 is 17420, and the formula in the Formula Bar is =(A4+B4)*20. The operators in the formula remain the same, but the relative cell references change to reflect the new location of the formula.
4. Click cell D5, type =$A$3*($B$3–200) and then press the Enter key. The value in cell D5 is 19458. The formula contains absolute cell references, which are indicated by the dollar signs that precede the row and column references.
5. Copy the formula in cell D5 to cell E6. The value in cell E6 is 19458, the same as in cell D5.
6. Click cell D5 and look at the formula in the Formula Bar.
EXTRA FOR EXPERTS
You can press the F4 key to change a cell reference from a relative reference to an absolute reference to a mixed reference with an absolute row to a mixed reference with an absolute column and back to a relative reference.
Bookmark Title:
Top of Form
Bottom of Form
7. Click cell E6 and look at the formula in the Formula Bar. The formula in cell D5 is exactly the same as the formula in cell E6 because the formula you copied from cell D5 contains absolute cell references.
8. Click cell E4, type =$A$4+B4 and then press the Enter key. This formula contains both relative and absolute cell references. The value in cell E4 is 871.
9. Copy the formula in cell E4 to cell E5, and then click cell E5. The relative cell reference B4 changes to B5, but the absolute reference $A$4 stays the same. The value in cell E5 is 589.
10. Copy the formula in cell E5 to cell D6. The relative cell reference B5 changes to A6, but the absolute reference $A$4 stays the same. The value in cell D6 is 1262.
11. Click cell A8, and then enter your name. Save, preview, print, and close the workbook.
Creating Formulas Quickly Pages
So far, you have created formulas by typing the formula or editing an existing formula. You can also create formulas quickly by using the point-and-click method and the Sum button.