Excel Formulae and Functions

Learning Outcomes:

• Given a set of criteria, accurately create formulas and perform calculations involving date and time.

• Using given database functions and filters in the Lesson Lab, correctly extract information needed from a worksheet.

• Successfully analyze data using Excel’s Statistical functions as stated within the exercise.

• Using Goal Seek during the Lesson Lab, work a function “backward” with 100% accuracy.

Students will use a variety of functions and timesaving techniques to help with common Math and Statistical functions.

• Entering and editing formulae
• Order of operations rules
• Using formulae for conditional formatting
• Categories of functions
• Common Math and Statistical functions
• Logical and Lookup functions
• Date and Time functions
• Financial functions
• Text functions
• Advanced Lookup Functions
• Using Array Formulae
• Database Functions
• Statistical Functions
• More Math Functions
• Dealing with Worksheet Errors

Excel Formulae and Functions

This workshop is designed for the more sophisticated user of Excel. You will learn to create formulae and perform calculations involving the following functions: Activities covered include: Tracking elapsed time. The Analysis ToolPak and other add-ins. Common Math and Statistical functions. Generating random numbers: RAND. The IF function. Using multiple lookup tables. Creating drop-down lists with Data Validation. MATCH and INDEX, looking up values in a two-dimensional table, using array formulae, matching values in ranges, tagging duplicate entries, extracting unique/common values, working with SUM and AVERAGE with multiple conditions. Look at DSUM, DAVERAGE, DMIN, DMAX, DCOUNT, DCOUNTA, DGET and SUBTOTAL. Work on Standard deviation: STDEV, STDEVP and MEDIAN, PERCENTILE, PERCENTRANK, RANK. Top off the day with trapping and tracing errors.

Class targeted to:

Students needing more information about functions and formulae.

Prerequisites:

Windows: Introduction and Excel: Introduction or equivalent knowledge.