UNIT 3—INTRODUCTORY MICROSOFT EXCEL



Unit 3—Introductory Microsoft Excel

Lesson 4—Worksheet Formulas

Objectives

• Enter and edit formulas.

• Distinguish between relative, absolute, and mixed cell references.

• Use the AutoSum button and the point-and-click method of entering formulas.

• Preview a calculation.

• Display formulas in the worksheet.

• Perform immediate and delayed calculations.

Teaching Materials

• Learner text

• Data files from the Data Files for Students drop-down menu on the Instructor’s Resource CD-ROM

• PowerPoint presentation from the PowerPoint Presentations drop-down menu on the Instructor’s Resource CD-ROM

• Solutions to Step-by-Step exercises, review questions, and projects from the Solutions to Exercises drop-down menu on the Instructor’s Resource CD-ROM

• ExamView( test questions from the Test Bank & Test Engine drop-down menu on the Instructor’s Resource CD-ROM

• Grading rubrics and annotated solutions from the Additional Faculty Files drop-down menu on the Instructor’s Resource CD-ROM

Prepare

• Focus learners’ attention on the objectives for the lesson.

• Set up a projection system and show the PowerPoint presentation for the lesson, if desired.

• Make sure learners know how to access the data files for this lesson.

• Prepare questions from ExamView.

Technical Notes

Make sure all computers are connected to a functioning printer.

Lecture Notes and Teaching Tips

Formulas are one of the most useful and exciting aspects of Excel worksheets. It is in this lesson that learners begin to understand the powerful computing power of Excel. This lesson is relatively long in comparison to other lessons.

Assessment is important for this lesson. Formulas are an essential concept of understanding and using worksheets. It is difficult to imagine the use of spreadsheets without the use of formulas. You should be confident that all learners are comfortable with formulas before progressing to the next lesson.

What Are Formulas?

This section introduces learners to formulas and how they are entered correctly in a worksheet. Be sure to reiterate that the formula appears in the formula bar and the result of the calculation is the value that appears in the cell.

Quick Quiz

1. Which character identifies that the data entered in a cell is a formula?

A. #

B. \

C. =

D. ^

Answer: C

Structure of a Formula

This section defines operands and operators. Learners should memorize the operators listed in Table 4-1.

Order of Evaluation

The order of evaluation is an important concept when entering formulas in worksheets. You might want to write on the board one version of a formula with parentheses and one without to illustrate how they affect the way that Excel calculates the formula.

Quick Quiz

1. Which operation is evaluated first in a formula?

A. exponentiation

B. multiplication

C. addition

D. subtraction

Answer: A

2. When entered in a worksheet, the result of the formula =2*2+(12-8) is __________.

Answer: 8

Editing Formulas

You can edit formulas the same way you edit other data entered in a cell. In addition, Excel provides some troubleshooting tips if you enter a formula incorrectly.

Quick Quiz

1. True or False? The only way to edit a formula is to make changes to it in the formula bar.

Answer: False

2. True or False? If you forget the closing parenthesis in a formula, Excel automatically inserts it where it should go.

Answer: False

Relative, Absolute, and Mixed Cell References

This section discusses the use of cell references in formulas. Spend some time reviewing Figures 4-3, 4-4, and 4-5 with learners and, if time permits, illustrate the three types of references with more examples in an actual worksheet. Being able to copy formulas is one of Excel’s more powerful features, so it is important that learners understand the concept of cell references.

Quick Quiz

1. The formula =B$5*B$2 is an example if a(n) __________ cell reference.

Answer: mixed

2. True or False? An absolute cell reference does not change when the formula is copied or moved to a new cell.

Answer: True

Creating Formulas Quickly

This section explains how to build a formula by clicking cells that are operands within the formula. It also introduces the AutoSum feature, which is commonly used to sum a row or column of data.

Point-and-Click Method

Learners will find that it is usually quicker and easier to click a cell that is to be included in a formula rather than keying the cell address. Explain that the point-and-click method helps minimize data entry errors.

Using the AutoSum Feature

AutoSum is a commonly used feature in Excel, and it introduces the concept of functions. In Step-by-Step 4.6, make sure learners take time to examine the formula in the formula bar. Point out that “SUM” is actually the name of the function that Excel is executing on the range D6:D11. Functions are discussed in detail in Lesson 5.

Troubleshooting Tip

Make sure learners remember that, after they click the AutoSum button, they must press Enter to complete the calculation.

Quick Quiz

1. True or False? The point-and-click method is a less efficient way of referencing a cell in a formula.

Answer: False

2. Which of the following characters is on the AutoSum button?

A. Σ

B. S

C. +

D. ^

Answer: A

Previewing a Calculation

The Auto Calculation feature is handy for quickly determining a calculation on a range of data without having to enter a formula. Make sure learners can identify the status bar. If time permits, you might want to explain the various functions that appear on the Auto Calculation menu.

Quick Quiz

1. To perform an Auto Calculation, you right-click the __________ bar.

Answer: status

2. True or False? The result of an Auto Calculation appears in the formula bar.

Answer: False

Formula Helpers

This section discusses the option for showing formulas in cells rather than their results. It also explains the delayed calculation feature, which is most useful in large worksheets with many formulas that may take longer to recalculate when you make a change.

Showing Formulas on the Worksheet

If a worksheet contains a number of formulas, you might find it helpful to temporarily display the formulas in the cells rather than their results. This can help you identify formula errors.

Delayed Calculations

The delayed, or manual, calculation feature is designed for use in worksheets that contain a multitude of formulas. It also can be used when you want to control when the cell content changes from formulas to formula results.

Quick Quiz

1. True or False? When you select to display formulas in cells rather than formula results, the cells that do not contain formulas display a series of ##### symbols.

Answer: False

2. To start a delayed, or manual, calculation, press the __________ key.

Answer: F9

Discussion Questions

1. You have created a worksheet that contains data on your sources of income and expenses. What calculations might you want to perform on this data?

2. Why is it important to understand the order of evaluation when you enter formulas in a worksheet?

3. In the worksheet discussed in question #1 above, when might you use an absolute cell reference? Explain.

Key Terms

• Absolute cell reference: Cell reference that does not adjust to the new cell location when copied or moved.

• Formulas: Equation that calculates a new value from values currently on a worksheet.

• Mixed cell reference: Cell reference containing both relative and absolute references.

• Operand: Numbers or cell references used in calculations in the formulas of worksheets.

• Operator: Tells Excel what to do with operands in a formula.

• Order of evaluation: The sequence used to calculate the value of a formula.

• Point-and-click method: Constructs a cell formula in Excel by clicking on the cell you want to reference rather than keying the reference.

• Relative cell reference: Cell reference that adjusts to a new location when copied or moved.

Projects to Assign

• In Project 4-2, learners will enter formulas in a worksheet. They will then save, print, and close the workbook file.

• In Project 4-3, learners will enter formulas, use the AutoSum feature, set the manual calculation option, and apply the Currency format to data. They will then save, print, and close the workbook file.

• In Project 4-4, learners will enter formulas, use the AutoSum feature, copy formulas, and set the manual calculation option. They will then save, print, and close the workbook file.

• In Project 4-5, learners will enter formulas using the point-and-click method, use the AutoSum feature, and enter data to be calculated in the formulas. They will then save, print, and close the workbook file.

• In Critical Thinking Activity 4-1, learners create a worksheet. The created worksheet should contain the following formulas:

|Cell |Formula |

|C3 |=B3*.1 |

|C4 |=B4*.1 |

|C5 |=B5*.1 |

|D3 |=B3*.07 |

|D4 |=B4*.07 |

|D5 |=B5*.07 |

|E3 |=B3-C3-D3 |

|E4 |=B4-C4-D4 |

|E5 |=B5-C5-D5 |

The completed worksheet should appear similar to the following:

| |A |B |C |D |E |

|1 |DETERMINATION OF MONTHLY NET PAY |

|2 |Job Offer |Gross Pay |Income Tax |Social Security Tax |Net Pay |

|3 |Job 1 |$24,500 |$2,450.0 |$1,715.00 |$20,335.00 |

|4 |Job 2 |$26,600 |$2,660.0 |$1,862.00 |$22,078.00 |

|5 |Job3 |$27,100 |$2,710.0 |$1,897.00 |$22,493.00 |

• In Critical Thinking Activity 4-2, learners use the Help system to obtain a detailed explanation of the differences between absolute and relative cell references. The explanation can be accessed by using keywords such as absolute reference or relative reference. Learners then choose the result entitled About Cell and Range References.

Assess

Administer the ExamView test for Lesson 4.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download