A Formula Masterclass for Excel power users
You’re already good at Excel, or you wouldn’t be reading this. But, even if you are better than just about everyone you know, do you know which way of solving a problem is the best and why? On which of these steps would you place yourself?
Using a framework to find the best formula for a lookup problem
A framework is a set of rules that helps you to decide between options. Some are simple enough to learn, like the framework for lookups; others might need a summary table of options as a guide. Applying the lookup framework to the following example might look like this.
Gideon Mitchell
I have been working with spreadsheets since the very first one, Visicalc, running on an Apple IIe. As Excel was born and then grew up into the fantastic analysis tool it is today, I have been using it every day working as a management consultant, developing models for car companies, supermarket chains, media firms and airlines. Along the way I have developed a set of frameworks that help you to choose which is the best way to solve a problem in Excel. In this course I will teach you the frameworks I have developed for creating formulae, the lifeblood of Excel models.
What you will learn
In the Formula Masterclass you will learn frameworks for classes of formulae like aggregations and lookups. You will also learn, to pick a few subjects at random,
- How (and why) to make dynamic Named ranges whose position and size can change
- Robust ways to handle errors
- How to use formulae in conditional formatting
- When forecasting series, how to generate nonlinear trendlines from the correct formulae. (Be careful with Excel’s built-in ones. They don’t work.)
- What the Excel date bug is and how to avoid problems with it
- How to create array formulae to solve hard problems
Components of array formulae
To most people – even those who have looked them up in a book – array formulae are a mystery. In this course we dispel the mystery by breaking down array formulae into components.
For example, as part of an array formula the confusing construction ROW(INDIRECT("1:"&MyData)) gives an array in which the elements of the array contain the numbers 1, 2, … up to however many cells there are in MyData. As part of a larger formula this can be used to sort the data, add up the largest five elements and for many other purposes.
There are various other constructions that are useful. We explain how the most important ones work and show how they can be used together to build array formulae that do magical things.
Who would the course benefit?
The course was built as the first in a series for Management Consultants who need a higher level of knowledge than is available in normal Excel courses. It would also suit analysts who use Excel every day and who need to improve their productivity with Excel as far as possible.
The course can be taken as a stand-alone unit. But by taking all courses in a Module, participants will acquire a significant body of knowledge and increase their productivity at work substantially.
The courses in Module A are designed to be taken first and cover the more fundamental and most popular areas. Those in Module B extend the user’s knowledge to the most powerful features of Excel and to some new ideas.
For example, in course S210 on Sensitivity and Risk analysis, we show how you can create inputs to a model that follow a probability distribution, rather than being a fixed value, without using any expensive add-ins. In course W220 we explain the principles of linear and non-linear programming so that you can choose the best algorithm to use for the Solver add-in.
Course content
- Chapter 0 Introduction
- Chapter 1 Aggregation
- Chapter 2 References and Names
- Chapter 3 Lookups
- Chapter 4 Advanced references
- Chapter 5 Date and Time
- Chapter 6 Handling errors and conditions
- Chapter 7 Array formula magic
- Chapter 8 Summary and next steps
Learn more
