VIEWS: 879 PAGES: 63 POSTED ON: 11/7/2009
Anatomy of a Spreadsheet Student Name: Period: Date: Student Activity ANATOMY OF A SPREADSHEET Directions: Take notes as your teacher demonstrates and explains each of the spreadsheet components listed below Component Workbook Description Spreadsheet Cell Cell address Cell range Column Row Page 1 of 63 Anatomy of a Spreadsheet Student Name: Period: Date: Student Activity Directions: Use cell addresses from the Invoice spreadsheet above to identify examples of each component Component Cell Description Cell address Cell range Column Row Page 2 of 63 Formula Notes Student Name: Period: Date: Student Activity FORMULA NOTES Directions: Record notes below as your teacher explains formulas and basic functions 1. Cell data is classified according to its intended purpose Describe each: Label: Value: Formula: 2. What is a spreadsheet formula? Example: 3. 4. 5. What symbol will prepare a cell for entry of a formula? What symbol will tell spreadsheet software not to treat cell data as a value? What are operators? What are the symbols for each operator? ____Addition ____Multiplication ____Subtraction ____Division Page 3 of 63 Formula Notes Student Name: Period: Date: Student Activity 6. What is the Order of Operations? Which operation would be performed first in the following equation? =(A8+C9)/(H8-L9) The value for C7 is 2; C8 is 4; and F4 is 2, What is the result of the equation =C7+C8*F4? Why? What is the result of the equation =(C7+C8)*F4? Why? Page 4 of 63 Fun with Formulas Student Name: Period: Date: Student Activity FUN WITH FORMULAS Student Directions: Practice writing formulas for the following 10 statements and answer the questions that follow on the next page Practice writing formulas for the items below 1 Add the range of cells A1:A5 2 Divide C7 by A3 3 Multiply A9 by the result of C10-B8 4 Subtract B8 from the sum of cells C1 through C5 Divide A2 by the result of A6 multiplied by the sum of cells A1 through A5 Subtract A1 from the result of C8 divided by A7 Calculate the average of cells A10, B10, and C10 Multiply the sum of the range of cells from A7 through A10 by the sum of the range of cells from B2 through B5 Divide the result of C4 divided by A6 by 2 Subtract C10 from the sum of the cells in the range A1 through A6 Page 5 of 63 5 6 7 8 9 10 Fun with Formulas Student Name: Period: Date: Student Activity 11. Open a new blank spreadsheet and enter the data in Table 1 (below) in the exact cell locations as shown. Try out your formulas by keying them in Cells D1:D10 of the spreadsheet you just created. Check the accuracy of your work against the answers in Column D of Table 2 (below). Table 2 If you entered the formulas correctly, you should achieve the results in Column D. Table 1 Enter these numbers in a new spreadsheet 12. What items gave you trouble? 13. What tips can you share that make formula writing easy? 14. How might you use a spreadsheet to verify your formulas? 15. What should you do if you can’t get a formula to work correctly? Page 6 of 63 Guided Practice Spreadsheet Formatting Student Name: Period: Date: Teacher Demonstration GUIDED PRACTICE: SPREADSHEET FORMATTING Directions: In this exercise, you will follow along with your teacher to enter and format data in a spreadsheet and take notes. You will answer the questions and complete the tasks below: • • • • • Format a header Use a simple formula Copy data Classify cell data Apply font styles • • • • • Adjust column and row size Wrap text Indent cell data Format values Add a border 1. 2. Open a new spreadsheet Set a header with your name, the current date, and the title of this activity using procedures outlined by your teacher. Take notes on the required steps in the space below. 3. 4. Key the title: In Cell A1, key INVOICE in all caps and bold Is the title classified as a label or a value? Explain 5. Enter the column headings (in bold font): • In Cell A3, key Item # • In Cell B3, key Quantity • In Cell C3, key Unit 6. Adjust column widths to fit contents: • In Cell D3, key Description • In Cell E3, key Unit Price • In Cell F3, key Amount Page 7 of 63 Guided Practice Spreadsheet Formatting Student Name: Period: Date: Teacher Demonstration 7. Beginning in Row 4, Column A, key the remaining data as indicated in the table below: Quantity 11 4 4 8 Ea Doz Ea Pkg Unit Description PDA Flash Drive Scanner Web cam Unit Price 129.99 25.99 99.99 35 Amount Item # 4376 8976 8632 7793 8. Merge and center the data in the title cells (in initial caps and bold) 9. Give an example of a cell range used in this spreadsheet 10. Which columns contain values and which contain labels? 11. Readjust all column widths. Why is this step necessary? 12. Edit the text: Change the # symbol in Cell A3 to the word Number 13. Center the contents of Cell A3 (as in Step 8) 14. Wrap text – Feature that aligns multi-line text within a cell. Wrap the text in Cell A3 Page 8 of 63 Guided Practice Spreadsheet Formatting Student Name: Period: Date: Teacher Demonstration 15. What is the effect of using the wrap text feature on a cell? 16. Format unit price for 2 decimals 17. Enter a formula in the last column to calculate the Amount (unit price x quantity) and format the data as currency. 18. Copy the formula to all cells in the Amount column. 19. Enter and indent the word Total in bold and initial caps below the last item in Column A. 20. Enter a formula to add the amounts in the last column and place the result in Cell F8. Format the data as currency. 21. Add an accounting border (single line above and double line below) to Cell F8. 22. Save and submit your work according to teacher directions. Page 9 of 63 Spreadsheet Rubric Student Activity SPREADSHEET RUBRIC Student Name: Document: Due Date: Excellent (20) Header/Footer Yes All required formulas are present and correct All label cells are formatted correctly All value cells are formatted correctly 0 Yes 80% of the formulas are present and correct 80% of the label cells are formatted correctly 80% of the value cells are formatted correctly 2-3 Satisfactory (17) No Less than 80% of the formulas are present and correct Less than 80% of the label cells are formatted correctly Less than 80% of the value cells are formatted correctly >3 Poor (12) Formulas Label Format Value Format Spelling errors Total Points (Multiply checked cells by point value of column) Final Grade (Sum of total points less 3 points for each day late): Comments: Page 10 of 63 Independent Practice: Spreadsheet Formatting Student Name: Period: Date: Student Activity INDEPENDENT PRACTICE: SPREADSHEET FORMATTING In this exercise, you will complete the following tasks independently: • • • • • Format a header Use a simple formula Copy data Apply font styles Adjust column and row size • • • • Wrap text Indent cell data Format values Add a border 1. 2. 3. Open a new spreadsheet Set a header with your name in the left section, the current date in the center, and the filename assigned to this exercise by your teacher in the right section. Key the data shown in the table below in the exact cell locations 4. 5. 6. 7. 8. 9. 10. Merge and center the title cells for Technology Advancements in all caps and bold across Columns A-F Merge and center the sub-title Sales Income in initial caps and bold across Columns A-F Adjust all column widths to fit contents Center all data in Row 4 and cell range A5:A11 Format the data in Columns D and E for two decimal places Change the title in A4 from Item No to Item Number Wrap the text in A4 Page 11 of 63 Independent Practice: Spreadsheet Formatting Student Name: Period: Date: Student Activity 11. 12. 13. Center the heading in B4 across Columns B and C Replace “Drive” with “Cartridge” in Cell B8 Enter a formula in Cell F5 that will calculate Net Change a. What is the formula? 14. Copy the formula to the remaining cells in Column F and format for 2 decimal places 15. Which items experienced negative growth? How do you know? 16. 17. 18. 19. 20. Add and indent the word Total in Cell A12 Calculate the total net change in Cell F12 and format it for currency Add a single line bottom border to Row 4, Columns A-F Add an accounting border to Cell F12 Save and submit according to teacher directions Page 12 of 63 Guided Practice: More Formatting Student Name: Period: Teacher Demonstration Date: GUIDED PRACTICE: MORE FORMATTING In this exercise, you will follow along with your teacher to enter and format data and complete the following tasks: • Insert and delete columns and rows • Rename a spreadsheet • Move a spreadsheet o Record the notes and steps necessary to complete each task in the spaces provided as your teacher demonstrates o Upon completion of the activity, save and submit according to teacher directions o This spreadsheet will be used again in another activity 1. Open a new spreadsheet and enter the data exactly as shown below • Format numbers and labels (review) • Format borders (review) 2. Set a header with your name, date, and the filename of this activity 3. In Cell D2, insert the word Regular before Hours Worked 4. Format the headings in Row 2 for wrap text, center, initial caps, and bold font, and readjust the column width 5. Delete Row 6 Page 13 of 63 Guided Practice: More Formatting Student Name: Period: Teacher Demonstration Date: 6. Enter the hourly rates for each employee in Column C Employee Name Black, Adrian Buchanan, Sandra Funderburk, Lois Hayes, Claudia Headroom, Max Houseman, Leonard James, Anne Martini, Jack Seymore, Jacob Summers, Ross Tate, Allen Hourly Rate 25 32.75 18.5 17.25 16 9 10 27.5 19.25 18 19 7. Format labels. Remember that sometimes numbers are entered for identification purposes and not for calculations, such as the employee social security numbers in Column A. This data is used to identify each employee and will not be used to calculate pay. Format the data in the cell range A3 to A13 as a label (text). Notes: 8. Format the cells in C3:C13 as currency 9. Format a double-line border above and below the Cells A2:A12 Notes: Page 14 of 63 Guided Practice: More Formatting Student Name: Period: Teacher Demonstration Date: 10. Insert a column between Columns D and E. In the new E2, key the heading Overtime Hours in initial caps and bold Notes: 11. Rename the spreadsheet tab Payroll Notes: 12. Move the Payroll sheet behind Sheet 2 Notes: Page 15 of 63 Independent Practice: More Formatting Student Name: Period: Date: Student Activity INDEPENDENT PRACTICE: MORE FORMATTING In this exercise, you will work independently to enter and format data in a spreadsheet as you complete the following tasks: • • • Format numbers and labels Format borders Insert and delete columns and rows • • Rename a spreadsheet Move a spreadsheet 1. Retrieve the spreadsheet last edited in Guided Practice: More Formatting 2. Key the data below in Column D, Regular Hours Worked, and format the data as number with one decimal place Hourly Rate $ $ $ $ $ $ $ $ $ $ $ 25.00 32.75 18.50 17.25 16.00 9.00 10.00 27.50 19.25 18.00 19.00 Regular Hours Worked 40.0 45.0 39.0 40.5 16.0 32.0 40.0 41.5 6.0 45.0 18.0 SSN Emp Name 457859685 Black, Adrian 451856599 Buchanan, Sandra 456857456 Funderburk, Lois 451857465 Hayes, Claudia 452854586 Headroom, Max 457854566 Houseman, Leonard 451352586 James, Anne 421527865 Martini, Jack 451859654 Seymore, Jacob 456789567 Summers, Ross 451253564 Tate, Allen 3. Format the data in Column B as text 4. Key the word Totals in Cell A17 in bold and format a double-line outline border around the cell 5. Insert a Row above Row 1 and key ABC Hourly Payroll in bold in A1. Format the text as 16 point Arial 6. Merge and center the title across Columns A-I 7. Move the Payroll spreadsheet in front of Sheet 2 8. Rename the sheet Hourly Pay 9. Save and submit your work according to teacher instructions Page 16 of 63 Guided Practice: Spreadsheet Operations Student Name: Period: Teacher Demonstration Date: GUIDED PRACTICE: SPREADSHEET OPERATIONS In this exercise, you will take notes and follow along with your teacher to complete the following tasks: • • • 1. 2. Perform a simple sort Perform a multiple sort Freeze panes • • Fill a series Print a selection Retrieve the spreadsheet last edited in Independent Practice: More Formatting Sort the data in descending order by Employee Name 3. Perform a primary sort of hourly rate and a secondary sort of employee names A primary sort is: A secondary sort is: Procedure: 4. 5. 6. Change the hourly rate for Leonard Houseman to $10.00 per hour Perform the same primary and secondary sort again as in Number 3 What happened? Why? 7. Freeze panes so that the column headings remain stationary when scrolling down to Row 100 Page 17 of 63 Guided Practice: Spreadsheet Operations Student Name: Period: Teacher Demonstration Date: 8. When might this operation be useful? 9. Freeze panes so that Columns A and B remain stationary when scrolling to the far right 10. What happened? How can you fix this so that it will work? 11. Key January in Cell K4 and use the fill series to fill down to Cell K14 with the remaining months of the year 12. Resort the data in alphabetical order by employee name 13. Print only the Employee Name and Hourly Rate information (including the column headings). Center the selection on the page horizontally and print gridlines and row and column headers 14. List examples of when only a selection of data might be printed in business 15. Save and submit according to teacher instructions Page 18 of 63 Independent Practice: Spreadsheet Operations Student Name: Period: Date: Student Activity INDEPENDENT PRACTICE: SPREADSHEET OPERATIONS In this exercise, you will work independently to use print preview and print a selection 1. Open a new spreadsheet and enter the data exactly as shown, including the title appropriately formatted in 16 point bold 2. Merge Cells A2 and B2 and D3 and E3 3. Format the column headings with borders, centered, initial caps, and bold font 4. Use the wrap text feature where appropriate 5. Format the data in Cells A14 and B14 exactly as shown 6. Enter a formula in Cell B14 to calculate total expenses 7. Sort the data by fixed expense in ascending order (primary) and then by item (secondary) 8. Print the data in Columns A-C in horizontally centered format without gridlines or row and column headers 9. Save and submit according to teacher directions Page 19 of 63 Guided Practice: Linking and Embedding Student Name: Period: Date: Student Activity GUIDED PRACTICE: LINKING AND EMBEDDING Part I Instructions: Using word processing software, key the following business letter in block style with block paragraphs and mixed punctuation. Use 1” side margins, mixed punctuation, and the current date. Supply an appropriate salutation and complimentary close. Save the letter when finished keying The letter is to: Mrs. Alice Logan, 617 Soundside Road, Edenton, NC 7932 It is from Joan Knox, Customer Service, 919-589-5563 Letter body: Our records indicate that you have an outstanding debt in the amount shown below. Your account is 90 days past due. Please contact us immediately to make payment arrangements. Accounts over 120 days past due will be turned over to a collection agency and will adversely affect your credit rating. Part II Instructions: Create the following spreadsheet and enter data in the exact cell locations as shown Part III Instructions: 1. Insert and link the spreadsheet below paragraph 1 of the letter 2. 3. Save and close the documents Reopen the spreadsheet and change the amount owed to $2500.00 Page 20 of 63 Guided Practice: Linking and Embedding Student Name: Period: Date: Student Activity 4. 5. 6. Save and close the spreadsheet Reopen the letter What happens? 7. If you accept the link’s edit, what happens? 8. How is this helpful? Page 21 of 63 Independent Practice: Linking and Embedding Student Name: Period: Date: Student Activity INDEPENDENT PRACTICE: LINKING AND EMBEDDING Directions: In this activity, you will work independently to edit documents and practice linking and embedding 1. Retrieve the spreadsheet created in Guided Practice: Linking and Embedding 2. Change the information in the spreadsheet to Barr, John; $1,400.00; 60 days past due 3. Save and close the spreadsheet 4. Retrieve the letter keyed in Guided Practice: Linking and Embedding 5. Allow the update to be performed when the letter is reopened 6. Change the address on the letter to Mr. John Barr, 237 East Brunswick Street, Durham, NC 27485 7. Turn linking off (break the link) 8. Save and close 9. Reopen the spreadsheet and change the account balance to $1,300.00 10. Save and close 11. Reopen the letter 12. What happened? Why? 13. Which is the source document? Why? 14. Which is the target document? Why? Page 22 of 63 Basic Function Notes Student Name: Period: Date: Student Activity BASIC FUNCTION NOTES Directions: Record notes below as your teacher explains basic functions What is a function? What is a cell reference? Give an example of and explain a relative cell reference Give an example of and explain an absolute cell reference Give an example of and explain a mixed cell reference What is the addition function and why is it used? Give an example What is the average function and why is it used? Give an example What is the maximum function and why is it used? Give an example What is the minimum function and why is it used? Give an example Page 23 of 63 Fun with Basic Functions Student Name: Period: Date: Student Activity FUN WITH BASIC FUNCTIONS Once you have mastered how to enter a formula, a function is the next logical step. Think of a function as a shortcut formula. For example, if you want to add 9 numbers in a range of cells, it’s much easier to use the Sum function than to add each cell individually. The formula =B1+B2+B3+B4+B5+B6+B7+B8+B9 means the same thing when expressed as a function: =SUM(B1:B9). Directions: Practice using functions to write formulas for the following 10 statements and answer the questions that follow Practice using functions to write formulas for the items below 1 2 3 4 Add the range of cells A1:B10 Divide A2 by the sum of the range of cells in A10 through C10 Find the highest number in Column B Subtract B8 from the sum of cells C1 through C10 Divide A2 by the result of A6 multiplied by the sum of cells A1 through C10 Find the lowest number in Column A Calculate the average of cells C1:C10 Multiply the sum of the range of cells from A7 through B10 by the sum of the range of cells from B2 through C9 Find the average of all data in the spreadsheet Subtract C10 from the sum of the cells in the range A1 through A10 5 6 7 8 9 10 Page 24 of 63 Fun with Basic Functions Student Name: Period: Date: Student Activity 11. Open a new blank spreadsheet and enter the data in Table 1 (below) in the exact cell locations as shown. Try out your formulas by keying them in Cells D1:D10 of the spreadsheet you just created. Check the accuracy of your work against the answers in Column D of Table 2 (below). Table 1 Enter these numbers in a new spreadsheet Table 2 If you entered the formulas correctly, you should achieve the results in Column D. 12. What items gave you trouble? 13. What tips can you share that make using basic function writing easy? 14. How might you use a spreadsheet to verify your formulas? 15. What should you do if you can’t get a formula to work correctly? Page 25 of 63 Guided Practice: Use Basic Functions Student Name: Period: Teacher Demonstration Date: GUIDED PRACTICE: USE BASIC FUNCTIONS In this exercise, you will follow along with your teacher to enter data into a spreadsheet and perform simple calculations. You will use the functions listed below. • • Addition Division Multiplication Subtraction Average Sum Maximum Minimum You will also use an absolute reference Upon completion of the activity, save and submit according to teacher directions 1. Retrieve the spreadsheet from Independent Practice: Spreadsheet Formatting 2. Delete Column C 3. Use a function to write a formula in Cell C13 that will calculate the total for the range of cells C5:C11 and format the data as currency. Notes: 4. Copy the formula to Column D Notes: 5. Apply an accounting border to C12 and D12 6. Enter the heading Percent of Total Sales in Cell F4 and format the cell to Wrap Text. Page 26 of 63 Guided Practice: Use Basic Functions Student Name: Period: Teacher Demonstration Date: 7. Calculate the Percent of Total Sales for Year 2 by entering a formula in Cell F5 that will divide D13 by D5. Use an absolute reference for Cell D13. Format the data as percent. Copy the formula to the remaining cells in the column. Notes: 8. Review the formulas in F6:F11. What changed and what did not change? Why? Notes: 9. Insert a new column after Column B (it will be a new Column C). 10. Insert the column heading Unit Price in Cell C4 and enter the following prices: Item Computer Digital Camera Flash Drive InkJet Cartridge InkJet Printer PDA Scanner 11. Format the Unit Price column for currency 12. Enter the column heading Quantity in Cell H4 Unit Price 799 275 25 75 575 299 485 Page 27 of 63 Guided Practice: Use Basic Functions Student Name: Period: Teacher Demonstration Date: 13. Enter the quantity data and format the decimal places to zero Item Computer Digital Camera Flash Drive InkJet Cartridge InkJet Printer PDA Scanner Unit Price 799 275 25 75 575 299 485 Quantity 5 900 150 13 25 575 500 14. Enter the column heading Total Price in Cell I4 and format the cell to wrap and center the text 15. In Column I, Calculate the Total Price for each item Notes: 16. Enter the row heading Average in bold in Column A below the word Total and enter a formula in the same row in Column C to calculate the average price of the items Notes: 17. Key the heading Highest in bold in Column A below the word Average and enter a formula in the same row in Column C to find the highest priced item Notes: Page 28 of 63 Guided Practice: Use Basic Functions Student Name: Period: Teacher Demonstration Date: 18. Key the heading Lowest in bold in Column A below the word Highest and enter a formula in the same row in Column C to find the lowest priced item Notes: 19. Format data in Columns D, E, and F as currency 20. Readjust the title and subtitle to center across Columns A-I Notes: 21. Shorten the formula in Cell F11 by using a function 22. Sort the data in descending order by Net Change 23. Print in landscape format Notes: Page 29 of 63 Independent Practice: Use Basic Functions Student Name: Period: Date: Student Activity INDEPENDENT PRACTICE: USE BASIC FUNCTIONS In this exercise, you will work independently to enter data into a spreadsheet and perform simple calculations. You will use the functions and formulas listed below and also copy formulas. Addition Multiplication Average Maximum Directions: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. Retrieve the spreadsheet from Guided Practice: Spreadsheet Formatting In Cell B9, enter a formula to add the quantities of each item Enter and indent Tax in Cell A12 in bold font In Cell F12, calculate the tax by entering a formula to multiply the Total Sale amount by 6% (.06) In Cell A13, key and indent Total Cost in bold In Cell F13, enter a formula to add the Total Sale amount and the Tax In Cell A14, key and indent Discount in bold font In Cell F14, enter a formula that will calculate a 10% discount (multiply the Total Cost by .10) In Cell A15, key Total Due in bold In Cell F15, enter a formula to subtract the Discount from the Total Cost Add a single line border around cell range A15:F15 In Cell A17, key Highest in bold In Cell E17, enter a formula to calculate the highest priced item In Cell A18, key Lowest in bold In Cell E18, enter a formula to calculate the lowest priced item Format only cells F8 and F15 as currency and the rest of the cells in Column F for 2 decimal places Add the heading Percent of Total in Cell G3 and format appropriately to wrap text and appear consistent with the other column headings Readjust the title in Row 1 to center across all columns Division Subtraction Sum Minimum 6411 – Computer Applications I 2008 Unit B – 4.01 – page 30 Independent Practice: Use Basic Functions Student Name: Period: Date: Student Activity 19. Enter a formula in Cell G4 that will calculate the Percent of Total sales (Amount divided by Total) and format the data as percent. You must use an absolute reference for Cell F8 Write the formula used: 20. Verify the accuracy of the formula entered by using a function to write a formula in Cell G8 that will add the percents. If the result is not 100%, you have made a mistake 21. Save and submit your work according to teacher directions. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 31 Overview of Advanced Functions Student Name: Period: Student Activity Date: OVERVIEW OF ADVANCED FUNCTIONS Directions: Record notes about each function in the cells below Function IF Use Example DATE NOW DAYS360 LOOKUP VLOOKUP 6411 – Computer Applications I 2008 Unit B – 4.01 – page 32 Overview of Advanced Functions Student Name: Period: Student Activity Date: Function COUNT Use Example COUNTA COUNTIF LIST Validated Non-validated 6411 – Computer Applications I 2008 Unit B – 4.01 – page 33 Practice with IF Statements Student Name: Period: Student Activity Date: PRACTICE WITH IF STATEMENTS Part I Directions: Write an IF statement for each condition. The first has been completed for you. For number 10, write your own condition and IF Statement. Condition 1. IF A2 is equal to “Feline”, write “Cat” in B2, otherwise, write “Amphibian” IF A5 is equal to “Carrot”, write “Vegetable” in B5, otherwise, write “Mineral” IF A8 is equal to “Explorer”, write “SUV”, otherwise, write “Sedan” IF A11 is equal to “Deli”, copy the contents of A11, otherwise, enter 0 IF A14 is greater that 0, multiply A14 by 8, otherwise, enter 0 IF A17 is equal to 1, enter “True”, otherwise, enter “False” IF A20 is equal to 0, enter “Off”, otherwise, enter “On” IF A23 is greater than 0, multiply A23 by 15, otherwise, enter 0 IF A26 is equal to “Adult”, multiply B26 by 8.50, otherwise, multiply B26 by 5 IF Statement =IF(A2=”Feline”,”Cat”,”Amphibian) 2. 3. 4. 5. 6. 7. 8. 9. 10. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 34 Practice with IF Statements Student Name: Period: Student Activity Date: PRACTICE WITH IF STATEMENTS Part II Directions: 1. Open a new spreadsheet and key the data in the exact locations as shown in the spreadsheet example on the right In Cell C2, enter the IF statement you wrote for Condition 1 in Part I of this activity Copy the IF statement in Cell C2 to Cell C3 In Cell C5, enter the IF statement you wrote for Condition 2 in Part I of this activity Copy the IF statement in Cell C5 to Cell C6 Continue entering the IF statements you wrote in the appropriate cell locations for all of the remaining conditions in Part I Think about the answers that appear and rework any statements that do not return accurate results To view the spreadsheet in formula view, hold down the control key and press the tilde (~) 2. 3. 4. 5. 6. 7. 8. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 35 Guided Practice: Use IF Statements Student Name: Period: Date: Teacher Demonstration GUIDED PRACTICE: USE IF STATEMENTS In this exercise, you will follow along with your teacher to enter data into a spreadsheet, perform simple calculations, use the IF statement and fill a series. • Record the notes and steps necessary to complete each task in the spaces provided as your teacher demonstrates. 1. Open a new spreadsheet and enter the data in the exact locations as shown. Format all column headings and the title to imitate the format of the example. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 36 Guided Practice: Use IF Statements Student Name: Period: Date: Teacher Demonstration 2. Use the Fill Series tool to input employee numbers 3. Calculate the Regular Pay 4. 5. Insert a new column in front of Hours Worked and label it Overtime Rate. Format the heading in bold font and centered, with wrap text In Cell E4, enter a formula to calculate the Overtime Rate (Regular Rate x 1.5) 6. 7. 8. Copy the formula to the remaining cells in the column Insert a column before Regular Pay and label it Overtime Hours and format appropriately In Column F, Hours Worked, change all occurrences of 40 to 45 6411 – Computer Applications I 2008 Unit B – 4.01 – page 37 Guided Practice: Use IF Statements Student Name: Period: Date: Teacher Demonstration 9. In G4, enter an IF statement that will calculate the amount of overtime hours greater than 40. If the hours worked is not greater than 40, a zero will be entered into the cell. (Hint: Try to say If hours worked is greater than or equal to 40, subtract 40 from the hours worked and place the number in the overtime hours, otherwise, enter a zero in spreadsheet terms.) 10. Copy the formula to the remaining cells in the column 11. In H4, enter an IF statement that will calculate only the regular pay (hours no greater than 40). (Hint: Try to say If the hours worked is greater than or equal to 40, multiply 40 times the hourly rate, otherwise, multiply hourly rate by hours worked in spreadsheet terms.) 12. Copy the formula to the remaining cells in the column 13. In J4, enter the label Gross Pay and format it appropriately 14. Calculate Gross Pay by adding the Regular Pay and Overtime Pay 15. Sort the spreadsheet in descending order by Overtime Pay 6411 – Computer Applications I 2008 Unit B – 4.01 – page 38 Guided Practice: Use IF Statements Student Name: Period: Date: Teacher Demonstration 16. Who are the top three overtime pay earners? 17. Sort the spreadsheet in descending order by Regular Pay 18. Who are the top three regular pay earners? 19. In A32, enter the label Averages and format it appropriately 20. Using the average function, calculate the averages for hourly rate, overtime rate, hours worked, and gross pay. Format the cells with monetary figures as currency 21. What is the average gross pay? 22. What is the average number of hours worked? 23. What is the average hourly rate? 24. What is the average overtime rate? 25. In A31, enter the label Total and format it appropriately 6411 – Computer Applications I 2008 Unit B – 4.01 – page 39 Guided Practice: Use IF Statements Student Name: Period: Date: Teacher Demonstration 26. Using the AutoSum function, calculate the totals for regular pay, overtime pay, and gross pay. Adjust column widths if necessary 27. What is the total amount for regular pay? 28. What is the total amount for overtime pay? 29. What is the total amount for gross pay? 30. In Cell K4, enter the label FICA 31. In Cell K5, enter a formula to calculate the amount of FICA tax. It is calculated by multiplying the gross pay by 7.25% (.0725). Copy the formula to all remaining cells in the column 32. In L4, enter the label Net Pay 6411 – Computer Applications I 2008 Unit B – 4.01 – page 40 Guided Practice: Use IF Statements Student Name: Period: Date: Teacher Demonstration 33. In L5, enter a formula to calculate the Net Pay. It is calculated by subtracting the FICA tax from gross pay. Copy the formula to all remaining cells in the column and adjust column widths if necessary 34. Sort the spreadsheet alphabetically by Employee 35. Check the overall appearance of your spreadsheet for the following: Is the title centered across the entire selection? Are all column headings keyed in bold, centered, and is wrap text used in Columns D, E, F, G, and I? Are Columns H-L set for currency? Are Columns D-E and H-L set for two decimal points? Are Columns F and G set for one decimal point? Did you set gridlines over the entire spreadsheet except for the average and total lines? 36. Save and submit according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 41 Independent Practice: Use IF Statements Student Name: Period: Student Activity Date: INDEPENDENT PRACTICE: USE IF STATEMENTS In this activity you will work independently to create a spreadsheet, perform simple calculations, fill a series, and use IF statements. 1. Open a blank spreadsheet 2. Enter the labels and values in the exact cell locations and exactly formatted as shown 3. Format the title of the spreadsheet as Arial, bold, 26-point and the subtitle as Arial, 18-point bold 4. Create a column heading in I3 to read “Value on Hand” 5. Apply bold to the column headings and center and wrap the cell contents 6. Center the title and subtitle across Columns A-I 7. Enter a formula in Cell I4 to calculate the Value on Hand (Cost*Quantity on Hand) of the beverages in stock. Copy the formula to the remaining cells in the column and format the data as currency 8. Enter the word Totals in bold in Cell A26 6411 – Computer Applications I 2008 Unit B – 4.01 – page 42 Independent Practice: Use IF Statements Student Name: Period: Student Activity Date: 9. Use the AutoSum Feature to find the Total for Value On Hand of all the beverages in stock 10. Enter an IF statement formula in Cell G4 so that the word “Reorder” appears if the Cases on Hand is less than the Reorder Point and “NO” appears if it is the opposite. Copy the formula to the remaining cells in the column and center the text 11. Enter an IF statement formula in Cell H4 that will restore the Cases on Hand to 100 if the reorder point has been reached. Copy the formula to the remaining cells in the column 12. Sort the spreadsheet in ascending order by Vendor Name 13. Name the spreadsheet tab Beverages 14. Save and print according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 43 Guided Practice: The Date Function Student Name: Period: Teacher Demonstration Date: GUIDED PRACTICE: THE DATE FUNCTION In this exercise, you will follow along with your teacher to use the date function • • Record the notes and steps necessary to complete each task in the spaces provided as your teacher demonstrates. Upon completion of the activity, save and submit according to teacher directions 1. Create the spreadsheet below, keying the data in the exact cell locations as shown and using the appropriate formulas to make any necessary calculations where columns are blank 2. Use the NOW date function to insert the current date in Cell A1 3. To calculate the 30 Day Payment Date, enter a formula in Cell F3 to add 30 to the value in Cell D3. Copy the formula to the remaining cells in the column Describe the results: Why did they results appear in the date format? 6411 – Computer Applications I 2008 Unit B – 4.01 – page 44 Guided Practice: The Date Function Student Name: Period: Teacher Demonstration Date: 4. 5. Verify the accuracy of your formula by spot checking a few of the values in the column. Each entry should be a month later than the date of service Calculate the Outstanding Balance in Column H by subtracting the Payment Received from the Amount of Bill 6. Write a formula in Column I to indicate the 90 days past due date (add 60 to the value in Column F) 7. Calculate the number of days that have elapsed since the last payment was received by using the Days360 function 8. Determine the average number of days for Column I 9. Save and submit your work according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 45 Independent Practice: The Date Function Student Name: Period: Student Activity Date: INDEPENDENT PRACTICE: THE DATE FUNCTION In this exercise, you will work independently to use the date function and absolute references. 1. Key the spreadsheet below and enter the data in the exact cell locations with the appropriate formatting as shown 2. 3. 4. Use the AutoSum feature to calculate the Total Sales for Years 1 and 2 Use the NOW date function to enter the date in Row 3 Use the absolute reference of Cell E13 to calculate the Percent of Total Sales for Year 2. You will write a formula that divides the Year 2 value for each product by the constant value of Total Sales for Year 2 Format the data in Column F as percent Sort the data in descending order by percent Add Date Ordered as the column heading in Column F Add Date Received as the column heading in Column G Add Time Elapsed as the column heading in Column H 5. 6. 7. 8. 9. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 46 Independent Practice: The Date Function Student Name: Period: Student Activity Date: 10. Enter the following dates in Columns F and G Date Date Ordered Received 5/17/2007 5/18/2007 6/18/2007 6/19/2007 5/31/2007 8/17/2007 6/12/2007 7/13/2007 9/18/2007 10/19/2007 8/14/2007 8/17/2007 6/15/2007 9/30/2007 11. 12. 13. 14. Calculate the days that elapsed between the date ordered and the date received for each item Add the heading Next Order Date in Cell I4 and calculate the next order date for each item by adding 30 days to the date received Re-center the data in Rows 1-3 Save and submit your work according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 47 Guided Practice: Understanding the LookUp Function Student Name: Period: Student Activity Date: GUIDED PRACTICE: UNDERSTANDING THE LOOKUP FUNCTION Directions: For this activity, you will follow along with your teacher to learn about and compare the uses of the VLookUp and LookUp functions. You will need the Calories spreadsheet. Part I: Enter your daily food intake: 1. 2. Open the Calories spreadsheet that has been sent to you electronically by your teacher Beginning in Cell A25, select from the drop down list to record foods you would typically eat in one day (under My Diet) a. If the foods you eat are not on the list, pick the next closest food. b. For example, if you would never eat French fries, select potato chips or fruit. c. Select at least 6 items so that your list at least continues to Cell A30 but does not go farther than A32. Part II: Enter a formula using the VLookUp function to determine your daily calorie intake: 3. What is the cell address of the first item under My Diet? 4. What is the cell range of the Foods list? 5. Look in the Foods List and count which column number contains Calories. (Column A = 1) _ 6. Put it all together in a formula with the VLookUp function: a. In Cell D23, key =VLOOKUP(cell address of your first food, cell range of foods list, column number for calories) b. Your formula should be =VLOOKUP(A23, A1:L20,3) 7. Before copying the formula to the remaining cells in Column B, make the cell range for the foods list (A1:L20) absolute 6411 – Computer Applications I 2008 Unit B – 4.01 – page 48 Guided Practice: Understanding the LookUp Function Student Name: Period: Student Activity Date: 8. When you have finished determining the calories of each food, write a formula below your last entry in Column A to total your daily calories 9. Next, we will use the LookUp function. Use the drop down list in Cell A33 to select an activity that you participate in on a typical day. You must select at least one activity, but may select up to five. 10. The LookUp function will find the value in the column adjacent to the value you entered in Cell A 35. Since we are only looking up a value in a two column array (arrangement of cells), we can use the LookUp function. The data for this formula is located in the cell range A53:B59 with the heading Calories Burned. VLookUp and HLookUp are used when more than two columns are involved. 11. 12. What is the cell address of your first activity under Less Calories Burned? What is the address of the Calories Burned data (including the blank row)? 13. In Cell B5, enter the formula to look up the value for your first activity. Use absolute references for the Calories Burned data. =LookUp(cell address of your first activity, cell address of Calories Burned data) 14. Write a formula in Cell B39 that will subtract the calories burned from the total calories taken in. 15. Save and submit your work according to teacher directions. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 49 Independent Practice: Understanding the LookUp Function Student Name: Period: Student Activity Date: INDEPENDENT PRACTICE: UNDERSTANDING THE LOOKUP FUNCTION Directions: In this activity, you will work independently to use the LookUp Function 1. Retrieve the Calories spreadsheet from Guided Practice: Understanding the LookUp Function 2. You will write a formula using the VLookUp function that will locate the amounts of cholesterol, sodium, and fat consumed in the foods listed under My Diet. 3. In Cell E23, write a formula that will lookup the calcium value from the Foods List for the food listed in Cell A23. 4. Make the address of the array absolute 5. Copy the formula to the remaining cells in column E for each food selected in the My Diet section. For example, if you have a food selected in Cell A28, you should have a formula in Cell E28 that will look up the amount of cholesterol contained in that food. 6. Follow the same procedures to look up the amounts of sodium and fat consumed in Columns F and G for each of the foods listed under My Diet. Now that you know how much you consumed, let’s find out how you stack up against the recommended daily allowances for your age group. 7. Find the totals for Calcium, Sodium, and Fat 8. Write a LookUp formula in Cells E32:G32 to determine if the amounts consumed were within the guidelines 9. The formula for the Calcium is completed for you as an example. It is =LOOKUP(E31, I47:J48) 10. When writing the formula for Total Fat, look up the value in H31 instead of G31 because fats are calculated as a percentage of daily caloric intake. 11. Explain the difference between VLookUp and LookUp. 12. Save and submit according to teacher directions. 6411 – Computer Applications I 2008 Unit B – 4.01 – page 50 Guided Practice: Create a List Student Name: Period: Teacher Demonstration Date: GUIDED PRACTICE: CREATE A LIST In this activity, you will follow along with your teacher to create a list. • Upon completion of the activity, save and submit according to teacher directions 1. The purpose of the list function in spreadsheet operations is: 2. Examples of how lists are used in spreadsheets: 3. What is a validated list and why is it used? 4. 5. 6. Open the 15 GP List-Student spreadsheet that has been sent to you electronically by your teacher Use the fill option to enter employee identification numbers beginning with 001 in Column A (review) Calculate the days employed. If the days employed is calculated by subtracting the current date in Cell H3 from the date of hire, what formula will you use? (review) 7. To calculate the months employed in Column E, write a formula that will divide the total number of days employed by the number of days in a month (31) (review) 6411 – Computer Applications I 2008 Unit B – 4.01 – page 51 Guided Practice: Create a List Student Name: Period: Teacher Demonstration Date: 8. Create a list in Cell G5 using the data from Cells B33:B46 9. Select the departments for each employee according to the list below: Department Accessories Appliances Boys Wear Employee Feathers, Dino Lowder, Mike Dixon, Allen Lewis, Steadman Barefoot, Glen McCloud, James Brown, Mac Ogden, Jane Hatch, Bill Sumney, Ted Floyd, Laura Lox, Ben Martin, Lacy Tate, Allen Seymore, Jacob Hayes, Claudia James, Anne Summers, Ross Funderburk, Lois Martini, Jack Black, Adrian Buchanan, Sandra Chaps, Brenda Hart, Doris Headroom, Max Houseman, Leonard Children's Wear Cosmetics Furniture Housewares Infants Jewlery Men's Wear Shoes Technology Women's Wear 6411 – Computer Applications I 2008 Unit B – 4.01 – page 52 Guided Practice: Create a List Student Name: Period: Teacher Demonstration Date: 10. In Column H, use the LookUp function to calculate the commission rate based on the number of months employed. The data for the lookup information is located in the Commission/Bonus table. 11. In Column I, calculate the Bonus amount by multiplying the Sales by the Commission Rate and format the data as currency 12. Next, we will analyze the information. To do so, select the cell range A 4: I30 and create a list of the data. 13. What was the total commission for the Boy’s Wear Department? 14. What is the average for months of employment for all employees? 15. What is the highest commission for all employees? 6411 – Computer Applications I 2008 Unit B – 4.01 – page 53 Independent Practice: Create a List Student Name: Period: Student Activity Date: INDEPENDENT PRACTICE: CREATE A LIST Directions: In this activity, you will work independently to format a spreadsheet and create a list. 1. 2. 3. Retrieve the spreadsheet last worked on in Guided Practice: Use IF Functions (Payroll spreadsheet) Insert a column after Column B and key Division as the column heading in Cell C3 Beginning in Cell C35, key the following information: Division Accounts Payable Administrative Professionals Customer Support Education Human Resources Maintenance Marketing Payroll Shipping 4. 5. In Cell C4, use the list function to create a drop down list for the divisions and copy the list to all remaining cells in Column C that contain employee data In Column C, select the division for each employee according to the list below: Division Accounts Payable Accounts Payable Administrative Professionals Administrative Professionals Administrative Professionals Customer Support Customer Support Education Education Education Education Education Employee Lowe, Donald White, Lonnie Wayne, Ellen Jones, Barbara Crockett, Jim Barnes, Eddie Jones, Barry Knots, Eddie McIntosh, Angie Love, LeAnn Osswold, Tom Holt, Bill 6411 – Computer Applications I 2008 Unit B – 4.01 – page 54 Independent Practice: Create a List Student Name: Period: Student Activity Date: Division Human Resources Human Resources Maintenance Maintenance Marketing Marketing Marketing Marketing Marketing Marketing Payroll Payroll Shipping Shipping 6. 7. 8. 9. 10. 11. Employee Adams, Lee Kelly, Linda Cane, Rebecca Cotton, Donna Barnes, Lois Melton, Debra Pendergrast, Jane Stevens, Nancy Feree, Eileen McKay, Amy Lowe, Brenda Scott, Patrice Louis, May Miller, Kim Sort the list in alphabetical order by Employee Select the Marketing Division Display the Averages for Hourly Rate, Overtime Rate, Hours Worked, Overtime Hours, Regular Pay, Gross Pay, and FICA Display the total Net Pay Print the data for the Marketing Division Save and submit according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 55 Guided Practice: Use the Count Function Student Name: Period: Teacher Demonstration Date: INDEPENDENT PRACTICE: CREATE A LIST For a larger view, go to 4.01/15 IP List-Key 6411 – Computer Applications I 2008 Unit B – 4.01 – page 56 Guided Practice: Use the Count Function Student Name: Period: Teacher Demonstration Date: GUIDED PRACTICE: USE THE COUNT FUNCTION Directions: In this activity, you will follow along with your teacher to use the count function 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. In Cell A2, key the column heading Student Name Beginning in Cell A3, key the names of 10 of your classmates with their last name first In Cell B2, key the column heading Graduation Year Beginning in Cell B3, key the expected graduation year for each classmate In Cell C2, key the column heading Plans In Cell F2, key Work In Cell F3, key Community College In Cell F4, key 4-year college In cell C3, create a validated list using the data just entered in F2:F4 Survey each classmate and record their after-graduation plans Use the Count function a. The Count function will only return the number of cells in a selected range that contain numbers b. It will not count cells that contain text or are empty c. Use the count function to count the total number of entries in Column B 12. Use another version of the Count function. To further specify the number of diplomas by classifying them into categories, enter the formula =COUNTIF(C3:C12,”Work”) in Cell C14 13. Enter another CountIF formula in Cell C16 to determine the number of students who plan to attend a 4-year college 6411 – Computer Applications I 2008 Unit B – 4.01 – page 57 Guided Practice: Use the Count Function Student Name: Period: Teacher Demonstration Date: 14. Use the short method to format all columns in the spreadsheet at a list 15. Select the information for one classmate 16. Save and submit according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 58 Independent Practice: Use the Count Function Student Name: Period: Student Activity Date: INDEPENDENT PRACTICE: USE THE COUNT FUNCTION Directions: For this activity, you will work independently to use the count function. Upon completion of the activity, save and submit your work according to teacher directions. 1. 2. 3. Open the 4.01/16 IP-Student spreadsheet sent to you electronically by your teacher In Cells A23-A43, select 10 foods that you typically eat in a day from the drop down list in each cell What formula will you write in Cell I23 to look up the amount of fruit in the first item under My Diet (Cell A23)? 4. Enter the formula in I23 and make the Foods List reference absolute before copying it to the rest of the cells in the column that contain matching food items in the My Diet section of Column A. Repeat the process for Grains, Meat & Beans, Milk, and Vegetable. Record the formula you wrote for each below: a. Grains (Cell J23) b. Meat & Beans (Cell K23) c. Milk (Cell L23) d. Vegetable (Cell M23) 5. 6. 7. Calculate the totals for each in Row 31 What formula will you write to determine whether the amount of fruit consumed was within the USDA recommended dietary guidelines if the guidelines for fruits are in Cells K53:L54? 8. 9. Enter the formula in Cell I32 and repeat the process for the remaining food groups. The guidelines for each food group are in cells E51:L54. How did you do with meeting the dietary guidelines? If you’d like more information or would like to tailor the spreadsheet to your specific gender, 6411 – Computer Applications I 2008 Unit B – 4.01 – page 59 Independent Practice: Use the Count Function Student Name: Period: Student Activity Date: age, height, and weight, visit the USDA My Pyramid website at http://www.mypyramidtracker.gov/default.htm. 10. Use the CountIf function to determine the number of food groups you were able to consume. a. In Cell I35, enter the formula =COUNTIF(I23:I32,”>0”). b. Repeat the process and enter the formulas in Cells J35-M35 11. Are your formulas correct? How do you know? 12. In Cell A65, use the count function in a formula to calculate the total number of Activities in the Calories Burned portion of the spreadsheet a. Record the formula you entered in Cell A65 here: b. What was the result of your formula? c. Why? 13. In Cell B66, use the CountA function in a formula to count the total number of activities in the Calories Burned portion of the spreadsheet a. Record the formula you entered in Cell A66 here: b. What was the result of your formula? c. Why? 14. Save and submit your work according to teacher directions 6411 – Computer Applications I 2008 Unit B – 4.01 – page 60 Objective 4.01 Summary Student Name: Period: Date: Student Activity OBJECTIVE 4.01 SUMMARY Part I Directions: Provide an example formula in Column 2 and written explanation for each formula in Column 3. In the last column, provide an example of how the formula or item is used in a business setting. The first one has been done for you. Column1 Item Addition Column 2 Example Formula =C3+B3 Column 3 Explanation of Formula Column 4 Business Use Add the value of Cell C3 Add sales tax and to the value of Cell B3 shipping to the subtotal on an invoice Subtraction Multiplication Division Average Sum 6411 – Computer Applications I 2008 Unit B – 4.01 – page 61 Objective 4.01 Summary Student Name: Period: Date: Student Activity Column1 Item Minimum Column 2 Example Formula Column 3 Explanation of Formula Column 4 Business Use Maximum IF Date Lookup table Absolute reference 6411 – Computer Applications I 2008 Unit B – 4.01 – page 62 Objective 4.01 Summary Student Name: Period: Date: Student Activity Part II Directions: Describe the following items in your own words and give an example of a business application for each 1. Series 2. List 3. Indent 4. Linking and embedding 6411 – Computer Applications I 2008 Unit B – 4.01 – page 63