The following list is a list of all the documents changed during the last three months.  They are tagged New if created in the past month and updated if they have been updated in the past three months...

Module Three - Before We Begin Programming (Login reuired)  

This module is a reading-only module and lays out some issues which we as novice programmers need to give some consideration to before embarking on our first project.

Module One - Introduction to Macros (Login reuired)  

This module introduces Excel macros and the Macro Recorder as a stepping stone to understanding Visual Basic for Applications as implemented in Excel.

Module Two - Introduction to VBA (Login reuired)  

This module introduces the basics of Visual Basic for Applications, as implemented in Excel, and includes some simple exercises to demonstrate the basic workings of VBA code.

QUOTIENT 

Our function reference article on the use and troubleshooting for the QUOTIENT function.

DELTA 

Our function reference article on the use and troubleshooting for the DELTA function.

COLUMNS, ROWS 

Our function reference article on the use and troubleshooting for the COLUMNS and ROWS function.

COLUMN, ROW, SHEET 

Our function reference article on the use and troubleshooting for the COLUMN and ROW functions.

AREAS 

Our function reference article on the use and troubleshooting for the AREAS function.

ISEVEN, ISODD 

Our function reference article on the use and troubleshooting for the ISEVEN and ISODD function.

PI 

Our function reference article on the use and troubleshooting for the PI function.

NA 

Our function reference article on the use and troubleshooting for the NA function.

ARRAYTOTEXT  

Our function reference article on the use and troubleshooting for the ARRAYTOTEXT function.

Module Twenty-One - Introduction to Array Formulas and Functions (Login reuired) 

Excel has provided array formula functionality since its earliest days, but the introduction of dynamic arrays in Office 365 has seriously improved the power of array calculations, whilst making them vastly easier to understand and manage.

Module Twenty - String Manipulation Functions Introduced (Login reuired) 

The text string manipulation functions allow the modeller to modify, compose and break-down information stored in text strings. They can be useful in creating dynamic headings, labelling charts, converting data for lookups and adding content to presentation elements.

REPT 

Our function reference article on the use and troubleshooting for the REPT function.

FORMULATEXT 

Our function reference article on the use and troubleshooting for the FORMULATEXT function.

SUBSTITUTE 

Our function reference article on the use and troubleshooting for the SUBSTITUTE function.

REPLACE 

Our function reference article on the use and troubleshooting for the REPLACE function.

LEN 

Our function reference article on the use and troubleshooting for the LEN function.

CONCAT, CONCATENATE 

Our function reference article on the use and troubleshooting for the CONCAT and CONCATENATE functions.

TEXTJOIN 

Our function reference article on the use and troubleshooting for the TEXTJOIN function.

FIND, SEARCH 

Our function reference article on the use and troubleshooting for the FIND and SEARCH function.

EXACT 

Our function reference article on the use and troubleshooting for the EXACT function.

CLEAN 

Our function reference article on the use and troubleshooting for the CLEAN function.

CHAR, UNICHAR 

Our function reference article on the use and troubleshooting for the CHAR and UNICHAR functions.

CODE, UNICODE 

Our function reference article on the use and troubleshooting for the CODE and UNICODE function.

VALUETOTEXT 

Our function reference article on the use and troubleshooting for the VALUETOTEXT function.

LOWER, PROPER, UPPER 

Our function reference article on the use and troubleshooting for the LOWER, PROPER and UPPER functions.

Module Nineteen - Introduction to Lookup Functions (Login reuired) 

In this module, the lookup functions are introduced, and their usage demonstrated. The use of lookup functions is compared and contrasted to reference functions.

XLOOKUP 

Our function reference article on the use and troubleshooting for the XLOOKUP function.

XMATCH 

Our function reference article on the use and troubleshooting for the XMATCH function.

MATCH 

Our function reference article on the use and troubleshooting for the MATCH function.

LOOKUP 

Our function reference article on the use and troubleshooting for the LOOKUP function.

HLOOKUP, VLOOKUP 

Our function reference article on the use and troubleshooting for the HLOOKUP and VLOOKUP function.

INDEX 

Our function reference article on the use and troubleshooting for the INDEX function.

Module Eighteen - Adding Scenario Management Scenario Analysis and Data Table Summary (Login reuired) 

In this module, we will add a scenario management mechanism to the model, and then use a data table to provide scenario comparison of all the scenarios run simultaneously.

Module Seventeen - Matching and Referencing Functions Introduced (Login reuired) 

Excel provides a range of functions for locating and retrieving data from tables and calculation blocks. This module introduces the matching and referencing functions. In a later module, the related lookup functions will be introduced.

ADDRESS 

Our function reference article on the use and troubleshooting for the ADDRESS function.

INDIRECT 

Our function reference article on the use and troubleshooting for the INDIRECT function.

OFFSET 

