Create Presentation
Download Presentation

Download Presentation
## Excel Lesson 13 Using Powerful Excel Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Excel Lesson 13Using Powerful Excel Functions**Microsoft Office 2010 Advanced Cable / Morrison**Objectives**• Use the COUNT and COUNTA functions. • Utilize the COUNTBLANK function to count blank cells. • Use the Subtotal function with filtered data. • Create a named range. 2 2**Objectives (continued)**• Use a named range in a function. • Use a SUMIF function. • Search for data using VLOOKUP. • Search for data using HLOOKUP.**Vocabulary**• condition • criteria • function number • lookup table 4 4**Introduction**• Excel offers powerful functions to count, sum, and locate specific information. • COUNT function • COUNTA function • COUNTBLANK function • Subtotal function • SUMIF function • VLOOKUP function • HLOOKUP function**Using COUNTA versus COUNT**• To find the total number of cells that have numeric data, use the COUNT function. COUNT function counts numeric data**Using COUNTA versus COUNT (continued)**• To count cells with text, use the COUNTA function. • The COUNTA function counts cells if they contain text or a combination of text and numbers. COUNTA function counts cells with text**Using COUNTBLANK**• The COUNTBLANK function counts the number of cells that do not have any numbers or text entered in them. • You may want to count blank cells to see how many cells do not contain data.**Using COUNTBLANK (continued)**• COUNTBLANK function counts cells without data**Counting Filtered Cells Using theSubtotal Function**• The Subtotal function can be used to count, sum, or average filtered data. • The Subtotal function is =SUBTOTAL (function number, data range). • The function numberis a number that represents a calculation.**Counting Filtered Cells Using theSubtotal Function**(continued) • SUBTOTAL function counts displayed cells**Creating Named Ranges**• You can give arange a meaningful name. • Named ranges can be used in functions. • Especially helpful if you need to use a range in several functions • In a range name, use an underscore (_), rather than a space, to separate words. • Excel does not accept spaces in named ranges.**Creating Named Ranges (continued)**• Name Box with name entered**Using a Named Range in a Function**• Using named ranges in functions is convenient. • You can enter the range name instead of the cell names. • To select and use a named range: • Click the Name Box down arrow. • Select the named range that you want to include in a function.**Using a Named Range in a Function (continued)**• Function with named range**Using the SUMIF Function**• The SUMIF function will only total data that meets a certain condition. • A conditionis a requirement that needs to be met. • Criteriaare the conditions you are searching for in a data range. • The SUMIF function is =SUMIF(range,criteria,sum_range).**Using the SUMIF Function (continued)**• Example of SUMIF Function**Creating a VLOOKUP Function**• LOOKUP functions allow you to find data that already exists. • A lookup tableis the range of data you create in either a vertical or horizontal format, which Excel will use to match against the criteria. • The VLOOKUP function is used when the lookup table is listed vertically in columns.**Creating a VLOOKUP Function (continued)**• Example of VLOOKUP function**Using the HLOOKUP Function**• Use the HLOOKUP function when the data in the lookup table is listed horizontally in rows. • To use the HLOOKUP function, enter the following information into the Function Argument dialog box: • Lookup_value • Table_array • Row_index_num**Using the HLOOKUP Function (continued)**• Example of HLOOKUP function**Using the HLOOKUP Function (continued)**• Function Arguments for HLOOKUP dialog box**Summary**In this lesson, you learned: • The COUNT function counts cells with numeric data and the COUNTA function counts cells with text or a combination of text and numbers. • You can use COUNTBLANK to count cells without any data.**Summary (continued)**• The SUBTOTAL function can be used to count filtered data. • You can name a range of data. • A named range can be used in a function. • The SUMIF function totals data that meet a certain condition.**Summary (continued)**• You can use the VLOOKUP function to find data that is displayed vertically in a lookup table. • You can use the HLOOKUP function to find data that is displayed horizontally in a lookup table.