Formulas and Functions: Returning in 2021!

Overview

This course is designed for current users of Microsoft Office and Excel. This course builds on the FUNdamentals and the 5Fs of Excel courses. We will learn introductory, intermediate, and advanced functions/formulas. The introductory functions include SUM, AVERAGE, COUNT, COUNTA, and TODAY functions. The Intermediate functions include PMT and CONCATENATE. Advanced functions include VLOOKUP/HLOOKUP and IF…THEN…ELSE.

Prerequisite: Current Microsoft Office and intermediate Excel user. Comfortable with the Windows Operating system. The Excel FUNdamentals and the 5Fs courses.

Register for your class here. Thank you!

Learning Objectives

After completing this program, participants will be able to:

  • Create and edit basic, intermediate, and advance functions,
  • Use all of the cell references in Excel – Relative, Absolute/Mixed, and 3-D,
  • Develop results with functions based on criteria,
  • Use Introductory functions – SUM, AVERAGE, COUNT, and TODAY,
  • Use Intermediate functions –PMT and CONCATENATE,
  • Use Advanced functions – VLOOKUP/HLOOKUP and IF-THEN-ELSE.

Course Content

Lesson 1: Formula/Functions Syntax and Referencing

  • Syntax for creating and editing formulas and functions,
  • Symbols for continuous and noncontiguous formula/function ranges,
  • Absolute and Mixed Reference symbols,
  • 3-D and linkage symbols,
  • Relative, Absolute/Mixed and 3-D References,

Lesson 2: Fundamental Aggregate Formulas and Functions

  • Introductory Formulas and Functions:
    • SUM,
    • AVERAGE,
    • COUNT,
    • COUNTA,
    • TODAY.
  • Calculating days and years,
  • Linking worksheets with a formula/function (3-D Reference).

Lesson 3: Name Box and Name Manager

  • Name cells, tables, and areas of a worksheet,
  • Use Names for:
    • Absolute Reference,
    • Navigation,
    • Selection,
    • Values,
    • Formulas and Functions.

Lesson 4: Intermediate Functions

  • Financial – PMT,
  • Multiplication, Division, Addition, Subtractions, and Percentage formulas,
  • Text – CONCATENATE, LEFT/RIGHT, UPPER/LOWER,
  • VALUE and DATEVALUE.

Lesson 5: Advanced Functions

  • Lookup and Reference – VLOOKUP/HLOOKUP,
  • IF…THEN…ELSE (one criteria).

Register for your class here. Thank you!