SPREADSHEET TERMS
Teacher Notes
• Microsoft Office versions 2003 and 2007 were used to create the graphic examples used in this objective. Terms for specific functions and operations (such as sum, spreadsheet, and workbook) may differ if using software other than Microsoft, but will not affect the “understand” activities. The focus of the objective is to teach the concepts independent of the terminology used by specific software.
• All of the spreadsheet files are located in the 4.01 Spreadsheet activities folder and may be sent to students electronically, if needed, as a time saver or for students with disabilities. Spreadsheets that have been prepared specifically for students are labeled as such in the 4.01 activities folder.
• It is extremely important that the activities in this guide be reviewed and practiced by the teacher well in advance of class presentations.
• An activity tracking sheet is located in the 4.01 Objective folder for student use in keeping track of filenames, activities, and grades.
UNPACKED CONTENT
I. Spreadsheets and Components of Spreadsheets
A. A workbook is a file which contains one or more spreadsheets.
B. A spreadsheet (or worksheet) is an arrangement of cells in columns and rows used to organize, analyze, calculate, and report information, usually in numerical form.
• For the remainder of this objective, the term spreadsheet will be used.
C. Cells are individual locations on a spreadsheet.
1. Cell address – Cell A4 refers to one specific location.
2. Cell range – A4:A16 refers to a group of adjacent cells.
3. Column A refers to all of the contents in a vertical range of cells in the first column of the spreadsheet.
4. Row 3 refers to all of the contents in a horizontal range of cells on Row 3.
5. 'All Years'!A6:E16 refers to a range of cells in a specific spreadsheet.
D. Cell data is classified according to its intended purpose.
1. The label classification is used for cells that contain text or for numbers that will not be used in calculations.
2. A value classification indicates that the data has the potential to be used in calculations.
3. A formula instructs the software to perform a calculation.
a. Formulas begin with an equal sign (=). When the equal sign (or in some cases a plus sign (+)) is keyed in a cell, the software “knows” that the data will be used in a calculation.
b. Formulas use the following:
i. Addition
ii. Division
iii. Multiplication
iv. Subtraction
c. Calculations are performed according to the Order of Operations:
i. Parentheses
ii. Exponents
iii. Multiplication and division (from left to right)
iv. Addition and subtraction (from left to right)
1. A basic function is a shortcut for a formula.
a. Components of a Function
i. Cell reference – indicates a cell’s location and provides instructions for how cell data is copied or used in calculations.
a) Relative - cell value changes as the formula is copied
b) Absolute - cell value remains static when copied to other locations
c) Mixed - combination of an absolute and a relative cell
ii. Parentheses – control the Order of Operations
iii. Conditions or criteria tell the function how to calculate the results and what data to use.
b. Functions
i. Sum, for example, =Sum(C4:C18) adds the range of cells from C4 through C18
ii. Average, for example, =Average(C4:C18) determines the average of the range of cells from C4 through C18
iii. Maximum, for example, =Max(C4:C18) finds the highest number in the range of cells from C4 through C18
iv. Minimum, for example, =Min(C4:C18) finds the lowest number in the range of cells from C4 through C18
II. Formatting is applied to spreadsheet components for the purpose of organizing and clarifying information. Data that is presented in a uniform and consistent format is much easier to understand than data presented with random formats. Formatting can be applied to pages, columns, rows, cell ranges, and cells. Formatting features include:
A. A header/footer
B. Font Size and Style
C. Justification
• Left, right, or center justification can be applied globally to columns or rows.
• The format painter can also be used to apply global formats.
1. Left – by default, cells formatted as labels are left justified
2. Indent is a format applied to cell data to emphasize subcategories, such as the itemized list of expenses in a budget.
3. Right – by default, cells formatted as values are right justified
a. Values should be formatted uniformly, such as using two decimal places for all like data or currency for total amounts.
b. Values can be formatted for a set number of decimal places with or without a comma separator.
c. Values can be formatted in a variety of date formats.
d. Other formats include time, percentage, fraction, and scientific.
4. Center – formatting usually applied to titles and column headings
D. Adjusting Height, Width, and Size of Cells, Columns, and Rows
1. Wrap is used to align multi-line text within a cell.
2. Merge is used to combine two or more cells; default alignment is center.
3. Column width is adjusted to fit the longest entry.
E. Borders and Shading
F. Editing
1. Columns and rows can be inserted, copied, pasted, or deleted.
2. Cell data can be cleared, copied or pasted.
G. Renaming a spreadsheet adds a descriptive identifier to the spreadsheet tab.
H. Reordering of spreadsheets
III. Spreadsheet Operations increase the efficiency of data entry, the performing of calculations, and the presentation of information.
A. Spreadsheet operations
1. Sort is used to arrange data in alphabetical or chronological order.
a. A primary sort indicates the primary sort range of data.
b. A secondary sort indicates the next range.
2. Freeze panes allows the user to work in multiple areas of a large spreadsheet and focus the view on specific cell ranges
3. Fill Series is used to fill a column or row with consecutive data
4. Print is used to provide a hard copy
a. Print preview – used to view how data is represented on paper
b. Print a selection – used to print a portion of a spreadsheet
5. Linking and embedding is used to integrate spreadsheet data with other software applications
a. A word processing document (target) may contain a link to a spreadsheet (source) that will update anytime the spreadsheet data is edited
b. An embedded spreadsheet is converted into a graphic image when placed in a target document and does not change to reflect edits made at the source
I. Advanced Functions are used in higher-level operations, such as in conditional and comparison equations to compute interest rates, due dates and payment terms, and financial projections.
A. IF statements - conditional operators
1. Results are returned IF the data specified in an equation meets conditions set by the formula
2. IF statements can be written to carry out an action, such as: IF a value in a cell is greater than or equal to another value, insert the word “Pass” in a cell
B. Date functions - used to calculate a period of time
1. NOW - returns the current date
2. Days360 - calculates difference in days between two dates
C. LookUp function - used to compare a cell value to an array of cells and return a value that matches the location of the value in the array.
1. Lookup - used for two column vectors
2. VLookUp - used when there are more than two columns in the array (lookup table).
D. List - used to assist in organizing spreadsheet information
1. Uses:
a. Control the size or content of data entries
b. Filter for specific content within a list such as displaying only the Southeast region vice presidents
2. Types of Lists
a. Validated - limits data entry to specific choices programmed into the function
b. Non-validated - allows additional entries other than those provided in the drop-down menu
E. Count - used to return the number of cells in a range
1. Count - number of cells in a range that contain numbers
2. CountA - number cells in a range that contain value or letter
3. CountIf - cells that meet a condition set forth in the formula
| |Instructional Activities |Relevancy |Resources |
| |To introduce spreadsheets, facilitate class discussion using the |Helps students UNDERSTAND (B2) |Anatomy of a Spreadsheet activity |
| |following questions: |spreadsheet components |No key is provided for this activity – |
| |What is a spreadsheet? | |refer to unpacked content |
| |Do you or your family use spreadsheets at work or home? For what | | |
| |purposes? | | |
| |Why do businesses use spreadsheets? | | |
| |What does a spreadsheet look like? | | |
| |How do spreadsheets work? | | |
| |Distribute Anatomy of a Spreadsheet for students’ use in taking notes | | |
| |while displaying a spreadsheet and providing examples of each | | |
| |component. | | |
| |Use the unpacked content as reference | | |
| |Page 2 of the activity provides a sample spreadsheet and requires | | |
| |students to identify examples of each component | | |
| |Upon completion of the activity, review answers as a class | | |
| |Preparation: Prepare notes and sample spreadsheets for students in |Helps students UNDERSTAND (B2) |Formula and Basic Function Notes |
| |basic formula writing using the unpacked content. Refer to the Formula|spreadsheet components and basic |activity and key |
| |and Basic Function Notes Key while preparing notes. |formulas |Teacher prepared samples and problems |
| |Distribute Formula and Basic Function Notes for students’ use in taking| |using basic spreadsheet formulas and |
| |notes and performing the simple tasks | |functions |
| |Complete the notes activity in a discussion and demonstration format | | |
| |Allow students to take turns writing formulas on the board for | | |
| |teacher-suggested problems | | |
| |Facilitate discussion about the importance of accuracy in writing | | |
| |formulas | | |
| |Ask for student suggestions about how to check for accuracy | | |
|Continued on next page |
| |
| |
| |
| |
| |Distribute Fun with Basic Formulas and Functions and review the |Helps students EXPLAIN (B2) the |Fun with Basic Formulas and Functions |
| |instructions with the class |cause and effect relationship of |activity and key |
| |#1-10 require students to write formulas |formulas and basic functions |(4.01/1-Fun 2 Bas F&F key |
| |#11 requires students to test their formulas in a spreadsheet and | | |
| |provides the spreadsheet data that must be entered in order to test the| | |
| |formulas | | |
| |#12-15 are reflection questions that should be answered as a | | |
| |teacher-facilitated class discussion once all students have had time to| | |
| |answer them independently | | |
| |Monitor students/provide assistance | | |
| |Note: Formulas can be viewed and printed. In some software, the use | | |
| |of the CTRL +~ (tilde) will change the spreadsheet mode to formula view| | |
| |Note: Formulas can begin with the = or the + symbol | | |
| |Discuss the importance of formatting with students |Helps students UNDERSTAND (B2) |Guided Practice: Spreadsheet |
| |Data that is presented in a uniform and consistent format is much |spreadsheet formatting and |Formatting activity and key |
| |easier to read and understand than data presented with random formats. |formula writing through guided |(4.01/2-GP Formatting-key |
| |Formatting can be applied to pages, columns, rows, cell ranges, and |practice | |
| |cells. | | |
| |Distribute Guided Practice: Spreadsheet Formatting to students | | |
| |Demonstrate the activity and provide ample time for students to take | | |
| |notes and follow along on their computers | | |
| |As part of the activity, students are asked to classify data as labels | | |
| |or values. | | |
| |Refer to the unpacked content to review labels and values | | |
| |Remind students of the importance of keeping pace during demonstrations| | |
| |Upon completion of this activity, review the procedures for formatting | | |
| |a spreadsheet and explain why formatting is important. | | |
| |Distribute Independent Practice: Spreadsheet Formatting to students |Helps students UNDERSTAND (B2) |Independent Practice: Spreadsheet |
| |and instruct them to complete the activity independently |spreadsheet formatting and |Formatting activity and key |
| |Remind students that they can refer to their notes from the previous |formula writing through |(4.01/3- IP Formatting-key |
| |activity |independent practice | |
| |Remind students to self-assess their work with their rubric prior to | | |
| |turning it in | | |
| |Instruct students how to save and submit their work | | |
| |Monitor students as they work and provide assistance as needed | | |
| |Upon completion of the activity, facilitate class discussion about the | | |
| |tasks they performed | | |
| |What formulas did they use? | | |
| |Did they refer to their notes and their rubric? | | |
| |Distribute Guided Practice: Use Basic Formulas & Functions |Helps students UNDERSTAND (B2) |Guided Practice: Use Basic Formulas & |
| |Instruct students to watch and take notes as the activity is |the uses of basic formulas and |Functions |
| |demonstrated and then complete the activity on their own |functions while formatting a |(4.01/4-TechAdv Key |
| |Monitor/provide assistance as needed |spreadsheet | |
| |Distribute Guided Practice: More Formatting to students |Helps students UNDERSTAND (B2) |Guided Practice: More Formatting |
| |Using a digital projector, demonstrate the activity as students take |spreadsheet formatting through |activity and key |
| |notes and follow along on their computers |guided practice |(4.01/5-GP More-Student |
| |Discuss the tasks learned in this activity, why they are important and | |(4.01/6-GP More-key |
| |how they are used in business | | |
| |For example: Why might a column need to be added or deleted? | | |
| |Why might a spreadsheet need to be moved or renamed? | | |
| |Distribute Independent Practice: Use Basic Functions to students and |Helps students UNDERSTAND (B2) |Independent Practice: Use Basic |
| |instruct them to complete the activity independently |spreadsheet formatting through |Functions activity and key |
| |Monitor students and provide assistance as needed |independent practice |(4.01/ 7-IP Use Basic Functions Key |
| |Instruct students to use their rubric as a self check before turning in| | |
| |their work | | |
| |Preparation: Refer to the unpacked content to prepare notes on |Helps students UNDERSTAND (B2) |Guided Practice: Spreadsheet |
| |spreadsheet operations including sort, freeze panes, fill series, and |the benefits and uses of |Operations activity |
| |print a selection |spreadsheet operations |(4.01/ 8-GP Sp Ops-key |
| |Distribute Guided Practice: Spreadsheet Operations to students | | |
| |Using a digital projector, demonstrate the activity as students take | | |
| |notes and follow along at their computers | | |
| |Review answers to the questions embedded in the activity with the class| | |
| |Instruct students to use their rubric as a self check before turning in| | |
| |their work | | |
| |Distribute Independent Practice: Spreadsheet Operations to students |Helps students UNDERSTAND (B2) |Independent Practice: Spreadsheet |
| |and instruct them to complete the activity independently |the benefits and uses of the |Operations activity and key |
| |Monitor students and provide assistance as needed |spreadsheet operations |(4.01/9-IP Operations-key |
| |Instruct students to use their rubric as a self check before turning in| | |
| |their work | | |
| |Upon completion of the activity, review the tasks learned with the | | |
| |class | | |
| |Using a digital projector, describe and demonstrate object linking and |Helps students UNDERSTAND (B2) |Guided Practice: Linking and Embedding|
| |embedding as students take notes and follow along on their computers |the uses of object linking and |activity and key |
| |using Guided Practice: Linking and Embedding |embedding |4.01/10-GP Link-key (letter) |
| |Review the difference between linking and embedding and how to identify| |4.01/11-GP Link-Student (spreadsheet) |
| |the target and the source documents | | |
| |Discuss examples of common business uses of embedded and linked | | |
| |documents | | |
|Continued on next page |
| |
| |
| |
| |
| |
| |
| |
| |
| |Distribute Independent Practice: Linking and Embedding to students and|Helps students UNDERSTAND (B2) |Independent Practice: Linking and |
| |instruct them to complete the activity independently |the uses of object linking and |Embedding activity and key |
| |Instruct students to follow the directions to edit the spreadsheet and |embedding |4.01/12- IP Link-key (letter) |
| |letter from the previous activity to view the effect of linking | |4.01/13-IP Link-key (spreadsheet) |
| |After making the initial edits, instruct students to follow directions | | |
| |to break the link in the word processing document before editing the | | |
| |spreadsheet again | | |
| |Provide help on breaking the link | | |
| |Once students have completed the activity, review the difference | | |
| |between linking and embedding, target and source with the class | | |
| |Preparation: Prepare notes on advanced functions from the unpacked |Helps students UNDERSTAND (B2) |Unpacked content |
| |content, |the purpose and variety of |Overview of Advanced Functions student |
| |Describe and demonstrate advanced functions as students take notes |advanced functions |activity/notes page |
| |using the Overview of Advanced Functions activity sheet | | |
| |Discuss possible business uses of each function | | |
|Continued on next page |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |Distribute Practice with IF Statements to students and review the |Helps students UNDERSTAND (B2) |Practice with If Statements activity |
| |instructions as follows: |the IF function |and key |
| |In Part I, students work in pairs to write IF statements based on each | |(4.01 /14 Practice w IF-key |
| |condition described in the handout. The first item has been completed | | |
| |as an example. | | |
| |Once all students have written their IF statements, review each item | | |
| |through class discussion | | |
| |In Part II of the activity, students will enter pre-defined conditions | | |
| |on a spreadsheet in Column A and then test their IF statements by | | |
| |entering them in the appropriate cell locations (as directed in the | | |
| |activity) in Column C of the spreadsheet | | |
| |(Optional) Send the spreadsheet with the pre-defined conditions to | | |
| |students electronically | | |
| |Once students have completed the activity, ask them to review their | | |
| |work and rework any statements that return incorrect results | | |
| |Go over the activity as a class. If needed, use additional problems | | |
| |and collaborative pairs to help students master their understanding of | | |
| |how to write IF statements | | |
| |Distribute the Guided Practice: Use IF Statements activity to students|Helps students UNDERSTAND (B2) |Guided Practice: Use IF Statements |
| | |the effects of basic formulas and|activity and key |
| |Send the spreadsheet for this activity to students electronically |functions |Spreadsheet Rubric |
| |Using a digital projector, demonstrate the activity as students take | |(4.01/ 15-GP IF-Student |
| |notes and follow along at their computers | |(4.01/ 16-GP Use IF Key |
| |Upon completion of the activity, review the tasks learned and the “why”| | |
| |of each through facilitated discussion | | |
|Continued on next page |
| |
| |
| |
| |
| |Distribute Independent Practice: Use IF Statements to students and |Helps students UNDERSTAND (B2) |Independent Practice: Use IF |
| |instruct them to complete the activity independently |the effects of basic formulas and|Statements activity and key |
| |If desired, the spreadsheet for this activity can be sent to students |functions |(4.01/17- IP If key |
| |electronically | |(4.01/ 18-IP IF-Student |
| |Monitor/provide assistance as needed | | |
| |Allow students to use trial and error with the IF statement logic and | | |
| |talk them through the process of writing the statements | | |
| |Distribute the Guided Practice: The Date Function activity to students|Helps students UNDERSTAND (B2) |Guided Practice: Date activity and key|
| | |the effects of the date functions|(p 81-83) |
| |If desired, the spreadsheet for this activity can be sent to students |(Now and Days360) |(4.01/19-GP Date-Student |
| |electronically | |(4.01/ 20-GP Date-Key |
| |Using a digital projector, demonstrate the activity as students take | | |
| |notes and follow along at their computers | | |
| |Review the uses of the NOW and DAYS360 date functions and describe | | |
| |examples of business uses | | |
| |Distribute Independent Practice: The Date Function along with the |Helps students UNDERSTAND (B2) |Independent Practice: The Date |
| |electronic file, IP Date-Student to students and instruct them to |the effects of the date functions|Function activity and key |
| |complete the activity independently |(Now and Days360) |(4.01/21- IP Date-Student |
| |Monitor students and provide assistance as needed | |(4.01/22- IP Date-Key |
| |Upon completion of the activity, review the three uses of the date | | |
| |function that were used | | |
| |Distribute the Guided Practice: Understanding the LookUp Function |Helps students UNDERSTAND (B2) |Guided Practice: Understanding the |
| |activity to students |the effects of LookUp functions |LookUp Function activity and key |
| |Distribute 4.01/ GP Calories-Student to students electronically |(LookUp and VLookUp) |(4.01/23-GP Calories-Student |
| |Using a digital projector, demonstrate the activity as students take | |(4.01/24-GP Calories-Key |
| |notes and follow along at their computers | | |
| |Review the logic behind the VLOOKUP function and discuss the difference| | |
| |between it and the LOOKUP function | | |
| |Distribute Independent Practice: Understanding the LookUp Function to |Helps students UNDERSTAND (B2) |Independent Practice: Understanding |
| |students and instruct them to complete the activity independently and |the effects of LookUp functions |the LookUp Function activity and key |
| |answer the question that follows |(LookUp and VLookUp) |(4.01/25-IP Calories-Key |
| |Go over the question as a class upon completion of the activity | | |
| |Monitor/provide assistance as needed | | |
| |Teacher Notes: | | |
| |Vector example: In the formula =Lookup(F9, G7:G12, H7:H12), the | | |
| |software is instructed to “look up” the value of Cell F9 in the cell | | |
| |range G7:G12 and return the value that is horizontally adjacent to it | | |
| |and located in the cell range H7:H12 | | |
| |So, if F9=5, G7=3, G8=7, and G9=9, the first “hit” will be to the value| | |
| |of 3 | | |
| |Array example: In the formula =Lookup(F9, G7:I12), the software will | | |
| |return the value that is in the last column (Column I) that matches the| | |
| |lookup value | | |
| |Distribute Guided Practice: Create a List to students and instruct |Helps students UNDERSTAND (B2) |Guided Practice: Create a List |
| |them to take notes as the purpose of the create-a-list function |the benefits and uses of the list|activity and key |
| |described and demonstrated |function |(4.01/26-GP List-Student |
| |Distribute 4.01/15 GP-Students to students electronically | |(4.01/27- GP List-Key |
| |Demonstrate the activity using a digital projector as students take | | |
| |notes and follow along at their computers | | |
| |Upon completion of the activity, facilitate class discussion about how | | |
| |the list function created a more user-friendly spreadsheet | | |
| |Distribute Independent Practice: Create a List |Helps students UNDERSTAND (B2) |Spreadsheet from Guided Practice: Use |
| |Instruct students to complete independently |the benefits and uses of the list|IF Functions |
| |Monitor/provide assistance |function |Independent Practice: Create a List |
| |Compare students’ results | |activity |
| |Facilitate discussion of examples how the create-a-list function is | |(4.01/ 28-IP List-Key |
| |used | |(4.01/ 29-IP Student |
| |Have students seen them in forms on the Internet? | | |
| |Distribute the Guided Practice: Use the Count Function to students |Helps students UNDERSTAND (B2) |Guided Practice: Use the Count |
| |Using a digital projector, demonstrate the activity as students take |the benefits and uses of the |Function activity and key |
| |notes and follow along at their computers |count function |(4.01/30-GP Use Count-key |
| |Discuss uses of the count function in business | | |
| |Distribute the Independent Practice: Use the Count Function to |Helps students UNDERSTAND (B2) |Independent Practice: Use the Count |
| |students |the benefits and uses of the |Function activity |
| |Distribute 4.01/16 IP Count-Student to students electronically |count function |(4.01/31-IP Count-Student |
| |Instruct students to complete the activity independently | |(4.01/ 32-IP Count-Key |
| |Monitor/provide assistance | | |
| |Distribute the Objective 4.01 Summary for students to use as a review |Reinforces students ability to |Objective 4.01 Summary and key |
| |of functions, formulas, and operations learned in this objective |UNDERSTAND (B2) functions, | |
| |Monitor students and provide assistance as needed |formulas, and operations learned | |
| |Go over the activity as a class upon completion |in this objective | |
1.
Instructional Activities
Anatomy of a Spreadsheet 229
Formula and Basic Function Notes 231
Formula and Basic Function Notes Key 234
Fun with Basic Formulas & Functions 238
Fun with Basic Formulas &Functions Key 240
Guided Practice: Spreadsheet Formatting 241
Guided Practice: Spreadsheet Formatting Key 244
Independent Practice: Spreadsheet Formatting 245
Independent Practice: Spreadsheet Formatting 247
Guided Practice: Use Basic Formulas & Functions 248
Guided Practice: Use Basic Formulas & Functions Key 252
Guided Practice: More Formatting 253
Guided Practice: More Formatting Key 256
Independent Practice: Use Basic Functions 257
Independent Practice: Use Basic Functions Key 259
Guided Practice: Spreadsheet Operations 260
Guided Practice: Spreadsheet Operations Key 262
Independent Practice: Spreadsheet Operations 263
Independent Practice: Spreadsheet Operations Key 265
Guided Practice: Linking and Embedding 266
Guided Practice: Linking and Embedding 268
Independent Practice: Linking and Embedding 269
Independent Practice: Linking and Embedding Key 270
Overview of Advanced Functions 271
Practice with IF Statements 273
Practice with IF Statements 274
Practice with IF Statements Key 275
Guided Practice: Use IF statements 276
Guided Practice: Use IF statements Key 282
Independent Practice: Use IF Statements 283
Independent Practice: Use IF Statements Key 285
Guided Practice: The Date Function 286
Guided Practice: The Date Function Key 288
Independent Practice: The Date Function 289
Independent Practice: The Date Function Key 291
Guided Practice: Understanding the LookUp Function 292
Guided Practice: Understanding the LookUp Function Key 294
Independent Practice: Understanding the LookUp Function 295
Independent Practice: Understanding the LookUp Function Key 296
Guided Practice: Create a List 297
Guided Practice: Create a List Key 300
Independent Practice: Create a List 302
Independent Practice: Create a List Key 304
Guided Practice: Use the Count Function 305
Guided Practice: Use the Count Function Key 307
Independent Practice: Use the Count Function 308
Objective 4.01 Summary 311
Objective 4.01 Summary Key 314
Anatomy of a Spreadsheet
|COMPONENT |DESCRIPTION |
|WORKBOOK | |
|SPREADSHEET | |
|CELL | |
|CELL ADDRESS | |
|CELL RANGE | |
|COLUMN | |
|ROW | |
DIRECTIONS: TAKE NOTES AS YOUR TEACHER DEMONSTRATES AND DESCRIBES EACH OF THE SPREADSHEET COMPONENTS LISTED BELOW
|Component |Description |
|Cell | |
|Cell address | |
|Cell range | |
|Column | |
|Row | |
Directions: Use cell addresses from the Invoice spreadsheet above to identify examples of each component
Formula and Basic Function Notes
|DIRECTIONS: RECORD NOTES BELOW AS YOUR TEACHER DISCUSSES FORMULAS AND BASIC FUNCTIONS. |
| |CELL DATA IS CLASSIFIED ACCORDING TO ITS INTENDED PURPOSE DESCRIBE EACH: | |
| |LABEL: | |
| |Value: | |
| |Formula: | |
| |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? | |
| |Use the spreadsheet below to answer questions 4 and 5 |
| | |A |
| |Write a formula that will add all of the numbers in Column B | |
| |What are the four operators? List the name of the operator and its symbol. |
| |Operator |Symbol |Operator |Symbol |
| | | | | |
| | | | | |
| |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? |
| | |
| |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 |
| | |
| |Use the spreadsheet below to answer questions 4 and 5 |
| | |A |
| |Use a function to write a formula that will find the average of all of the values in Row 4 | |
| | | |
| |Use a function to write a formula that will find the highest number in Row 1 | |
| | | |
| |Use a function to write a formula that will find the lowest number in Row 3 | |
| | | |
Formula and Basic Function Notes Key
|DIRECTIONS: RECORD NOTES BELOW AS YOUR TEACHER DISCUSSES FORMULAS AND BASIC FUNCTIONS |
| |CELL DATA IS CLASSIFIED ACCORDING TO ITS INTENDED PURPOSE DESCRIBE EACH: | |
| |LABEL: |THE LABEL CLASSIFICATION IS USED FOR CELLS THAT CONTAIN TEXT OR FOR NUMBERS THAT WILL NOT BE |
| | |USED IN CALCULATIONS. |
| |VALUE: |A VALUE CLASSIFICATION INDICATES THAT THE DATA HAS THE POTENTIAL TO BE USED IN CALCULATIONS |
| |FORMULA: |A FORMULA INSTRUCTS THE SOFTWARE TO PERFORM A CALCULATION |
| |WHAT SYMBOL WILL PREPARE A CELL FOR ENTRY OF A FORMULA? |= (EQUALS) |
| |WHAT SYMBOL WILL TELL SPREADSHEET SOFTWARE NOT TO TREAT CELL DATA AS A VALUE? |‘ (APOSTROPHE) |
| |USE THE SPREADSHEET BELOW TO ANSWER QUESTIONS 4 AND 5 |
| | |A |
| |WRITE A FORMULA THAT WILL ADD ALL OF THE NUMBERS IN COLUMN B |=B1 + B2 + B3 + B4 |
| | | |
| |WHAT ARE THE FOUR OPERATORS? LIST THE NAME OF THE OPERATOR AND ITS SYMBOL |
| |OPERATOR |SYMBOL |OPERATOR |SYMBOL |
| |ADDITION |+ (PLUS) |MULTIPLICATION |* (ASTERISK) |
| |DIVISION |/ (DIAGONAL) |SUBTRACTION |- (MINUS) |
| | |
| |WHAT IS THE ORDER OF OPERATIONS |
| |MATHEMATICAL EQUATIONS ARE PERFORMED IN THE FOLLOWING ORDER: |
| |Parentheses |
| |Exponents |
| |Multiplication and division (from left to right) |
| |Addition and subtraction (from left to right) |
| |For example, in the equation = 5+2*3, the result is 11 because the multiplication operation is performed first and then added to the |
| |number 5 |
| |To change the order of operations, simply place parentheses around the part of the formula to be calculated first. Thus, in the |
| |equation = (5 + 2) * 3, the result is 21 |
| |Which operation would be performed first in the following equation? |
| |=(A8+C9)/(H8-L9)? |
| |A8 would be added to C9 |
| |L9 would be subtracted from H8 |
| |The result of A8 + A9 would be divided by the result of H8-H9 |
| |The value for C7 is 2; C8 is 4; and F4 is 2, |
| |What is the result of the equation =C7+C8*F4? Why? |
| |2 + 4 * 2 =10 The multiplication occurs first and then the addition |
| |What is the result of the equation =(C7+C8)*F4? Why? |
| |2 + 4=6; 6 * 2 = 12 |
| |C7 + C8 is performed first because it is in parentheses |
| |What is a function? |
| |A function is a shortcut for a formula |
| |What is a cell reference? |
| |Indicates a cell’s location and provides instructions for how cell data is copied or used in calculations |
| |Give an example of and explain a relative cell reference |
| |C3 – A relative cell reference means that the cell value changes “relative” to the cell in which it is copied. |
| |If C3 contains the formula =A3 + B3 and the formula is copied from C3 and pasted in D3, the formula in D3 then becomes =B3 + C3 |
| |Give an example of and explain an absolute cell reference |
| |$A$3 – An absolute cell reference means that the cell value remains constant when copied to another cell or used in a formula |
| |If C3 contains the formula =$A$3 + B3 and the formula is copied from C3 and pasted in D3, the formula in D3 then becomes =$A$3 + C3 |
| |Give an example of and explain a mixed cell reference |
| |$A3 – A fixed row combined with a relative column or the other way around |
| |If C3 contains the formula =A$3 + B and the formula is copied from C3 and pasted in D3, the formula in D3 then becomes =B$3 + C3 |
| |What is the addition function and why is it used? Give an example |
| |=SUM(A3:A23) |
| |The sum function tells the software to add the range of cells from A3 to A23 |
| |What is the average function and why is it used? Give an example |
| |=Average(C4:C18) |
| |The average function tells the software to add the range of cells from C4 to C18 and then divide the result by the total number of |
| |cells in the range |
| |What is the maximum function and why is it used? Give an example |
| |=Max(C4:C18) |
| |The max function tells the software to look for the highest value in the range of cells from C4 to C18 |
| |What is the minimum function and why is it used? Give an example |
| |=Min(C4:C18) |
| |The min function tells the software to look for the lowest number in the range of cells from C4 to C18 |
| | |
| |Use the spreadsheet below to answer questions 4 and 5 |
| | |A |
| |Use a function to write a formula that will find the average of all of the values in Row 4 |=Average(A4:D4) |
| |Use a function to write a formula that will find the highest number in Row 1 |=Max(A1:D1) |
| |Use a function to write a formula that will find the lowest number in Row 3 |=Min(A3:D3) |
Fun with Basic Formulas & Functions
DIRECTIONS: PRACTICE USING FUNCTIONS TO WRITE FORMULAS FOR THE FOLLOWING 10 STATEMENTS AND ANSWER THE QUESTIONS THAT FOLLOW.
|Practice using formulas and functions for the items below |
|1 | |Add the range of cells A1:B10 |
|2 | |Divide A2 by the sum of the range of cells in A10 |
| | |through C10 |
|3 | |Find the highest number in the cell range A1:C10 |
|4 | |Subtract B8 from the sum of cells C1 through C10 |
|5 | |Multiply A6 by the sum of cells A1 through C10 and |
| | |then divide the result by A2 |
|6 | |Find the lowest number in the cell range A1:A10 |
|7 | |Calculate the average of cells C1:C10 |
|8 | |Multiply the sum of the range of cells A7 through B10 |
| | |by the sum of the range of cells B2 through C9 |
|9 | |Find the average of all data in the spreadsheet |
| | |(A1:C10) |
|10 | |Subtract C10 from the sum of the cells in the range A1|
| | |through A10 |
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).
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?
Fun with Basic Formulas &Functions Key
SPREADSHEET – FUN W FORM BAS FUNC KEY
12. What items gave you trouble? (Possible student answers)
• Remembering to put an equal sign in front of every formula
• Deciding where to put parentheses
• Figuring out the correct order for the formula
• Forgetting to add appropriate symbols
• Not knowing the shortcuts
• Not knowing the names of functions
13. What tips can you share that make formula writing easy? (Possible student answers)
• Say the problem out loud when writing a formula
• Plug easy numbers in the formula first to test for the correct answer
• Practice makes perfect
• Double check spelling when using functions
14. How might you use a spreadsheet to verify your formulas?
• Answers will vary
15. What should you do if you can’t get a formula to work correctly?
Answers will vary
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. Open a new spreadsheet
2. Set a header with your name, the current date, and the title of this activity using procedures outlined by your teacher. Take notes in the space below.
3. Key the title: In Cell A1, key INVOICE in all caps and bold
4. Is the title classified as a label or a value? Why?
5. Enter the column headings (in bold font):
|In Cell A3, key Item # |In Cell D3, key Description |
|In Cell B3, key Quantity |In Cell E3, key Unit Price |
|In Cell C3, key Unit |In Cell F3, key Amount |
a. Adjust column widths to fit contents:
6. Beginning in Row 4, Column A, key the remaining data as indicated in the table below:
|Item # |Quantity |Unit |Description |Unit Price |Amount |
|8976 |4 |Doz |Flash Drive |25.99 | |
|8632 |4 |Ea |Scanner |99.99 | |
|7793 |8 |Pkg |Web cam |35 | |
7. Center the data in the title cells (Row 3) (in initial caps and bold)
8. Merge and center the title in Row 1 across Columns A-F
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. Wrap and center the text in Cells A3 and E3
Wrap text – Feature that aligns multi-line text within a cell
14. What is the effect of using the wrap text feature on a cell?
15. Format the data in the unit price column for 2 decimals
16. Enter a formula in the last column to calculate the Amount (unit price x quantity) and format the data as currency.
17. Copy the formula to all cells in the Amount column.
18. Key and indent the word Total in bold and initial caps below the last item in Column A.
19. Use a function to enter a formula to add the amounts in the last column and place the result in Cell F8. Format the data as currency.
20. Add an accounting border (single line above and double line below) to Cell F8.
21. Save and submit your work according to teacher directions.
Guided Practice: Spreadsheet Formatting Key
SPREADSHEET – GP FORMATTING KEY
4. Is the title classified as a label or a value? Explain
A label is cell data that will not be used in spreadsheet calculations. A value has the potential to be used in calculations.
10. What is a cell range and how is it represented in spreadsheet terms?
A cell range is a group of cells and is expressed (in Excel) as A1:A20
11. Which columns contain values and which contain labels?
Columns B and E contain values
Columns A, C, and D contain labels
12. Readjust all column widths. Why is this step necessary?
So that the data is visible in each cell
15. What is the effect of using the wrap text feature on a cell?
Wrap feature allows cell data to take up more than one line, as in Cell A3
17. Formula to calculate the amount: =B4 * E4
20. Formula to calculate the total: =SUM(F4:F7) (as noted in the unpacked content, the plus sign can be used instead of the equals sign in some software to begin a formula)
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. Open a new spreadsheet
2. 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.
3. Key the data shown in the table below in the exact cell locations
4. Merge and center the title cells for Technology Advancements in all caps and bold across Columns A-F
5. Merge and center the sub-title Sales Income Comparison-2 Years in initial caps and bold across Columns A-F
6. Adjust all column widths to fit contents
7. Center all data in Row 4 and in cell range A5:A11
8. Format the data in Columns D and E for two decimal places
9. Change the title in A4 from Item No to Item Number
10. Wrap the text in A4
11. Merge and center the heading in B4 across Columns B and C
12. Replace “Drive” with “Cartridge” in Cell B8
13. 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. Add and indent the word Total in Cell A12
17. Use a formula to calculate the total net change in Cell F12 and format it for currency.
What formula did you use?
18. Add a single line bottom border to Row 4, Columns A-F
19. Add an accounting border to Cell F12
20. Save and submit according to teacher directions
Independent Practice: Spreadsheet Formatting
SPREADSHEET – IP FORMATTING KEY
13. The formula is = E5 – D5
15. The items that experienced negative growth are Computers, Flash Drives, Ink Jet Cartridges, and Ink Jet Printers
17. The formula is =SUM(F5:F11)
Guided Practice: Use Basic Formulas & 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 OPERATIONS AND FUNCTIONS LISTED BELOW.
|Addition |Multiplication |Average |Maximum |
|Division |Subtraction |Sum |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 (Technology Advancements)
2. Delete Column C
3. Use a function to write a formula in Cell C12 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.
7. Format the column as percent with one decimal place
8. Apply a single line bottom border to Cell F4
9. Enter a formula in F5 to calculate how much the total computer sales for Year 2 contributed to the total sales for all items for the same year. In other words, what percent is the total computer sales of the total sales for Year 2?
Notes:
=Total Sales Year 2 for Computers/Total Sales Year 2
10. Before copying the formula to the rest of the cells in Column F, edit the formula in Cell F5 to make the cell reference for D12 an absolute reference. Why?
11. Copy the formula to the remaining cells.
12. Insert a new column after Column B (it will be a new Column C).
13. Insert the column heading Unit Price in Cell C4 and enter the following prices:
|Item |Unit Price |
|Computer |799 |
|Digital Camera |275 |
|Flash Drive |25 |
|InkJet Cartridge |75 |
|InkJet Printer |575 |
|PDA |299 |
|Scanner |485 |
14. Format the Unit Price column for currency
15. Enter the column heading Quantity in Cell H4
16. Enter the quantity data in Column H
|Item |Quantity |
|Computer |5 |
|Digital Camera |900 |
|Flash Drive |150 |
|InkJet Cartridge |13 |
|InkJet Printer |25 |
|PDA |575 |
|Scanner |500 |
17. Enter the column heading Total Price in Cell I4 and format the cell to wrap and center the text
18. In Column I, calculate the Total Price for each item and format the data as currency.
Notes:
19. Copy the formula to the remaining cells in Column I
20. 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:
21. 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:
22. 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:
23. Readjust the title and subtitle to center across Columns A-I
Notes:
24. Sort the data in descending order by Net Change
25. Check for uniformity by making sure
a. all column headings are formatted in initial caps with a single bottom border
b. any amounts in Row 12 are formatted with an accounting border
26. Print in landscape format
Notes:
GUIDED PRACTICE: USE BASIC FORMULAS & FUNCTIONS KEY
SPREADSHEET – TECHADV KEY
Guided Practice: More Formatting
IN THIS EXERCISE, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO ENTER AND FORMAT DATA AND COMPLETE THE FOLLOWING TASKS:
• Format numbers and labels (review)
• Format borders (review)
• Insert and delete columns and rows
• Rename a spreadsheet
• Move a spreadsheet
• Use functions and formulas
• 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 Remember to save your work periodically throughout the activity
1. Open the GP More-Student spreadsheet
2. Set a header with your name, date, and the filename of this activity
3. Format the title, ABC Hourly Payroll, in bold, 16 pt. font and center the title across all columns. You may need to adjust the row height.
4. Format the headings in Row 3 for wrap text, center, initial caps, and bold font, and readjust the column width; adjust row height if needed
5. Delete Row 7
6. Format labels – Format the data Column A as a label (text).
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.
Notes:
7. Format the data in Column C as currency
8. Format a double-line border around Row 3
Notes:
9. Rename the spreadsheet tab Payroll
Notes:
10. Move the Payroll sheet behind Sheet 2
Notes:
11. Key the data below in Column D, Hours Worked, and format the data as number with one decimal place.
|Hours Worked |
|40.0 |
|45.0 |
|39.0 |
|40.5 |
|16.0 |
|32.0 |
|40.0 |
|41.5 |
|6.0 |
|45.0 |
|18.0 |
12. Insert a new column after Column F and key Regular Pay as the column heading
13. Enter a formula in Cell G4 to calculate regular pay (Regular Hours x Hourly Rate) and copy the formula to the remaining cells in the column
14. Insert a new column after Column G and key Overtime Pay as the heading
15. Enter a formula in Cell H4 to calculate the Overtime Pay and format the data as currency.
You will enter a formula in Cell H4 to multiply the result of the regular rate multiplied by 1.5 by the overtime hours. Hint: =(Hourly rate * 1.5) * Overtime hours
16. Insert a new column after Column H and key Gross Pay in the heading.
17. In I4, enter a formula to calculate gross pay by adding regular and overtime pay and copy the formula to the remaining cells in the column and format the data as currency.
18. Adjust column widths if necessary, re-center the title of the spreadsheet across all columns, and add borders to the newly added columns
19. In Cell I16, use a function to enter a formula to total all of the gross pay.
20. Save and submit your work according to teacher directions.
Guided Practice: More Formatting Key
SPREADSHEET – GP MORE KEY
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, OPERATIONS, AND FORMULAS LISTED BELOW AND ALSO COPY FORMULAS.
|Addition |Multiplication |Average |Maximum |
|Division |Subtraction |Sum |Minimum |
Directions:
1. Retrieve the spreadsheet from Guided Practice: Spreadsheet Formatting
2. In Cell B8, enter a formula to add the quantities of each item
3. In Cell A10, key and indent Discount in bold font
4. In Cell F10, enter a formula that will calculate a 10% discount (multiply the Total by .10)
5. Enter and indent Tax in Cell A11 in bold font
6. In Cell F11, calculate the tax by entering a formula to multiply the Total by 6% (.06)
7. In Cell A12, key and indent Total Cost in bold
8. In Cell F12, enter a formula to add the Total amount and the Tax
9. In Cell A13, key Total Due in bold
10. In Cell F13, enter a formula to subtract the Discount from the Total Cost
11. Add a single line border around cell range A13:F13
12. In Cell A14, key and indent Highest in bold
13. In Cell E14, enter a formula to calculate the highest priced item
14. In Cell A15, key Lowest in bold
15. In Cell E15, enter a formula to calculate the lowest priced item
16. Format only cells F8 and F13 as currency and the rest of the cells in Column F for 2 decimal places
17. Add the heading Percent of Total in Cell G3 and format appropriately to wrap text and appear consistent with the other column headings
18. Readjust the title in Row 1 to center across all columns
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.
Independent Practice: Use Basic Functions Key
SPREADSHEET – IP USE BASIC FUNCTIONS KEY
GUIDED PRACTICE: SPREADSHEET OPERATIONS
IN THIS EXERCISE, YOU WILL TAKE NOTES AND FOLLOW ALONG WITH YOUR TEACHER TO COMPLETE THE FOLLOWING TASKS:
• Perform a simple sort
• Perform a multiple sort
• Freeze panes
• Fill a series
• Print a selection
1. Retrieve the spreadsheet last edited in Guided Practice: More Formatting (5-GP-More-Student)
2. Sort the data in descending order by Employee Name
Is descending A-Z or Z-A?
3. Perform a primary sort of hourly rate in descending order and a secondary sort of employee names in ascending order
A primary sort is:
A secondary sort is:
Procedure:
4. Change the hourly rate for Leonard Houseman to $10.00 per hour
5. Perform the same primary and secondary sort again as in Number 3
6. What happened? Why?
7. Freeze panes so that the column headings remain stationary when scrolling down to Row 100
8. When might this operation be useful?
9. Unfreeze panes.
10. Freeze panes so that Columns A and B remain stationary when scrolling to the far right.
11. Unfreeze panes.
12. Key January in Cell K4 and use the fill series to fill down to Cell K15 with the remaining months of the year. This column will be used in a later activity.
13. Resort the data in alphabetical order by employee name
14. Print only the SSN and Hourly Rate information (including the column headings). Center the selection on the page horizontally and print gridlines and row and column headings
15. List examples of when only a selection of data might be printed in business
16. Save and submit according to teacher instructions. This spreadsheet will be used again.
Guided Practice: Spreadsheet Operations Key
SPREADSHEET – GP SP OPS KEY
Independent Practice: Spreadsheet Operations
IN THIS EXERCISE, YOU WILL WORK INDEPENDENTLY TO USE PRINT PREVIEW AND PRINT A SELECTION
1. Open a new spreadsheet
2. Key the title in 16 point bold
3. Key and center the column headings using bold font
4. Format a single line border underneath the column headings
5. Format the values in Column B for 2 decimal places
6. Center the data in Column C
7. Use auto-fill to enter the months in Column D and center the data
8. Format the amounts in Column E as currency
9. Merge and center Monthly Budget across Columns A-E
10. Merge Cells A2 and B2and and right justify the data
11. Use the wrap text feature where appropriate (in column headings)
12. Key June in Cell C2
13. Key Total Expenses in bold font and initial caps in Cell A16
14. Enter a formula in Cell B16 to calculate total expenses
15. Delete Column D
16. Sort the data by Type of Expense in ascending order (primary) and then by item (secondary)
17. Print the data in Columns A-C in horizontally centered format without checking gridlines or row and column headings
18. Save and submit according to teacher directions
Independent Practice: Spreadsheet Operations Key
SPREADSHEET – IP OPERATIONS KEY
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 27932
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: Open the 5 GP Link Student spreadsheet that was sent to you electronically by your teacher
1. Select the 5 GP Link tab
2. Filter the data for the information about Alice Logan
Notes
Part III Instructions:
1. Insert a triple space between paragraphs one and two of the letter.
2. Insert and link the spreadsheet at the midpoint between paragraphs one and two in the letter
Notes:
3. Save and close the documents
4. Reopen the spreadsheet and change the filter to Chuck Lacy
5. Select the letter and update the spreadsheet
6. What happens?
7. What else will you need to do to the letter before you can mail it?
a.
b.
c.
Guided Practice: Linking and Embedding
Independent Practice: Linking and Embedding
DIRECTIONS: IN THIS ACTIVITY, YOU WILL WORK INDEPENDENTLY TO EDIT DOCUMENTS AND PRACTICE LINKING AND EMBEDDING
1. Retrieve the letter keyed in Guided Practice: Linking and Embedding and select No to the option to update the spreadsheet
2. Edit the addressee to: Nathan Lewis, 238 Steinbeck Road, Raleigh, NC 28609 and update the salutation
3. Retrieve the spreadsheet created in Guided Practice: Linking and Embedding (5 GP Link)
4. Filter the data for Nathan Lewis’s account
5. Try to update the spreadsheet. Does it work? Why or why not?
6. If it did not update, is the spreadsheet object linked or embedded?
7. Which is the source document? Why?
8. Which is the target document? Why?
Independent Practice: Linking and Embedding Key
SPREADSHEET – IP LINK KEY
Overview of Advanced Functions
DIRECTIONS: RECORD NOTES ABOUT EACH FUNCTION IN THE CELLS BELOW
|Function |Use |Example |
|IF | | |
| | | |
| | | |
|Date | | |
| | | |
| | | |
| NOW | | |
| | | |
| | | |
| Days360 | | |
| | | |
| | | |
|Lookup | | |
| | | |
| | | |
| VLOOKUP | | |
| | | |
| | | |
|COUNT | | |
| | | |
| | | |
| COUNTA | | |
| | | |
| | | |
| COUNTIF | | |
| | | |
| | | |
|LIST | | |
| | | |
| | | |
| Validated | | |
| | | |
| | | |
| Non-validated | | |
| | | |
| | | |
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 |IF Statement |
| |IF A2 is equal to “Feline”, write “Cat” in B2, |=IF(A2=”Feline”,”Cat”,”Amphibian”) |
| |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 | |
| | | |
| | | |
1.
Practice with IF Statements
PART II DIRECTIONS:
2. Open a new spreadsheet and key the data in the exact locations as shown in the spreadsheet example on the right
3. In Cell C2, enter the IF statement you wrote for Condition 1 in Part I of this activity
4. Copy the IF statement in Cell C2 to Cell C3
5. In Cell C5, enter the IF statement you wrote for Condition 2 in Part I of this activity
6. Copy the IF statement in Cell C5 to Cell C6
7. Continue entering the IF statements you wrote in the appropriate cell locations for all of the remaining conditions in Part I
8. Think about the answers that appear and rework any statements that do not return accurate results
9. To view the spreadsheet in formula view, hold down the control key and press the tilde (~)
Practice with IF Statements Key
SPREADSHEET – PRACTICE W IF
Guided Practice: Use IF statements
IN THIS EXERCISE, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO 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 the spreadsheet GP-IF-Student that was sent to you electronically by your teacher and save it to your files.
2. Use the Fill Series tool to input employee numbers in Column A
3. 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
4. In Cell E4, enter a formula to calculate the Overtime Rate (Hourly Rate x 1.5)
5. Copy the formula to the remaining cells in the column
6. Insert a column before Regular Pay and label it Overtime Hours and format appropriately
7. Insert a column before Overtime Hours and label it Regular Hours and format appropriately
8. In Column F, Hours Worked, change all occurrences of 40 to 45
9. In G4, (Regular Hours) enter an IF statement that will calculate the amount of regular hours worked and copy the formula to the remaining cells in the column.
=IF(Hours Worked40,Hours Worked-40,0)
11. In I4, calculate the Regular Pay (Hourly Rate * Regular Hours) for each employee and format the data as currency.
12. In J3, key the heading Overtime Pay in initial caps and bold font.
13. In J4, enter a formula to calculate the overtime pay (Overtime Rate * Overtime Hours) and copy the formula to the remaining cells in the column and format the data as currency.
14. In K3, enter the label Gross Pay and format it appropriately
15. Calculate Gross Pay by adding the Regular Pay and Overtime Pay in Column K
16. Check the spreadsheet to make sure all cells are formatted consistently. For example, the data in Columns I and J should have gridlines.
17. Sort the spreadsheet in descending order by Overtime Pay
18. Who are the top three overtime pay earners?
19. Sort the spreadsheet in descending order by Regular Pay
20. Who are the top three regular pay earners?
21. In A31, enter the label Total and format it appropriately
22. Using the AutoSum function, calculate the totals for regular pay, overtime pay, and gross pay. Adjust column widths if necessary
23. In A32, enter the label Averages
24. 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
25. What is the average gross pay?
26. What is the average number of hours worked? Format the cell for one decimal place.
27. What is the average hourly rate?
28. What is the average overtime rate?
29. What is the total amount for regular pay?
30. What is the total amount for overtime pay?
31. What is the total amount for gross pay?
32. In Cell K3, enter the label FICA
33. In Cell K4, 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
34. In L3, enter the label Net Pay
35. In L4, 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
36. Sort the spreadsheet alphabetically by Employee
37. 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 where needed?
• Are Columns I-M set for currency?
• Are Columns D-E set for two decimal points?
• Are Columns F-H set for one decimal point?
• Did you set gridlines over the entire spreadsheet except for the average and total lines?
38. Save and submit according to teacher directions
1. Guided Practice: Use IF statements Key
SPREADSHEET – GP USE IF KEY
Please refer to the 4.01 Spreadsheet Activities folder to view the formula page
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 the IP-IF Statements spreadsheet sent to you electronically by your teacher and save the spreadsheet to your files.
2. 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
3. Use the AutoSum Feature to find the Total for Value On Hand of all the beverages in stock
4. Enter an IF statement in Cell G4 so that the word “Reorder” appears if the Cases on Hand is less than or equal to the Reorder Point. If the Cases on Hand has not reached the Reorder Point, the formula should return a value of “No”.
5. Copy the formula to the remaining cells in the column and center the text
6. Enter an IF statement in Cell H4 that will restore the Cases on Hand to 100 (100-Cases on Hand) if the reorder point has been reached. If the reorder point has not been reached, the formula should return a value of “n/a”.
7. Copy the formula to the remaining cells in the column.
8. Sort the spreadsheet in ascending order by Vendor Name
9. Name the spreadsheet tab Beverages
10. Save and print according to teacher directions
Independent Practice: Use IF Statements Key
SPREADSHEET – IP USE IF STATEMENTS KEY
Refer to the 4.01 Spreadsheet Activities folder for the Formula View of this activity
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. Open the GP Date Student spreadsheet sent to you electronically by your teacher and save it to your files.
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 the results appear in the date format?
4. Verify the accuracy of your formula by spot checking a few of the values in the column. Each entry should be 30 days later than the date of service
5. Determine the total 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. Copy the formula to the remaining cells in the column
8. Calculate the number of days that have elapsed since the last payment was received (30 Day Payment Date) by using the Days360 function
9. Determine which cell reference in the formula above must not change and format it as an absolute reference before copying the formula to the remaining cells in the column.
10. Determine the average number of days for Column H
11. Save and submit your work according to teacher directions
Guided Practice: The Date Function Key
SPREADSHEET – GP DATE KEY
Independent Practice: The Date Function
IN THIS EXERCISE, YOU WILL WORK INDEPENDENTLY TO USE THE DATE FUNCTION AND ABSOLUTE REFERENCES.
1. Open the IP Date Student spreadsheet that was sent to you electronically by your teacher and save it to your files.
2. Use the AutoSum feature to calculate the Total Sales for Years 1 and 2
3. Use the NOW date function to enter the date in Row 3
4. In E5, use the absolute reference of Cell D13 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
5. Format the data in Column E as percent
6. Sort the data in descending order by percent
7. Add Date Ordered as the column heading in Column F
8. Add Date Received as the column heading in Column G
9. Add Time Elapsed as the column heading in Column H
10. Format the column headings for F, G, and H so that they are consistent with the other column headings.
11. Enter the following dates in Columns F and G
|Date Ordered |Date 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 |
12. Calculate the days that elapsed between the date ordered and the date received for each item
13. 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
14. Re-center the data in Rows 1-3
15. Save and submit your work according to teacher directions
Independent Practice: The Date Function Key
SPREADSHEET – IP DATE KEY
To view the formulas, please refer to the 4.01 Spreadsheet Activities folder
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 GP CALORIES SPREADSHEET.
1. Open the GP Calories Student spreadsheet that has been sent to you electronically by your teacher
2. Select items for your daily food intake in the My Diet section of the spreadsheet
• Beginning in Cell A23 (under My Diet), select an item from the drop down list that is similar to a food you would typically eat in one day
• If the foods you eat are not on the list, pick the next closest food. For example, if you would never eat French fries, select potato chips or fruit.
• Select at least 8 items so that your list continues to Cell A30.
3. What is the cell address of the first food item under My Diet?
4. What is the cell range of the Foods List?
(beginning at the top of the page)
5. Which column is labeled Calories (look in the Foods List)
Record the column number (Column A = 1, Column B=2)
6. Put it all together in a formula with the VLookUp function:
In Cell D23, key =VLOOKUP(cell address of your first food, cell range of foods list, column number for calories)
For example: =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
8. Check for accuracy. Verify that the food you selected in Cell A23 has the correct amount of calories listed.
9. Next, we will use the LookUp function. Beginning in Cell A33 (below Calories Burned) select an activity that you participate in on a typical day.
• Continue selecting activities, if desired, for Cells A34-A37
The LookUp function will find the value in the column adjacent to (next to) the value you entered in Cell A 33. 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 array is located in the cell range A46:B62 with the heading Calories Burned.
10. What is the cell address of your first activity?
11. What is the cell range for the Calories Burned data?
12. Put it all together in a formula with the LookUp function:
In Cell D33, enter the formula =LookUp(cell address of your first activity, cell range of Calories Burned data)
What formula did you enter?
Summary:
In this activity, two functions were used.
1. Name and describe the first function:
2. Name and describe the second function:
3. What is the difference between the two functions?
4. What is an array?
Save and submit your work according to teacher directions.
Guided Practice: Understanding the LookUp Function Key
SPREADSHEET – GP CALORIES KEY
Independent Practice: Understanding the LookUp Function
DIRECTIONS: IN THIS ACTIVITY, YOU WILL WORK INDEPENDENTLY TO USE THE LOOKUP FUNCTION
1. Calculate the nutritional value of the foods you consumed:
a. Retrieve the IP Calories Student Calories spreadsheet from the previous activity
b. You will write a formula using the VLookUp function that will locate the amounts of cholesterol, sodium, and fat consumed in the foods you selected under My Diet.
c. In Cell E23, key a formula that will look up the calcium value of the food you selected in Cell A23 from the Foods List
d. Remember to make the address of the array absolute before copying the formula to the remaining cells in the column.
e. Follow the same procedures to look up the amounts of sodium and fat consumed (in Columns F and G) for each of the foods selected under My Diet.
2. Compare your diet to the Recommended Daily Allowances
a. Find the totals for Calcium, Sodium, and Fat
b. Write a LookUp formula in Cell E32 to determine if the amount of calcium consumed was within the recommended daily allowances guidelines. The spreadsheet address for the calcium guidelines is I46:J48
c. What formula did you enter in Cell E32?
d. Write a LookUp formula in Cell F32 to determine if the amount of sodium consumed was within the recommended daily allowances guidelines. The spreadsheet address for the sodium guidelines is G46:H48.
e. What formula did you enter in Cell F32?
f. Write a LookUp formula in Cell G32 to determine if the amount of fat consumed was within the recommended daily allowances guidelines. The spreadsheet address for the fat guidelines is E46:F50
g. What formula did you enter in Cell G32?
3. Compare VLookUp and LookUp.
4. Save and submit according to teacher directions.
Independent Practice: Understanding the LookUp Function Key
SPREADSHEET – IP CALORIES KEY
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. Open the GP List-Student spreadsheet that has been sent to you electronically by your teacher
5. Use the fill option to enter employee identification numbers beginning with 001 in Column A (review)
6. Calculate the days employed and format the data for zero decimal places. 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. Before copying the formula to the remaining cells in the column, an absolute cell reference must be applied to which cell?
8. 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 (31or 30) (review)
9. Create a list in Cell G5 using the data from Cells B33:B46
10. Select the departments for each employee according to the list below and adjust column widths after all items have been entered
|Department |Employee |
|Accessories |Feathers, Dino |
| | |
| |Lowder, Mike |
| | |
|Appliances |Dixon, Allen |
| | |
| |Lewis, Steadman |
| | |
|Boys Wear |Barefoot, Glen |
| | |
| |McCloud, James |
| | |
| |Brown, Mac |
| | |
|Children's Wear |Ogden, Jane |
| | |
| |Hatch, Bill |
| | |
| |Sumney, Ted |
| | |
|Cosmetics |Floyd, Laura |
| | |
| |Lox, Ben |
| | |
| |Martin, Lacy |
| | |
|Furniture |Tate, Allen |
| | |
| |Seymore, Jacob |
| | |
|Housewares |Hayes, Claudia |
| | |
| |James, Anne |
| | |
|Infants |Summers, Ross |
| | |
| |Funderburk, Lois |
| | |
|Jewelry |Martini, Jack |
| | |
| |Black, Adrian |
| | |
|Men's Wear |Buchanan, Sandra |
| |Chaps, Brenda |
|Shoes |Hart, Doris |
| | |
|Technology |Headroom, Max |
| | |
|Women's Wear |Houseman, Leonard |
| | |
11. 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 (D32:F39).
• Format the data for 2 decimal places.
• Apply an absolute reference to the array
12. In I4, key the column heading Bonus and format it so that it is consistent with the other headings
13. In Column I, calculate the Bonus amount by multiplying the Sales by the Commission Rate and format the data as currency. Adjust column width if necessary.
14. Next, we will analyze the information. To do so, select the cell range A4: I30 and create a list of the data. (Note: In Office 2007, lists are called tables). You will be able to select data from drop down lists.
15. What was the total bonus for the Boy’s Wear Department?
16. What is the average for months of employment for all employees?
17. What is the highest commission rate for all employees?
Guided Practice: Create a List Key
SPREADSHEET – GP LIST KEY
18. THE PURPOSE OF THE LIST FUNCTION IN SPREADSHEET OPERATIONS IS TO ASSIST IN ORGANIZING SPREADSHEET INFORMATION. THE DEPARTMENT DATA IN COLUMN B WAS USED TO CREATE A VALIDATED LIST. ONCE CREATED, THE LIST WAS APPLIED TO COLUMN G AND THUS USERS WERE REQUIRED TO SELECT A DEPARTMENT FROM THE LIST AND WERE NOT PERMITTED TO ADD A DIFFERENT DEPARTMENT. THE VALIDATED LIST ALSO PREVENTS MISSPELLED WORDS! A LIST CAN ALSO BE USED TO CONTROL THE FORMAT OF THE INFORMATION, SUCH AS DATE OR TIME FORMAT. LISTS ARE ALSO VERY HANDY FILTERS. WHEN THE DATA IS SELECTED AS IN THE EXAMPLE ABOVE, THE COLUMN HEADINGS TURN INTO DROP DOWN MENUS TO ALLOW A USER TO SELECT SPECIFIC DATA FOR VIEWING. FOR EXAMPLE, THE MANAGER OF THE APPLIANCES DEPARTMENT MAY WANT TO VIEW ONLY THE DATA FOR HIS/HER DEPARTMENT.
19. Examples of how lists are used in spreadsheets:
• To filter for specific categories of information
• To control how and what data is entered
• To ensure accuracy
20. What is a validated list and why is it used?
A validated list is a control source for data entry. When created, a validated list provides a data entry format that allows only the entries that match the content of the list.
21. Open the GP List-Student spreadsheet that has been sent to you electronically by your teacher
22. Use the fill option to enter employee identification numbers beginning with 001 in Column A (review)
23. Calculate the days employed and format the data for zero decimal places. 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)
=DAYS360(C5:$H$3)
24. Before copying the formula to the remaining cells in the column, an absolute cell reference must be applied to which cell?
H3
25. 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 (31or 30) (review)
=D5/30
INDEPENDENT PRACTICE: CREATE A LIST
DIRECTIONS: IN THIS ACTIVITY, YOU WILL WORK INDEPENDENTLY TO FORMAT A SPREADSHEET AND CREATE A LIST.
1. Open the IP-List-Student spreadsheet that has been sent to you electronically by your teacher.
2. Insert a column after Column B and key Division as the column heading in Cell C3
3. Beginning in Cell C35, key the following information:
|Division |
|Accounts Payable |
|Administrative Professionals |
|Customer Support |
|Education |
|Human Resources |
|Maintenance |
|Marketing |
|Payroll |
|Shipping |
4. 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
5. In Column C, select the division for each employee according to the list below:
|Division |Employee |
|Human Resources |Adams, Lee |
|Customer Support |Barnes, Eddie |
|Marketing |Barnes, Lois |
|Maintenance |Cane, Rebecca |
|Maintenance |Cotton, Donna |
|Administrative Professionals |Crockett, Jim |
|Marketing |Feree, Eileen |
|Education |Holt, Bill |
|Administrative Professionals |Jones, Barbara |
|Customer Support |Jones, Barry |
|Human Resources |Kelly, Linda |
|Education |Knots, Eddie |
|Shipping |Louis, May |
|Education |Love, LeAnn |
|Payroll |Lowe, Brenda |
|Accounts Payable |Lowe, Donald |
|Education |McIntosh, Angie |
|Marketing |McKay, Amy |
|Marketing |Melton, Debra |
|Shipping |Miller, Kim |
|Education |Osswold, Tom |
|Marketing |Pendergrast, Jane |
|Payroll |Scott, Patrice |
|Marketing |Stevens, Nancy |
|Administrative Professionals |Wayne, Ellen |
|Accounts Payable |White, Lonnie |
6. Create a list of all data in the range A3:M29 (in Word 2007, use insert table)
7. Sort the list in alphabetical order by Employee
8. Select the Marketing Division
9. Display the Averages for Hourly Rate, Overtime Rate, Hours Worked, Overtime Hours, Regular Pay, Gross Pay, and FICA
10. Display the total Net Pay (for Marketing)
11. Print the data for the Marketing Division
12. Save and submit according to teacher directions
1.
Independent Practice: Create a List Key
FOR THE ACTUAL SPREADSHEET, VIEW THE ITEM IN THE 4.01 SPREADSHEET ACTIVITIES FOLDER
Spreadsheet – IP List Key
Guided Practice: Use the Count Function
DIRECTIONS: IN THIS ACTIVITY, YOU WILL FOLLOW ALONG WITH YOUR TEACHER TO USE THE COUNT FUNCTION
1. In Cell A2, key the column heading Student Name
2. Beginning in Cell A3, key the names of 10 of your classmates with their last name first
3. In Cell B2, key the column heading Graduation Year
4. Beginning in Cell B3, key the expected graduation year for each classmate
5. In Cell C2, key the column heading Plans
6. In Cell F2, key Work
7. In Cell F3, key Community College
8. In Cell F4, key 4-year college
9. In cell C3, create a validated list using the data just entered in F2:F4
10. Survey each classmate and record their after-graduation plans
11. Use the Count function
• The Count function will only return the number of cells in a selected range that contain numbers
• It will not count cells that contain text or are empty
• Use the count function to count the total number of numeric 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. The value returned by the formula should correspond with the number of students who plan to work after graduation.
13. Enter another CountIF formula in Cell C16 to determine the number of students who plan to attend a 4-year college
14. Select A2:C12 and create a list of the data
15. Select the information for one classmate.
16. The difference between the COUNT function and the COUNTA function is:
17. Save and submit according to teacher directions
Guided Practice: Use the Count Function Key
SPREADSHEET – GP USE COUNT KEY
Make sure that students understand that the COUNT function counts cells that contain numeric values and the COUNTA function counts cells that contain any data.
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. Open the IP-Count-Student spreadsheet sent to you electronically by your teacher
2. Beginning in Cell A23, select 10 foods that you typically eat in a day from the drop down list in each cell
3. Using the VLOOKUP function, write a formula (in I23) that will find the amount of fruit contained in each item.
a. The first food item is located in Cell A23
b. The array is the cell range $A$1:$Q$21
c. The column number of Fruit is 13
Write the formula:
d. Copy the formula to the remaining cells in the column
4. Use the VLOOKUP function to calculate the amount of Grains, Meat & Beans, Milk, and Vegetables for each food item. 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. Calculate the totals for each in Row 33, I23:M23
6. Write a formula to determine if the total amount of fruit is within the USDA recommended dietary guidelines.
a. The total for fruit is located in Cell I33
b. The array is the cell range K54:L56
Write the formula:
7. Write a formula to determine if the total amount of grains is within the USDA Recommended Dietary Guidelines
8. Write a formula to determine if the total amount of meat and beans is within the USDA Recommended Dietary Guidelines
9. Write a formula to determine if the total amount of milk is within the USDA Recommended Dietary Guidelines
10. Write a formula to determine if the total amount of vegetables is within the USDA Recommended Dietary Guidelines
11. 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, age, height, and weight, visit the USDA My Pyramid website at .
12. 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
13. Are your formulas correct? How do you know?
14. 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?
15. In Cell B65, 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 B65 here:
b. What was the result of your formula?
c. Why?
16. Save and submit your work according to teacher directions
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 |Column 2 |Column 3 |Column 4 |
|Item |Example Formula |Explanation of Formula |Business Use |
|Addition |=C3+B3 |Add the value of Cell C3 to the value of |Add sales tax and shipping to the |
| | |Cell B3 |subtotal on an invoice |
|Subtraction | | | |
| | | | |
| | | | |
|Multiplication | | | |
| | | | |
| | | | |
|Division | | | |
| | | | |
| | | | |
|Average | | | |
| | | | |
| | | | |
|Sum | | | |
| | | | |
| | | | |
|Minimum | | | |
| | | | |
| | | | |
|Maximum | | | |
| | | | |
| | | | |
|IF | | | |
| | | | |
| | | | |
|Date | | | |
| | | | |
| | | | |
|Lookup table | | | |
| | | | |
| | | | |
|Absolute reference | | | |
| | | | |
| | | | |
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
Objective 4.01 Summary Key
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.
|Item |Example Formula |Explanation of Formula |Business Use |
|Addition |=C3+B3 |Add the value of C3 to the value of |Add sales tax and shipping to the |
| | |B3 |subtotal on an invoice |
|Subtraction |=C3-B3 |Subtract the value of B3 from the |Subtract FICA tax from gross pay |
| | |value of C3 | |
|Multiplication |=C3*B3 |Multiply the value of C3 times the |Calculate a 10% discount by |
| | |value of B3 |multiplying the total by .10 |
|Division |=C3/B3 |Divide the value of C3 by the value |Divide the quantity by the total cost |
| | |of B3 |to achieve the unit cost |
|Average |=AVERAGE(B3:B21) |Calculate the average of the values |Calculate the average hours worked for|
| | |in the cell range B3 to B21 |a group of employees |
|Sum |=SUM(B3:B21) |Calculate the total of the values in |Calculate the total cost of all items |
| | |the cell range B3 to B21 |on a purchase order or invoice |
|Minimum |=MIN(B3:B21) |Locate the lowest value in the cell |Find the lowest number in a list of |
| | |range B3 to B21 |student test grades |
|Maximum |=MAX(B3:B21) |Locate the highest value in the cell |Find the highest number in a list of |
| | |range B3 to B21 |student test grades |
|IF |=IF(A3>40,B3*1.5,0) |If the value of A3 is less than 40, |If an employee’s hours worked is |
| | |multiply the value of B3 by 1.5; |greater than 40, multiply his hourly |
| | |otherwise, enter a zero |rate by 1.5 |
|Date |=NOW |Insert today’s date |Maintain the current date on a |
| | | |spreadsheet every time it is opened |
|Lookup table |=VLOOKUP(F6,A1:E16,5) |Compare the value in F6 to Column 1 |Locate the cost of maintenance for the|
| | |of the cell range A1 to E16 and |year 2007 |
| | |record the value of the cell in | |
| | |Column 5 that is horizontally aligned| |
| | |with it | |
|Absolute reference |=$A$4 |Do not change the cell address in any|Find the percent of sales to a |
| | |calculation |constant number, such as total sales |
1. Series – Used to quickly and automatically fill cells with values that are sequential. Examples include months, checkbook numbers, and employee numbers
2. List – Used to insure accuracy and make a spreadsheet more user-friendly by pre-formatting the cell values. Examples include lists of employees and comparison data, such as years or expenses
3. Indent – Used to adjust the alignment of cell contents slightly to the right for improved readability. Often used in business income statements
4. Linking and embedding – Used to associate a spreadsheet with another document. A linked spreadsheet will be updated in the target document whenever it is edited in the original document. An embedded spreadsheet is more like a copy of a spreadsheet that has been pasted into a target document. Uses of linked or embedded spreadsheets include a letter from a collection agency to notify a client of an outstanding balance or a letter from a brokerage firm to notify a client of current market trends affecting his/her portfolio.
-----------------------
Table 1
Enter these numbers in a new spreadsheet
Table 2 Did you achieve the same results as those in Column D?
Answers to Questions
5. The spreadsheet should not update because the link was broken
6. The object is embedded
7. The source document is the spreadsheet because it contains the original data
8. The target document is the letter because that is where the object is placed
Answer to Question 7
Students will have to:
a. Change the address (write the address on the board once they determine the answer)
b. Change the salutation
c. Sign the letter
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.