Our function reference article on the use and troubleshooting for the OFFSET function.

NOT 

Our function reference article on the use and troubleshooting for the NOT function.

XOR 

Our function reference article on the use and troubleshooting for the XOR function.

OR 

Our function reference article on the use and troubleshooting for the OR function.

AND 

Our function reference article on the use and troubleshooting for the AND function.

Module Fourteen - Good Layout Simplifies Model Changes (Login reuired) 

This module introduces tasks that make modifications to the Pacific Juices model, and demonstrates how the initial planning enables this to be undertaken simply and with little opportunity for errors to creep in.  While undertaking the modifications, they will be done in a way that allows us to still produce earlier results, allowing for the simple addition of scenario management at a later time.

TRIM 

Our function reference article on the use and troubleshooting for the TRIM function.

Module Thirteen - Conditional Functions including IF, IFS and SWITCH (Login reuired) 

This module introduces alternatives to the IF function for handling conditional expressions. Two new functions were introduced in 2016, but we have not seen them widely adopted, but they are very powerful when applied to a number of multi-conditional calculations.

CHOOSE 

Our function reference article on the use and troubleshooting for the CHOOSE function.

SWITCH 

Our function reference article on the use and troubleshooting for the SWITCH function.

IFS 

Our function reference article on the use and troubleshooting for the IFS function.

MAX, MIN 

Our function reference article on the use and troubleshooting for the MAX and MIN functions.

MAXA, MINA 

Our function reference article on the use and troubleshooting for the MAXA and MINA functions.

Module Eight - Model Layout and Segregation (Login reuired) 

Developing a clear and consistent layout for models is one of the most important tools for controlling construction and referential errors and ensuring that a model is as easy to maintain as possible. An added benefit is that a well laid-out model is also the easiest kind for adding scenario management, sensitivity analysis, charting and dashboarding.

Module Seven - Understanding and Working with Dates & Times in Excel (Login reuired) 

A critical element in modelling is the passing of time, and its impact on valuation and the timing of transactions. In this module we will examine the way in which Excel stores and calculates dates and times. We will examine key functions for working with dates and building dynamic titling and calculation elements for forecasting and modelling,

Module Six - Understanding and Working with Value Types (Login reuired) 

In this module we examine the different value types supported by Excel, and how values implicitly coerced or explicitly converted between value types. We discuss when coercion of type will occur due to the way an expression is built.

Financial Analysis Certificate 4.0 Online Textbook (Login reuired) 

Online textbook and associated materials accompanying the Financial Analysis Certifiecate coourse.
Thirty-seventh Edition, Published August 1, 2021, Revision #1, July 1, 2022

Module Five - Range Addresses and Reference Absoluteness (Login reuired) 

In this module we will discuss and explore the issues around address modes and the use of absolute, row-absolute, column-absolute, and relative addresses.

Module Four - Consolidating Multiple Worksheets (Login reuired) 

In this module, we will complete the model commenced in the previous task. It demonstrates a technique for working with consolidation of workbooks with multiple sheets all sharing a common layout. It reinforces the learnings from the previous module about pointing techniques when building formulas.

Module Three - Effective Techniques for Building Formulas (Login reuired) 

This module which introduces our first model, concerns itself with the basics of building formulas and ensuring consistency in those formulas. It builds on the techniques introduced in the previous module for pointing and selecting. Those techniques will be used for selecting ranges and sheets for input, and in selecting ranges referenced by the formulas.

Updates in Progress (Login reuired) 

Updates now nearly complete.

Please note that the texts for Modules 3 to 20 have been updated where they contain function syntax tables to instead reference the online function reference articles.  This removes some awkward bulk from the materials, but makes the general referencing of this material easier.  Small modifications in the function syntax information will no longer necessitate .updating these materials.

We are also in the process of adding tags to each article, linking to the function reference and ready reference articles, again to make finding related information easier.

Final changes on this project now expected to be completed by July 22 at the latest.

IF 

Our function reference article on the use and troubleshooting for the IF function.

MID 

Our function reference article on the use and troubleshooting for the MID function.

LEFT, RIGHT 

Our function reference article on the use and troubleshooting for the LEFT and RIGHT function.

YEARFRAC 

Our function reference article on the use and troubleshooting for the YEARFRAC function.

MOD 

Our function reference article on the use and troubleshooting for the MOD function.

EOMONTH 

Our function reference article on the use and troubleshooting for the EOMONTH function.

EDATE 

Our function reference article on the use and troubleshooting for the EDATE function.

DAY, MONTH, YEAR 

Our function reference article on the use and troubleshooting for the DAY, MONTH and YEAR functions.

HOUR, MINUTE, SECOND 

Our function reference article on the use and troubleshooting for the HOUR, MINUTE and SECOND functions.

WEEKNUM 

Our function reference article on the use and troubleshooting for the WEEKNUM function.

