ࡱ> ikh!` `bjbj\\ e>>J%g h $$$%%h 3&&&&&'''j2l2l2l2l2l2l2$4h729 '''''2 &&2)))' & &j2)'j2))V*/@ /&& 9Q($=(j/ /|203v/T7(7/7 /$'')'''''22) '''3''''h h h $h h h $h h h  Unit 3Introductory Microsoft Excel Lesson 4Worksheet 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 Instructors Resource CD-ROM PowerPoint presentation from the PowerPoint Presentations drop-down menu on the Instructors Resource CD-ROM Solutions to Step-by-Step exercises, review questions, and projects from the Solutions to Exercises drop-down menu on the Instructors Resource CD-ROM ExamView( test questions from the Test Bank & Test Engine drop-down menu on the Instructors Resource CD-ROM Grading rubrics and annotated solutions from the Additional Faculty Files drop-down menu on the Instructors 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 Excels 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: CellFormulaC3=B3*.1C4=B4*.1C5=B5*.1D3=B3*.07D4=B4*.07D5=B5*.07E3=B3-C3-D3E4=B4-C4-D4E5=B5-C5-D5 The completed worksheet should appear similar to the following: ABCDE1DETERMINATION OF MONTHLY NET PAY2Job Offer Gross PayIncome TaxSocial Security TaxNet Pay3Job 1$24,500 $2,450.0 $1,715.00 $20,335.00 4Job 2$26,600 $2,660.0 $1,862.00 $22,078.00 5Job3$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  ! ) >  / D ~ 2=NVWw~  v.6;"""3#;#=#$$/%8%%%%%3&<&p&&&&I' h5 h6] jh h]h5\] h6 h5\hR$@Kd - Y l y G M  j " B -._ & FJ``_r\gXljuMXi!` !^`!$$i v ; wj_jBD !^`"!DNZfrt{-<LQ s 3!H!)"4"""2#>#S##M$!!`M$$$/%%%3&p&&I'''/(()***+ +$If  & Fh^h!I'`'* ++++++,,-,/,5,_,`,b,h,,,,,,-----.``J`K`M`N`P`Q`S`T`V````````````º­h'l0J CJmHnHuh0J CJjh0J CJU hCJh jh UU h6hCJaJ h5\h h51 + +++$Ifokd$$Ifl0 04 la +++"+$Ifokd$$Ifl0 04 la "+#+&+-+$Ifokd$$$Ifl0 04 la -+.+1+9+$Ifokd$$Ifl0 04 la 9+:+=+E+$IfokdH$$Ifl0 04 la E+F+I+Q+$Ifokd$$Ifl0 04 la Q+R+U+_+$Ifokdl$$Ifl0 04 la _+`+c+m+$Ifokd$$Ifl0 04 la m+n+q+{+$Ifokd$$Ifl0 04 la {+|+}+++++++++}}}}}}$If`1$^okd"$$Ifl0 04 la ++++A;;$Ifkd$$Iflֈ\$ Il %RF04 la++++,,$,,,$Iftkd$$Ifl40\ V04 laf4,,-,/,5,>,H,S,A;;;;;$Ifkd6$$Iflֈh$ Il %RF04 laS,_,`,b,h,q,{,;kd $$Iflֈ\$ Il %RF04 la$If{,,,,,,,;kd $$Iflֈ\$ Il %RF04 la$If,,,,,,;2  & Fh^hkd $$Iflֈ\$ Il %RF04 la$If and Range References. Assess Administer the ExamView test for Lesson 4.     Microsoft Office 2003 Lesson 4: Worksheet Formulas  PAGE 5 ,``I`J`L`M`O`P`R`S`U`V```````,1h/ =!"#$% E 01h/ =!"#$% P0 xx/ 01h/ =!"#$% E 01h/ =!"#$% P0 xx/ 01h/ =!"#$% E 01h/ =!"#$% P0 xx/ 01h/ =!"#$% / 01h/ =!"#$% $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If !vh55#v#v:V l0554a $$If!vh555%55R5F#v#v#v%#v#vR#vF:V l0555%55R5F4$$If!vh55V#v#vV:V l4055V4f4$$If!vh555%55R5F#v#v#v%#v#vR#vF:V l0555%55R5F4$$If!vh555%55R5F#v#v#v%#v#vR#vF:V l0555%55R5F4$$If!vh555%55R5F#v#v#v%#v#vR#vF:V l0555%55R5F4$$If!vh555%55R5F#v#v#v%#v#vR#vF:V l0555%55R5F4#@@@ NormalCJ_HaJmH sH tH F@F Heading 1 $@& 5:CJ$\B@B Heading 2 $@& 5CJ \V@V Heading 3$<@&5CJOJQJ\^JaJDA@D Default Paragraph FontVi@V  Table Normal :V 44 la (k(No List XOX BY-AFFP(^P`5OJQJ_HmH sH tH TOTBYdP%5;OJQJ_HmHnHsH tH uVOVTX-NI$da$#CJOJQJ_HmHnHsH tH uZO"ZS1$dxa$&5CJOJQJ_HmHnHsH tH uTO2TCT  $d )5CJ OJQJ_HhmHnHsH tH uLOBLS4 d"6OJQJ_HmHnHsH tH uRORRS3$da$"6OJQJ_HmHnHsH tH uFObFEBd`_HmHnHsH tH uZOrZS2$d<a$%5OJQJ\_HmHnHsH tH u*O* S5 Char5,O, S6 Char56ZO1Z A-Head Title 6CJOJQJ\]^JaJ8O8 B-Head TitleCJ.B@. Body Text4@4 Header  !:0@: List Bullet  & F4 @4 Footer  !.)@. Page Number<O< Num List!h^h`&O& Tip"5 ":I%%d.dudddeeee$@Kd -YlyGMj"B-._r\gX l j u M X i  v ;  wj_j!"'-39:E^{-<LQs3H)42>SM/3pI/ !!!" " """""""#"&"-"."1"9":"="E"F"I"Q"R"U"_"`"c"m"n"q"{"|"}"""""""""""""""##$#,#-#/#5#>#H#S#_#`#b#h#q#{############%%I%J%L%M%O%P%R%S%U%V%%%%%%0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y0y00!0!0!0!0(00(00(00("000!0!0!0y0!0!0!0!0y0!0(000!0!0!0!0(00(00(000!0!0!0!00!0!0!00 0 0 0 0 0 0 0 00 0 0 0 0 000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0000y0000y0000y0000y0000@0y00@0y0000& YYfffffffffffffffffffffffffffffffffffffffffffffiI'`_iDM$ ++"+-+9+E+Q+_+m+{+++,,S,{,,,` !"#$%&'()*+,-.1`Zaci!48@0(  B S  ?#$*+6-%5%J%J%L%L%M%M%O%P%R%S%U%V%%%%%6 8 48J%J%L%L%M%M%O%P%R%S%U%V%%%%%33333"#@d Yy!}""""#H%I%J%J%L%L%M%M%O%P%R%S%U%V%%%%%%Api8N F~} hh^h`OJQJo(h ^`OJQJo(h ^`OJQJo(oh pp^p`OJQJo(h @ @ ^@ `OJQJo(h ^`OJQJo(oh ^`OJQJo(h ^`OJQJo(h ^`OJQJo(oh PP^P`OJQJo(h ^`OJQJo(h ^`OJQJo(oh pp^p`OJQJo(h @ @ ^@ `OJQJo(h ^`OJQJo(oh ^`OJQJo(h ^`OJQJo(h ^`OJQJo(oh PP^P`OJQJo(i8N~}Ps                  'l@-".p!" " """""""#"&"-"."1"9":"="E"F"I"Q"R"U"_"`"c"m"n"q"{"|"""""""""""""""##$#,#-#/#5#>#H#S#_#`#b#h#q#{###########%%@+ 4%%`@``<@`@UnknownGz Times New Roman5Symbol3& z Arial;& Helvetica9Palatino?5 : Courier New;Wingdings"1h:Ʋ:,CC!4d7%7%2qHX(?2#UNIT 3 INTRODUCTORY MICROSOFT EXCELCustom Editorial ProductionsDR. R. Wayne Stewart   Oh+'0  @L l x $UNIT 3INTRODUCTORY MICROSOFT EXCEL Custom Editorial ProductionsNormalDR. R. Wayne Stewart2Microsoft Office Word@%@D>(@D>(՜.+,0 hp  Cincinnati, OhioC7%d $UNIT 3INTRODUCTORY MICROSOFT EXCEL Title  !"#$%&'()*+,-./012456789:<=>?@ABCDEFGHIJKLMNOPQRSTUVWYZ[\]^_abcdefgjRoot Entry FQ(lData 31Table;8WordDocumenteSummaryInformation(XDocumentSummaryInformation8`CompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q