Format View Window Help Excel Week 1 Instructions.txt st Uni Read pp. 103-127; 133, 137 paying special attention to the last paragraph on p103. j-ad Please take the time to read the aforementioned pages. THEY COV EVERY EXERCISE ASKS. e lom Do: --Worksheet 1 (p. 110) New Worksheet goi Sub --Drill 2 (p. 113) Insert and Delete Rows, Columns, Cells Ja --Drill 3 (p. 114) Format Numbers m ce --Drill 4 (p. 115) Cell Styles --Drill 5 (p. 116) Sparklines --Drill 1 (p. 119) Formulas --Drill 2 (p. 120) Functions I --Drill 3 (p. 121) Absolute and Relative Cell Reference --Worksheet 4 (p. 122) Simple Payroll --Worksheet 5 (pp. 122–123) Gradebook Red Orange Yellow Graan 2, 2016 7:00 PM S: วววว DRILL 3 WORKING WITH WORKSHEETS With ex1 drill2 open, practice moving within the worksheet and selecting cells Move with keystrokes 8. Tap HOME to move to the beginning of the row. 9. Press CTRL + END to move to the last cell with data. 10. Press CTRL + HOME to move to the beginning of the worksheet. Move by Scrolling 1. Click the area below the vertical scroll box to move down one window and then click above the vertical scroll box to move up one window. 2. Click the down arrow three times on the vertical scroll bar to move down three rows. 3. Click the right arrow on the horizontal scroll bar to move right one column. 4. Scroll left one column. 5. Drag the vertical scroll box up any distance, 6. Click the area to the right of the horizontal scroll box to scroll right one window. Move with Go To Command 7. Press CTRL + G and key C6. Click OK. Select Range 11. Select the range A4:F4: Click A4 and drag to F4. Click away from the range to deselect. Select Nonadjacent Ranges 12. Select the range A5:F5. Hold down the CTRL key and select A9:F9. Deselect. Select Rows, Columns, and All 13. Select column B: Click column B heading. 14. Select row 1: Click row 1 heading. 15. Select all cells in the worksheet: Click the Select All button. 16. Close the file without saving. Apply It Worksheet 1 New Worksheet DISCOVER 1. Key the worksheet shown in Figure 1.14. Format Tickets Sold in B2 on two lines. 2. Format cell A1 in 14-point bold; apply bold and center alignment to A2:D2. 3. Adjust the column widths to display all text as shown in Figure 1.14. 4. In D3, key the formula =B3*C3. Using the fill handle in D3, copy the formula to D4:D7. 5. Increase the number of tickets sold for the City Tour by 12. Change Regal to Regis. 6. Add two worksheets. Rename the sheet tabs as follows: Sheet!, Events; Sheet2, Registration; Sheet3, Program; Sheet4, Awards; and Sheets, Budget. 7. Rearrange the sheet tabs in alphabetical order. Change sheet tabs to a desired color. 8. Save as exl-wl and print. Close the file. Quick Print File/Print Print Shortcut: Click arrow on Quick Access Toolbar and add Quick Print A B D E 1 Summary Special Events Participation and Sales Report Tickets 2. Event Sold Price Total 3 Kick-off Breakfast 164 25 4 Delegates Luncheon 78 45 5 Dinner Banquet 223 70 6 City Tour 125 45 7 Regal Dinner Cruise 200 75 Figure 1.14 Worksheet 1 Source EXCEL 110 LESSON 1 CREATE WORKSHEETS DELETE ROWS, COLUMNS, AND CELLS Editing a worksheet often includes deleting unneeded cells, rows, or columns. The procedure to delete is very similar to inserting rows, columns, and cells. * Delete Cells Delete Delete Sheet Bows * Delete Sheet Columns U Delete Sheet Figure 2.5 Delete Rows or Columns Delete ? x To DELETE A SINGLE Row or COLUMN: Home/Cells/Delete 1. Click a cell in the row or column to be deleted and click the Delete drop-list arrow (Figure 2.5). 2. Click Delete Sheet Rows to delete a row or Delete Sheet Columns to delete a column. Shortcut: Click the row or column heading, right-click, and click Delete. To DELETE CELLS: 1. Select a cell or a range of cells to be deleted. Click the Delete drop-list arrow. 2. Click Delete Cells. 3. Choose Shift cells left or Shift cells up. Refer to Figure 2.6. Delete Shift cells left Shift cells up Entire row Entire column OK Cancel Figure 2.6 Delete Dialog Box INSERT DRILL 2 INSERT AND DELETE ROWS, COLUMNS, AND CELLS 1. Open the data file insert. Save as ex2-drill2a. 2. Insert a row immediately before row 4. Travel. Add the following entry in the new row 4. - Legal Fees 95332 97000 5. Insert two columns immediately to the left of column B. 2010. 6. In B2, key 2008, and in C2, key 2009. Bold and center align the column headings in the range B2:E2 7. Save your changes so far. 8. Delete columns B and C. 3. Select B1:C1; insert cells and choose to shift cells down. 4. Select A2; insert cells and choose to shift cells down. 9. Save as ex2-drill2b and close. WARE CAREER FOCUS Companies are investing large sums of money in the sophisticated automated workplace. Interestingly, training consultants report that only a small percentage of the functions of software are used by most people. If companies are to earn a return on investment, employers will be inclined to hire employees who possess expert levels of software expertise and who know how to maximize the value of software. Set a goal to learn the powerful functions of the software and look for smarter ways to accomplish tasks to save time and energy. Stockbyte/Getty Images EXCEL 113 LESSON 2 EDIT WORKSHEETS FORMAT NUMBERS Home/Number Formatting numbers is very easy when you use the number General 1 2 formats commands on the Number group on the Home tab. The following formats are available in this group: $ - % to.. Accounting (S): Displays fixed dollar sign at the left of the cell. Number Percent (%): Displays % sign. Comma (): Numbers display commas for ease in reading large numbers, e.g., 1,353,813. Decimal places: Click Increase or Decrease Decimals to select the number of decimal places to display. For more advanced formatting, click the Number Dialog Box Launcher to display the Format Cells dialog box. Click the Number tab to display additional formatting categories. For example, Figure 2.7 shows the Currency format (floating dollar sign that appears at the left of the first digit) and negative numbers displayed in red. Figure 2.7 Format Cells Dialog Box Torta Cell Pula NA Cry 4 Aug STATEMENT DRILL 3 FORMAT NUMBERS DISCOVER 1. Open the data file statement and save as ex2-drill3. 2. Format the number in B2 as Accounting. O decimal places: B3:06 as Comma, o decimal places; and C7 as Currency with dollar sign, o decimal places, and negative number formatted to display in red in parentheses. 3. Check the spelling on the worksheet and make the needed changes. 4. Resave as ex2-drills and close. Check Spelling Review/Proofing Spelling CELL STYLES Home/Styles/Cell Styles You can apply formats to worksheet cells just as you apply styles to text in a Word document. Applying a cell style results in consistent formatting and saves valuable time. Study Figure 2.8 for the various cell styles categories to Cell mark Good, Bad, or Neutral data; to identify Data and Model data types; to Styles format Titles and Headings; to apply Themed Cell Styles; and to apply Number Formats. Click Normal to remove cell styles. Nome 1 DISCOVER TO APPLY A CELL STYLE: 1. Select the cells to be formatted. 2. Click the arrow next to Cell Styles and click the desired style. Title Total Heading 1 Heading 2 cented 20. Modify Cell Style Home/Styles/Cell Styles Select the style and right-click. Click Modify and then Format. Make the desired changes. com Commal Figure 2.8 Cell Styles Gallery EXCEL 114 LESSON 2 EDIT WORKSHEETS DRILL 4 CELL STYLES 1. Open ex2-drill2b. Save as ex2-drill4. 2. Select Al:C1 and merge and center the title. Increase the row height of row 1 to 45.00 points and middle-align the text in the row. 3. Select Al and apply the Title cell style and the Accenti themed cell style. 4. Select the column headings A2:C2 and apply the Heading 2 cell style. 5. Select A3:C10 and apply the Calculation style. Select A3:A10 and apply the Normal style to remove the style. 6. Select B3:010 and apply the Comma [O] cell style. 7. Key the word Total in A11. Select A11:C11 and apply the Total cell style. 8. Modify the Calculation cell style by changing the font to blue and the font size to 10. Apply the modified cell style to cells B3:C10. 9. Change the theme to Foundry. Note the cells where themed cell styles were applied updated to the new theme. 10. Print; resave the file as ex2-drill4 and close. QUICK A Figure 2.9 to check the format. С B Expenses 1 2 2010 3 Salaries 198,736 4 Office Supplies 8,458 5 Legal Fees 95 332 6 Travel 7 Communication 999 8 Printing 1,089 9 Insurance 5,983 10 Utilities 6,991 11 Total 9,873 2011 250,789 8,547 97.000 9,105 1,089 1,369 5,977 7,988 Figure 2.9 Quick Check SPARKLINES A sparkline is a miniature chart (line, column, or win/loss) that displays in a cell. Note the chart is simple--it does not contain titles, legends, or axes. Users find sparklines helpful to show valuable information in little space. Line Column Win/Loss 1 Sparklines To INSERT SPARKLINES: Insert/Sparklines/Line, Column, or Win/Loss 1. Select the desired cell or range of cells where the sparkline is to display. 2. Follow the path above and select either the Line, Column, or Win/Loss chart . Create Spardines The Create Sparklines dialog box displays. The appropriate location range displays @ Data Range: 13:E5 3. Click in the Data Range box and then select the data range B3:E5 Ⓡ. Location Range: $F$3:$F$S 4. Click OK Choose the data that you want Choose where you want the sparkdines to be placed OK Canon Figure 2.10 Create Sparklines EXCEL 115 LESSON 2 EDIT WORKSHEETS DRILL 4 CELL STYLES 1. Open ex2-drill2b. Save as ex2-drill4. 2. Select Al:C1 and merge and center the title. Increase the row height of row 1 to 45.00 points and middle-align the text in the row. 3. Select Al and apply the Title cell style and the Accenti themed cell style. 4. Select the column headings A2:C2 and apply the Heading 2 cell style. 5. Select A3:C10 and apply the Calculation style. Select A3:A10 and apply the Normal style to remove the style. 6. Select B3:010 and apply the Comma [O] cell style. 7. Key the word Total in A11. Select A11:C11 and apply the Total cell style. 8. Modify the Calculation cell style by changing the font to blue and the font size to 10. Apply the modified cell style to cells B3:C10. 9. Change the theme to Foundry. Note the cells where themed cell styles were applied updated to the new theme. 10. Print; resave the file as ex2-drill4 and close. QUICK A Figure 2.9 to check the format. С B Expenses 1 2 2010 3 Salaries 198,736 4 Office Supplies 8,458 5 Legal Fees 95 332 6 Travel 7 Communication 999 8 Printing 1,089 9 Insurance 5,983 10 Utilities 6,991 11 Total 9,873 2011 250,789 8,547 97.000 9,105 1,089 1,369 5,977 7,988 Figure 2.9 Quick Check SPARKLINES A sparkline is a miniature chart (line, column, or win/loss) that displays in a cell. Note the chart is simple--it does not contain titles, legends, or axes. Users find sparklines helpful to show valuable information in little space. Line Column Win/Loss 1 Sparklines To INSERT SPARKLINES: Insert/Sparklines/Line, Column, or Win/Loss 1. Select the desired cell or range of cells where the sparkline is to display. 2. Follow the path above and select either the Line, Column, or Win/Loss chart . Create Spardines The Create Sparklines dialog box displays. The appropriate location range displays @ Data Range: 13:E5 3. Click in the Data Range box and then select the data range B3:E5 Ⓡ. Location Range: $F$3:$F$S 4. Click OK Choose the data that you want Choose where you want the sparkdines to be placed OK Canon Figure 2.10 Create Sparklines EXCEL 115 LESSON 2 EDIT WORKSHEETS To EDIT SPARKLINES: Sparkline Tools Design 1. Click the Sparkline Tools Design tab and browse the commands in each group (Figure 2.11). You will apply these in Drill 5. Type--Use to change sparkline type or edit data . Show-Display the various points noted and show markers @. Style-Select from a gallery of styles; change sparkline color and marker colors Sparkline Color Marker Color Ungroup Line Column Win Loss High Point First Point low Point Last Point Negative Points Markers Show 2 ER Data Sparkling 3 Grove type Figure 2.11 Edit Sparklines DRILL 5 SPARKLINES REQUESTS 1. Open the data file requests. Save as ex2-drills. 2. Select the range F3:F5. Click Insert and then click the Line sparkline. In the Create Sparklines dialog box, select B3:E5 as the data range. Click OK. 3. In F2, key the column title Quarterly Record. Use the Format Painter to copy the format of E2, and wrap text in cell F2. 4. Change the sparkline type to Column, then to Win/Loss, and back to Line. 5. Apply the Accent 5, Darker 50% sparklines style. 6. Click the Marker Color drop-list arrow and choose Standard Purple for the marker color, Green for the High Point, and Dark Red for the Low Point. 7. Resave as ex2-drills and close. Apply It Worksheet 2 Format Worksheet ADDRESS FILE 1. Open the data file address file and save as ex2-w2. Key your name in Al. Key information for at least five people you would add to your address file. Apply the Equity theme. 2. Merge and center the worksheet title; apply the 20% Accent style and then apply the Title style. Increase the row height to 45.00 points and middle-align the title. 3. Insert two rows above row 2; click the Insert Options button and select Format Same As Below. Key your full address in A2. Move cell phone data in All to A3. Merge and center both rows. Apply same accent cell style used in Al for new rows. 4. Apply Heading 4 style to A2. 5. Modify Heading 3 style by changing horizontal alignment to center. 6. Apply Heading 3 style to A3 and to A4:G4. 7. Change column heading Phone to Work Phone and click Wrap Text to display text on two lines. Insert a new column to the left of Work Phone. Key the heading Cell Phone on two lines. 8. Delete the column containing e-mail addresses. Change to the Module theme. 9. Check spelling, resave as ex2-w2, and close. DISCOVER Move Selected Cells 1. Select cells to be moved. 2. Point to the border and drag to the new location and drop. 2009 2008) EXCEL 116 LESSON 2 EDIT WORKSHEETS QUICK Use Figure 2.12 to check ex2-w2. Address File for Your Name Your Address, City, State ZIP Cell Phone X00K Call Pana Work Phone Street State First City ZIP Figure 2.12 Quick Cheek Worksheet 3 Format Worksheet SALES STATS 1. Open the data file sales stats and save as ex2-w3. Apply the Module theme. 2. Apply the Normal cell style to clear the formatting in range A1:A2. 3. Delete the blank row 3. Delete column B, ID Number. 4. Insert a blank row and key your name so it will appear in alphabetical order. Your sales figures are follows: 1458381, 1442321, 1331333, 1467928. 5. Brian Brooks has moved to another city; delete the row that contains Brian's stats. 6. The manager changed his mind about recording the ID. Insert a column before Qtr 1 and label it ID Number. Key the ID numbers from the data file. Your ID is 25-00-73. 7. In G3, key the column title Trend. Insert Line sparklines in cells G4:G8 to show the sales trends for the range C4:F8. Apply the sparkline style Accent 2, Darker 50%. Display the High Point and Low Point. Change the marker color to Green for the High Point and Red for the Low Point. 8. Format as follows: a. Row 1-Merge and center, Accent2 and Title cell styles, row height 39.00 points, middle-align text. b. Row 2-Merge and center, 40% Accent2 and Heading 3 cell styles, row height 24.00 points. C. Column headings (A3:63)-20% Accent5 and Heading 3 cell styles. d. Selected cells--Select A5:F5; press CTRL and select A7:F7. Apply the 40% Accent4 cell style. e. C4:F8-Format Comma [0] cell style. 9. Check the spelling, resave, and print. Close the file. QUICK Use Figure 2.13 to check ex2.w3. Sales Report 1 Trend Prepared by: David Slaughter 3 Representative ID Number Qtra Otr2 4 Leung, Kelly 25-12-93 1,458,921 2,459.982 Marion, Abigail 25-41-03 2,318,926 1,298,932 6 Uriz, Michael 25-45-37 1,242,387 1,198,792 7 West, Samantha 25-23-22 1,258,976 1,294.729 8 Student's Name 25-00-73 1,458,38: 2,442,321 9 10 Student's name should be included in the proper location Otr3 Otr 4 2,458,882 1,438,921 1,279.363 1,258,921 2,298,297 1,268,293 1,308,789 3,327,894 1,331,333 1,467,928 (7V Figure 2.13 Quick Cheek EXCEL 117 LESSON 2 EDIT WORKSHEETS LESSON 3 Insert Formulas and Functions OBJECTIVES Create formulas in a worksheet • Insert functions in a worksheet • Use relative and absolute cell references CREATE FORMULAS A formula is a set of instructions to perform calculations in a cell. All formulas begin with an equals sign (=) and consist of a number or cell reference (e.g., A5) and a calculation operator that indicates what to do. In the formula =A5+C2, the values in A5 and C2 are added. The cell reference may be a cell address, such as D4, or a range of cells (D4:D8). Study the calculation operators in Table 3.1; substitute 10 for B4 and 2 for D4. Operation Addition (4) Subtection (-) Division Muttiplication (! Percent (95) Exponentiation (™) Example -B4+04 -B4-04 -B4/D4 -B4D4 --B4.8% --B443 Meaning Adds the values in B4 and D4 Subtracts the value in D4 from the value in B4 Divides the value in B4 by the value in D4 Multiplies the values in B4 and 4 Calculates 8% of the value in B4 Increases the value of B4 to the third power Result 10+2 -12 10-2-8 1012-5 10.2 - 20 10.8% -8 102 1000 Table 3.1 Formula Operations In the worksheet titled Invoice shown in Figure 3.1, the formula =A3*C3 is displayed in the active cell, D3 O. An efficient way of entering this formula is to key the =; then point to A3 and click; key the *; then point to C3 and click. Note the formula displays in the active cell and the formula bar. Tap ENTER or click the Enter button to the left of the formula bar to complete the operation. SUM X fo =A3*C3 A B с D 1 Invoice 2 Quantity Description 2 WX-1098 color copier 10 279 TX internal modem 5 10 FX-19 laser printer Unit Price Total 725.99 =A3*C 59.99 599.99 1 Figure 3.1 Formula in Active Cell and Formula Bar If several calculation operators are included in one formula, Excel performs the operations in the following order of precedence: (1) negation, e.g., -1; (2) percent; (3) exponentiation; (4) multiplication and division, whichever comes first left to right; and (5) addition or subtraction, whichever comes first left to right. In the formula =6+4/2, division has precedence over the addition, so the result is 8-not 5. To change the order of precedence, enclose the part of the formula to be calculated first in parentheses. The formula =(6+4)/2 would result in the answer 5 because the addition is performed first within the parentheses. EXCEL 118 SON 3 INSERT FORMULAS AND FUNCTIONS D3 k =A3*C3 c с D A B 1 Invoice Total To COPY A FORMULA: 1. Point to the fill handle at the bottom right corner of the active cell until the crosshair appears (Figure 3.2). 2. Drag down through the range to copy the formula to the remaining cells. Alternate method: Click the Copy command; select the range of cells where the formula is to be copied; and click the Paste command. Unit Price 725.99 59.99 599.99 2 Quantity Description 3 2 WX-1098 color copier 4 10 279 TX internal modem 5 10 FX-19 laser printer 1451.98! 2 Figure 3.2 Drag Fill Handle to Copy Formula FORMULAS DRILL 1 ENTER FORMULAS Open the data file formulas. Save as ex3-drilli. On Sheet1, in D2 (Difference column), enter the formula =C2-B2. Remember to key =, point to C2 and click; then key , point to B2, and click. In E2 (% Change column), key the formula =D27C2. • Select D2:E2. Copy both formulas by dragging the fill handle through the remaining expenses. 5. Click Sheet2. In E20, enter the appropriate formula for Quantity times Unit Price. 6. Copy the formula to the remaining items being ordered. 7. Rename Sheet1 as % Change and Sheet2 as Purchase Order 8. Save the file and leave open for the next drill. INSERT FUNCTIONS Formulas/Function Library Formulas may also include functions, which are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or syntax. Each function has a specific structure: (1) equals sign (=), (2) function name, and (3) arguments. If the argument consists of more than one part, separate each part with a comma. Table 3.2 shows common Excel functions and their arguments. Function SUM Syntax =SUM(A5:A12) =SUMA5,A12) =AVERAGE(C5:F5) -COUNT(A2:A20) AVERAGE COUNT MIN MAX -MIN(A2:A20) --MAX(A2:A20) --PMTtratenperpvfv.type) Meaning Adds all cells from AS through A12 Adds only A5 and A12 Averages all values in the range CS:F5 Counts the number of cells that contain numbers in the range A2 A20 Returns the smallest number in a set of values Returns the largest number in a set of values Calculates the payment for a loan based on constant payments and a constant interest rate The 12% rate is divided by 12 to determine monthly payment; 36 is the number of months, and $20,000 is the amount of the loan. (The last two parts of this argument are optional) PMT -PMT(12%/12,36,20000) Table 3.2 Function Arguments fic Excel has many functions, which are arranged in categories in the Function Library group (Figure 3.3). Begin with the AutoSum category, click the arrow next to each category, and browse the various functions available. Σ AutoSum 12 Logical Lookup & Reference Recently Used - A Text Lo Math & Trig Insert Function Financial - Date & Time - More Functions Function Library Figure 3.3 Function Library Group EXCEL 119 SSON 3 INSERT FORMULAS AND FUNCTIONS Σ Sum 2 Average ТІР Count Numbers Max me/Editing e frequently used functions, Ruding the SUM function, are ated on the Home tab, To INSERT A FUNCTION: Formulas/Function Library 1. Click in the cell where the result of the function is to display O. 2. Click the arrow next to a category, e.g., AutoSum, and click the desired function, e.g., Sum (Figure 3.4). Figure 3.5 shows the function displayed in F4 with a scrolling marquee marking the cells used in the function. 3. Tap ENTER or click the Enter button. Min More Functions... 3 Figure 3.4 AutoSum Functions SUN X =SUM( 84:14) B D 1 Brentwood Music Tunes 2 Quarterly Sales 3 Genre Qtr1 Qt2 Qtr3 Qt4 Total 4 Alternative Rock 50321 51333 49343 $2093 SUM( 34:41 5 Blues 43901 50387 31093 34949 SUMinumberl. (number 6 Classical 37999 38083 39388 41279 Figure 3.5 SUM Function Displayed in F4 Insert function Search for a function present value Or selectategory: Recommended Select a function Note: If you cannot find the function you want to use, click More Functions or click Insert Function in the Function Library group. The Insert Function dialog box displays (Figure 3.6). In the Search for a function box, key the desired function name. Click the Help on this function hyperlink to go directly to Help information on the desired function. Note the description provided for the selected function. VALUE PV OPV py NPER CUMPMT HPV[rate value vake2...) Returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values) Figure 3.6 Insert Function Dialog Box DRILL 2 INSERT FUNCTIONS E TIP 1. Save ex3-drill1 as ex3-drill2. 2. On the Purchase Order sheet, click D28. Key Total and apply the Total cell style to D28:E28. 3. Click in E28 and insert the SUM function to sum (E20:E27). 4. On the % Change sheet, key Total in A9 and apply the Total cell style to A9:E9. Change the B1 and C1 headings to text data by keying an apostrophe in front of the heading ('2010 and '2011). 5. Click in B9 and insert the SUM function to sum (B2:B8). Drag the fill handle to copy the formula in C9. you do not add the apostrophe the dates 2010 and 2011, cel will attempt to include them the SUM function's range of alues. 6. In D9, compute the total difference, and in E9, compute the total % change. Hint: For D9, copy formula from D8; for E9, copy formula from E8. 7. Apply the Percent cell style to E2:E9. 8. Resave as ex3-drill2, print, and close the file. EXCEL 120 ESSON 3 INSERT FORMULAS AND FUNCTIONS USE CELL REFERENCES Using cell references in formulas rather than using the actual values in cells is essential because when a cell value changes, each formula or function that contains the cell reference is automatically recalculated. Relative and absolute cell references are shown in Figure 3.7. A relative cell reference changes relative to the cell from which the formula is being copied. Note that when the formula in D4 is copied down, =B4*C4 changes to =B5*C5; the row 4 cell references adjust B D for their new location in row 5. 1 Commission Report for Product 23-934 An absolute cell reference 1 2 Commission Rate: does not adjust if copied and Total is designated with the dollar Name Units Prices Sales Commission symbol (S) before the column 4 Davis, Bonnie 175 $ 250 =B4°C4 =D4*$B$2 and the row, e.g., $B$2. 5 Ferguson, George 250 250 =B5*C5 =D5*$B$2 When the formula in cell E4 6 Kline, Mary 110 250 =B6 C6 =D6*$B$2 is copied, the reference to cell $B$2 stays the same in each Figure 3.7 Relative and Absolute Cell References new row. Note that the D4 reference in E4 is a relative cell reference and changes when copied to ES. 5% TIP 3 After keying a cell reference, tap F4 and the $ symbols will be added to the cell reference. Repeat tapping F4 to cycle to the desired cell reference. BAKERY DRILL 3 USE ABSOLUTE AND RELATIVE CELL REFERENCES 1. Open the data file bakery. Save as ex3-drill3. 2. In G4, enter the formula for calculating the total due from each customer. Use the cell references in the Price List (A14:B19). Hint: Include all cells in the range B4:F4 in the formula because you will copy the formula down. The formula will include a relative and absolute cell reference. 3. Copy the formula in G4 to the other customers. 4. In B12, enter the function to determine the total coffee cakes sold. Be sure the scrolling marquee marks B4:B11; drag to select that range if necessary. 5. Copy the formula in B12 to columns C through G. Apply the Accounting [O] cell style to cell G12 6. Resave as ex3-drill3, print, and close the file. QUICK Use Figure 3.8 to check the worksheet. G4 $ =B4*$B$15+C4*$B$16+D4$B$17+E4*$B$18+F4*$B$19 B С G 1 Bakery Fundraiser 2 Cakes Pies 3 Customer Coffee Cake Carrot Cake Pound Cake Pecan Pie Egg Custard Total Due 4 Ron Atkins 32.00 5 Gail Barr 2 30 00 6 Andrew Dennison 37.00 7 Vanessa King 60.00 8 Chien Lee 1 50.00 9 Tom Robinson 1 2 55.00 10 Wanda Walden 1 1 45.00 11 Jim Zakkak 61 00 12 Total 5 2 4 5 $ 370 13 14 Price List 15 Coffee Cake $20.00 16 Carrot Cake 25.00 17 Pound Cake 25.00 18 Pecan Pie 15.00 19 Egg Custard 12.00 4 Figure 3.8 Quick Check EXCEL 121 LESSON 3 INSERT FORMULAS AND FUNCTIONS Apply It Worksheet 4 Payroll Worksheet EL SIMPLE PAYROLL 1. Open the data file simple payroll and save as ex3-w4. Rename Sheet1 as June 1-5. 2. Select column headings B-F; right-click, select Column Width, and key 5. 3. Enter the following formulas and copy to the remaining employees: a. Total Hours-In G4, enter a function to add the hours worked M-F. b. Gross Pay-In 14, key a formula that multiplies total hours and hourly rate. c. Stock Deduction-In J4, enter an absolute cell reference to the cost of Stock Option 1 (B16) in the table shown in A15:B18. Stock Option numbers display in parentheses after each employee name. Enter the correct Stock Option cost for each employee. d. Net Pay-In K4, key a formula that subtracts the Stock Deduction from Gross Pay. e. Total-In G13, enter a function to add total hours; copy the formula to 113 and K13. 4. Format the following cells using cell styles: Al-Title, merge and center; A2-Heading 4, merge and center A3:K3--Heading 3; H4:K4, 113, and K13-Accounting, 2 decimals H5:K12-Comma, 2 decimals; A15:B18-Note A13:K13--Total 5. Resave as ex 3-w4, print, and close the file. QUICK Use Figure 3.9 to check the worksheet. A 1 Payroll 2 Week of June 1-5, 20- Total Hourly Gross 3 Employee Mon Tue Wed Thu Fri Hours Rate Pay 4 Menntt Terry (1) 8 8 7 5 4 32 $600 $ 192.00 5 Newsome Dianna (2) 8 8 & 3 40 625 250 00 6 Pankasemsuk Ginsh (1) 8 7 8 8 4 35 700 245 00 7 Skaggs Greg (3) 5 5 8 4 8 30 750 225 00 8 Wakefield Demek (2) 8 7 4 8 8 35 625 218 75 9 Jackson Jolynn (1) 6 8 8 8 8 38 750 285 00 10 Ingram, Faye (2) 65 6 7 355 10.50 37275 11 Ballard Barry (1) 7 75 4 8 34 5 600 207 00 12 Aycock Melody (3) 8 8 8 8 40 7.10 284.00 13 Total 320 $2,279.50 14 15 Stock Option Type Cost 16 10 17 15 18 25 Stock Deduction Net Pay 10.00 $ 182 00 15.00 235 00 1000 235 00 25 00 200.00 15 00 20375 10.00 275 00 15 00 357 75 10.00 197 00 25 00 259 00 $ 2,144.50 Figure 3.9 Quick Check Worksheet 5 Gradebook 1. Open the data file gradebook and save as ex3-w5. Enter the following functions from the AutoSum category of the Function Library: G4Compute the average on the four tests; copy the formula to each student. C16-Average scores for Test 1 (C4:C14). C17-Count the number of students taking Test 1 (C4:C14). C18-Find the minimum score made on Test 1 (C4:C14). C19-Find the maximum score made on Test 1 (C4:C14). Select C16:C19 and use the fill handle to copy the four formulas in one efficient step to D16:F19. GRADEBOOK LESSON 3 INSERT FORMULAS AND FUNCTIONS EXCEL 122 Function Arguments - FASE - President's Lagical test A true President's st" Value false 2. In H4, insert the IF function from the Logical category to determine who qualifies for the President's List (95 or better). In the cell you will see the three parts of the argument separated by commas. 2x Figure 3.10 shows the function arguments. Enclose text state- ments in quotation marks. Key two quotation marks ("") to dis- play a blank for a false statement. The result of H4 is blank because Figure 3.10 Function Arguments the average did not meet the criteria. Copy the formula in H4 to the remaining stu- dents. Two students qualify for the President's List. Adjust the width of column H. 3. In 14, key an IF statement that would determine students who are exempt from the final exam (98 and above). The word Exempt should display if eligible. Copy the formula to the remaining students. One student is exempt from the final. 4. Check the spelling. Resave as ex 3-w5, print, and close the file. Worksheet 6 Edit Gradebook DISCOVER 1. Open ex 3-w5 and save as ex 3-w6. 2. Make H4 the active cell. Click the formula bar and edit the formula so that students with averages of 94.5 or higher may be eligible for the President's List. Copy the for- mula to the remaining students. 3. Double-click 14 and edit the formula so that students with an average of 94.5 or higher may be exempt from the final exam. Three students are eligible for the President's List and exempt from the final exam. 4. In L4:M8, key the table at right; you will use it to compute OF the final grade in 14. 60 D 5. In J3, key Final Grade on two lines. Use the Format Painter 70 C button to copy the formats needed for J3. 80 B 90 A 6. In J4, key the following vertical lookup function: =VLOOKUP(G4,SL$4:$M$8,2). Copy the formula to the remaining students. Note: The logical test is G4, the student average in G4 is then located in the table L4:M8, and the value in the second column is recorded in J4. The reference to the table was keyed as an absolute cell reference because it was copied down to the other students. 7. Resave as ex 3-w6, print, and close the file. Home/Clipboard Format Painter Click the cell whose formats you want to copy, click Format Painter, and click cell to format. Worksheet 7 Advanced Payroll Worksheet ADVANCED PAYROLL 1. Open the data file advanced payroll and save as ex3-w7. Display the June 1-5 worksheet. 2. Write an IF function for 13 that will compute net pay for those employees working 40 hours per week and those working overtime at 1.5 times their regular hourly rate. The argument for the IF statement is =IF(logical_test, value_if_true,value_if_false). a. Logical test: G3