WORKDAY.INTL, WORKDAY 

Our function reference article on the use and troubleshooting for the WORKDAY.INTL and WORKDAY functions.

NETWORKDAYS.INTL, NETWORKDAYS 

Our function reference article on the use and troubleshooting for the NETWORKDAYS.INTL and NETWORKDAYS functions.

WEEKDAY 

Our function reference article on the use and troubleshooting for the WEEKDAY function.

NOW 

Our function reference article on the use and troubleshooting for the NOW function.

TODAY 

Our function reference article on the use and troubleshooting for the TODAY function.

ISOWEEKNUM 

Our function reference article on the use and troubleshooting for the ISOWEEKNUM function.

VALUE 

Our function reference article on the use and troubleshooting for the VALUE function.

TYPE 

Our function reference article on the use and troubleshooting for the TYPE function.

TIMEVALUE 

Our function reference article on the use and troubleshooting for the TIMEVALUE function.

TEXT 

Our function reference article on the use and troubleshooting for the TEXT function.

NUMBERVALUE 

Our function reference article on the use and troubleshooting for the NUMBERVALUE function.

Our function reference article on the use and troubleshooting for the T function.

Our function reference article on the use and troubleshooting for the N function.

FIXED 

Our function reference article on the use and troubleshooting for the FIXED function.

DATE 

Our function reference article on the use and troubleshooting for the DATE function.

CELL 

Our function reference article on the use and troubleshooting for the CELL function.

Key Topic Lecture Series 2.0 Online Textbooks (Login reuired) 

Online textbooks for the Key Yopic Lecture Series modules.
See modules foredition and publication details

Power BI Certificate 2.0 Online Textbook (Login reuired) 

Online textbook and associated materials accompanying the Power BI Certifiecate coourse.
Second Edition, Published August 1, 2022

Financial Analysis VBA Certificate 3.0 Online Textbook (Login reuired) 

Online textbook and associated materials accompanying the Financial Analysis VBA Certifiecate coourse.
Nineteen Edition, Published June 1, 2021, Revision #1, July 1, 2022

Understanding Range References and Address Modes (Login reuired) 

This Excel Fundamentals article explains how the addressing modes work in Excel and how to understand range references in expressions.

Understanding Excel Values and How They Are Displayed (Login reuired) 

This Excel Fundamentals article explains how Excel stores values and how they are displayed. It covers basic understanding of formatting.

Understanding Functions (Login reuired) 

This Excel Fundamentals article explains how functions operate and how they are used in workbook formulas.

Understanding Defined Names (Login reuired) 

This Excel Fundamentals article explains how Defined Names are created and how they are used for navigation and in formulas.

M02 - Implementing Complex Scenarios in Cash Flow Models (Login reuired) 

Given some starting assumptions, it builds a sample construction cash flow from scratch, implementing scenarios in which the costs are variable as to amount, timing, phasing and in which the cash flows can be automatically allocated on an S Curve basis. Finally it implements sensitivity analysis to allow the comparison of all scenarios within the one model.

M01 - Fundamentals of Model Design and Development in Excel (Login reuired) 

This module demonstrates the rapid design and development of a forecasting model from scratch, given a number of key assumptions, implementing design elements to facilitate model maintenance and then a powerful technique for sensitivity analysis is introduced, incorporating a number of scenarios in the one model.

Understanding Excel Formulas (Login reuired) 

This Excel Fundamentals article explains the basic workings of Excel formulas.

T07 - Formula Auditing Error Tracing and Handling (Login reuired) 

This module of the Key Topic Lecture Series introduces important fundamental information about working with functions and formulas in Excel. It introduces and demonstrates the use of key formula entry and effective ways to simplify and streamline conditional expressions. It introduces the use of key functions for deriving values in models and analyses.

T02 - Advanced Analytic Formulas (Login reuired) 

The use of dynamic references, dynamic names, advanced conditional expressions, array formulas is canvassed in this session. Techniques to avoid redundant and complex calculations which directly impact on model size and performance are discussed and demonstrated. It also presents techniques for working with formulas to build more flexible analyses of performance data and structuring workbooks to facilitate these analyses.

T01 - Function and Formula Fundamentals (Login reuired) 

This module of the Key Topic Lecture Series introduces important fundamental information about working with functions and formulas in Excel. It introduces and demonstrates the use of key formula entry and effective ways to simplify and streamline conditional expressions. It introduces the use of key functions for deriving values in models and analyses.

Volatile Functions and Expressions (Ready Reference 3.0) 

A volatile expression is one that must be recalculated each and every time there is a recalculation event within a workbook. A recalculation event will occur any time someone updates a cell; presses a recalculation shortcut; refreshes a table or query; or executes a macro that triggers recalculation.  The needless use of volatile expressions is one of the things that can make a workbook cumbersome and slow.