Empowerment Technologies Module 5: Advanced Excel Techniques PDF

11/12 ===== What I Know ----------- Choose the letter of the best answer. Write your answer on a separate sheet of paper. 1. A spreadsheet program used to organize and perform calculations on data. a. Microsoft Excel b. Microsoft Word c. Microsoft Publisher d. Microso...

11/12 ===== What I Know ----------- Choose the letter of the best answer. Write your answer on a separate sheet of paper. 1. A spreadsheet program used to organize and perform calculations on data. a. Microsoft Excel b. Microsoft Word c. Microsoft Publisher d. Microsoft OneNote 2. A function that adds a range of cells e. ADD f. PLUS g. SUM h. TOTAL 3. A function that gets the average of a range of cells. i. AVERAGE j. SUM k. MEAN l. MEDIAN 4. Which among the following is not part of the syntax for AVERAGEIF? m. Average range n. Range o. Logical test p. Criteria 5. A function used to add a certain range of cells if a condition is met. q. SUMIF r. ADDIF s. TOTALIF t. PLUSIF -------------- -- **Lesson 5** -------------- -- What's In --------- In our previous lesson, we have discussed Microsoft Word as one of the powerful applications of Microsoft Office Suites. Microsoft Word has become widely used because of its features which people use to perform various tasks. One of its features is Mail Merge which enables users to efficiently create documents with the same content but may have different recipients. With mail merge, users were able to create documents like letters, e-mail messages, envelopes, labels, and directory. Besides Mail Merge, another feature of Microsoft Word is its ability to insert different kinds of materials such as pictures, clipart, shapes, SmartArt, chart, and screenshot that help enhance a word document. With these materials, users were able to create a layout based on their likings. What's New ------------------------------- Directions: Arrange the scrambled letters to form the correct word or words. Write your answer on a separate sheet. 1. FTIONSNCU 2. CLEL RFEENCERE 3. CNSOTTAN 4. ORWKSETSHE 5. EETDAEPRSHS 6. EGRAEVA 7. AURFLOM 8. WKBROKOO 9. ELLC AGENR 10. TVEICA LELC 1. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 2. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 3. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 4. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 5. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 6. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 7. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 8. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 9. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 10. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ What is It ---------- **What is a spreadsheet?** It is a software application that enables a user to save, sort and manage data in an arranged form of rows and columns.  Beyond arithmetic operations, spreadsheets provide invaluable tools such as functions that are very useful in calculating data. As a result, spreadsheets have become one of the widely used software programs, and MS Excel is the most commonly used spreadsheet tool. Spreadsheets can be used to determine statistical analysis, compute student's grades, keep track business accounts and inventories, and manage databases. Spreadsheets are made up of the following: - **Columns** -- identified with alphabetic headings  **Rows** - identified with numeric headings - **Cells** -- are the intersection of rows and columns - **Cell Reference** -- also known as cell address that identifies a cell on a worksheet. - B2 and D4 are examples of cell references - Use cell reference when creating formulas in Excel to ensure that your formulas are accurate. Take a look at the table below, instead of using the actual values which are 5, 10, and 20 use the cell references A1, A2, and A3.   - **Active Cell** -- the currently selected cell in a spreadsheet and is indicated by a bold outline that surrounds the cell - B5 is the active cell - **Texts or labels** -- It identifies the purpose of a cell, it can be a brief instruction, a title or caption. - **Number data or constant** -- a value that doesn't change and is directly inserted into a cell. - **Formula (mathematical equations)** -- an expression that calculates the value of a cell. - **Mathematical Operators** -- MS Excel uses standard operators for formulas. - **Functions**- These are pre-defined formulas that are already available in MS Excel. This formula used Note: A formula always begins with an equal (=) sign. **Commonly Used Excel Functions** **COUNT** - Counts the number of cells that contain numbers. ### SUMIF(range, criteria, sum\_range) - **Range** - The range of cells that you want to evaluate based on a given criteria. - **Criteria** -- It determines which cells will be added. - **Sum\_Range** -- These are the cells containing numeric values and the cells to add if the condition is met. ### AVERAGEIF(range, criteria, average\_range) - **Range** - The range of cells that you want to apply the criteria. - **Criteria** -- It determines which cells to average. - **Average\_Range** -- These are the cells containing numeric values and the actual set of cells to average. ***COUNTIF(range, criteria)*** - **Range** -- The range of cells to count. - **Criteria** -- The criteria that determines which cells to be counted. ### IF(logical\_test, value\_if\_true, value\_if\_false) - **Logical Test** - A value or logical expression that can be evaluated as True or False. - **Value if True** -- The value to return if the logical test or logical expression evaluates to TRUE. - **Value if False** -- The value to return if the logical test or logical expression evaluates to FALSE. What's More --------------------------------- Direction: Using the tables below create the formula that will solve the given problems. Write your answer on a separate sheet of paper. Use the table below to answer numbers 1 and 2. Count the number of products where price is less than 500. Display "Within the Budget" if the total amount is less than 1,500. Otherwise "Over the Budget" will be displayed. Use the table below to answer number 3. 1. Show the total sales for the month of August. Use the table below to answer numbers 4, 5, and 6. 2. Count the number of orders delivered in Lake Sebu. 3. Count the number of television orders. 4. Count the number of items that are less than 15. Use the table below to answer numbers 7, 8, 9, and 10.  5. Count the number of respondents. 6. Show how many respondents are having glucose level of more than 90. 7. Compute the average of glucose level. 8. Count the number of respondents whose age is older than 50. What I Have Learned ------------------- In this lesson we have learned that a spreadsheet is an application that enables a user to save, sort and manage data in an arranged form of rows and columns. Spreadsheet can be used to calculate students' grades, track inventories, and even manage databases. One of the widely used spreadsheet programs is the Microsoft Excel. MS Excel allows users to organize, format and calculate data using formulas and functions. With Excel formulas and functions, many operations and tasks are performed automatically. Users can type numbers or value directly into the formula or use cell references, so the formula can use any data found in the referenced cells. Some of the many functions of MS Excel are SUM, AVERAGE, COUNT, MIN, MAX, SUMIF, AVERAGEIF, COUNTIF, and IF. These functions are pre-defined, it means that these functions are already available in MS Excel. What I Can Do ------------- Answer the following questions and write your answer on a separate sheet of paper. 1. What is the importance of using Microsoft Excel? 2. Determine other instances where you can use the COUNTIF, SUMIF, and AVERAGEIF functions. 3. How does Excel interpret data?  Assessment --------------------------------- **Modified True or False**: Write TRUE if the statement is correct. If the statement is false, change the underlined word or phrase to make the whole statement correct. Write your answer on a separate sheet of paper. +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Microsoft Excel can perform | | _\_1. | mathematical equations. | +===================================+===================================+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Range, criteria, and logical test | | _\_2. | are part of the | | | | | | AVERAGEIF syntax. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Excel formulas always begin with | | _\_3. | a sign. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Excel can be used to create a | | _\_4. | database. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Column J and row 9 is also | | _\_5. | referred to as cell 9J. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | AVERAGE function used to get the | | _\_6. | average of a certain range of | | | cells if a condition is met. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | SUMIF function adds all the | | _\_7. | numeric values in a range of | | | cells, ignoring those which are | | | not numeric, and place the result | | | in a different cell. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Both IF and AVERAGEIF can perform | | _\_8. | a logical test. | +-----------------------------------+-----------------------------------+ | \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\ | Asterisk (\*) symbol is used to | | _\_9. | multiply items in Excel. | +-----------------------------------+-----------------------------------+ \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_11. IF function can have two results. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_13. The intersection of rows and columns is called cell reference. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_14. AVERAGEIF function returns the arithmetic mean of all the cells in a range that meet a given criteria. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_15. In Excel, the active cell is indicated by an underline. Additional Activities --------------------- Answer the following questions and write your answer on a separate sheet of paper. 1. List at least five benefits of using Microsoft Excel. \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 2. How can Microsoft Excel help you as a student? \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 3. Why do you think Microsoft Excel is important in any business organizations?