Excel Advanced Formulas Course

COMMON FORMULAE

Order of Operation (BEMDAS)

• How to Enter a Formula Using the Keyboard
• Using Point and Click Formulas
• Using AutoSum to Create Formulas
• Automatically Fill in a Series of Formulas (Autofill)
• Another Alternative for Performing the SUM Function
• Calculating a percentage
• AutoFilling Relative Cell References
• Show Formulas in a Spreadsheet
• AutoFilling Absolute Cell References
• Calculating an AVERAGE
• Calculating a MINIMUM and MAXIMUM
• LARGE and SMALL Functions
• REPT Function (Repeats characters)
• MODE function

COUNTING, TESTING AND LOGICAL FORMULAE

• COUNT function
• COUNTA function
• COUNTBLANK function
• COUNTIF function
• SUMIF function
• IF Function to Display a Text Message

Nesting functions

• Nesting an IF Function to Display a Calculated result

IS functions

• ISBLANK functions
• ISERROR functions
• IFERROR functions

Other Functions in the Information Category

AND Function / OR Function

• AND function
• OR function
• Nesting IF with AND/OR functions

LOOKUP AND REFERENCE FUNCTIONS

Naming Ranges

• Naming a Range Using the Name Box

Using VLOOKUP and Reference Functions in Formulas

• VLOOKUP function
• Create Name Ranges using Create from Selection
• HLOOKUP function
• Using the Define Name dialog box

Using LOOKUP

• Using the LOOKUP Function in a Formula

Using INDEX and MATCH

• INDEX function
• MATCH function
• Nesting MATCH and INDEX functions
• Using Nesting to Replace Error Messages using IFERROR

SUBTOTALS

• SUBTOTALS function
• Nesting IF and ISERROR and MATCH functions to compare two lists

DATE AND TIME FORMULAE

Common Date and Time Functions

• Using Simple Date and Time Functions
• TODAY function
• NOW function
• DAY function
• MONTH function
• YEAR function
• DATE function
• NETWORKDAYS function
• More Complex Date Calculations
• Nesting Date Functions

DATABASE FUNCTIONS

Database Functions

• DAVERAGE function
• DCOUNT function
• DCOUNTA function
• DMAX function
• DMIN function
• DSUM function

ROUNDING FUNCTIONS

The Need for Rounding

• ROUND function
• ROUNDUP function
• ROUNDDOWN function
• CEILING function
• FLOOR function
• INT function

FINANCIAL FUNCTIONS

Financial Functions

• RATE function
• NPER function
• PV function
• PMT function

USEFUL TEXT FUNCTIONS

Text Functions

• UPPER function
• LOWER function
• PROPER function
• CONCATENATE function
• TEXT function

STRUCTURED TABLE REFERENCES

Structured References

• Formulas Compared
• Examples of Structured Reference formulas
• Formatting a Table
• Naming the Table
• Turning on the Total Row
• Adding Calculations using the Total Row
• Entering Structured References Using the Mouse or Keyboard

ARRAY FORMULAE

Array Formulae

IMPORTANT AMENDMENT

An Introduction to Arrays and Array Formulas

• Create A Multi-Cell Array Formula

More on Array Formulas

• Advantages of Array Formulas
• Array Formula Syntax
• Rules for Entering and Changing Array Formulas
• Expanding an Array Formula
• Disadvantages of Array Formulas
• Array Constants
• Creating a Horizontal Constant Array
• Creating a Vertical Constant Array
• Creating A 2D Constant Array
• Using Array Constants within Formulae
• LARGE Function as a MultiCell Array
• ROUND Function as a MultiCell Array
• COUNTIF Function as a MultiCell Array

More on Array Formulas

• Naming Array Constants
• Troubleshooting Array Constants
• Naming and Applying an Array Constant in a Formula
• Simplifying the AND Function Using an Array
• Excluding Zeros from a MIN (Array Formula)
• SUM IF Multiple Criteria
• Excluding Zeros from an Average for a Specific Item
• Create an Array from Existing Values
• Create Array Constants from Existing Values
• More Array Examples (with SUM)
• More Array Examples – (with AVERAGE)
• More Array Examples - Count the Differences between Two Ranges of Cells
• Custom {AVERAGE(IF)} function
• Custom {SUM(IF)} function
• Custom {MIN(IF)} function