2 Guidelines to Be more Efficient with Excel Functions
Know Excel Functions in Deep to avoid guessing in the middle of the Function Wizard.
Never be short of information about Arguments and the way a specific function works.
A Function is a black box, in the sense that doesn’t allow you to see the computations neither modify them.
Being trapped in the Function Wizard or formula bar is an unnecessary waste of time.
Here you will learn how to enter Arguments in a flash, have fun writing Functions, be confident in outputs, avoid Too-Long-Formulas with User Defined Functions and more.
To be more efficient with Excel Functions you need to take two approaches:
- Become a local Function Expert
- Write your own Library
This is inevitable; if you want to be more efficient in your job and get promoted you need to invest time in your education.
There is good news for you…
Once you master one complex formula that involves nested functions you gain confidence to build more and more complex Excel Spreadsheets.
You must compact long formulas that occupy one or more than one cell into a single new custom Function (UDF). This also allows you to reuse knowledge from model to model.
Let’s explore the 2 guidelines and more…
1. Become a Local Functions Expert
341 Excel Functions surpass your needs, so it is important to develop your toolkit, this is the group of functions you cannot build any model without.
How to manage three hundred Functions? Group them.
Divide 341 Functions in two groups:
- Real World Functions
- Spreadsheet Functions
This classification is not intended to be exhaustive; it will give you a practical framework to manage the bunch of tools.
Learn about the functions that belong to these groups and examine examples in the Function List page.
To have a reference guide where you can explore how to create megaformulas, financial formulas examples, possibilities of array formulas, how to develop custom functions with VBA and more, read Excel 2003 Formulas and Functions.
Well; let’s explore this interesting categorization…
Know the Real World Functions List
These Functions return values, numerical results.
These types of Excel Functions represent the formulas you knew in High School and University; they also need Constants, math operators, variables and other functions to work. Additionally, they speed the process of modeling building since the Relationships are established beforehand.
Formal formulas in Math, Statistics, Financial, etc are Functions in Excel.
They exist before Excel and before Spreadsheets as established formulas in Science, Math, Statistics, etc. Five categories belong to this type:
- Math and Trigonometry and
- Statistical functions
For example the Statistical Formula ARITHMETIC MEAN has the following notation:
It corresponds to the AVERAGE Function from Statistical Functions category in Excel, its notation is:
There are some differences between real world Formula and the Excel Function
- Notation, the most obvious
- The name differs: ARITHMETIC MEAN vs. AVERAGE
- The symbols differ: no symbols in Excel
- The name of the arguments differ: number 1, number 2 vs. xi
- Calculation sequence
On paper, you do a lot of work to get partial results that serve as input for the formula. You perform the final calculation replacement when other variables are ready; this calculation is usually done in separate section of the paper.
On paper, you work your formula in blocks, on Excel, you work with all the arguments at a time, Excel doesn’t wait until you have one block ready, you just put the arguments (all) and Excel delivers.
Some authors don’t know exactly what formula corresponds outside, this is a risky habit. They start using it right into their models.
The result is Unnecessary frustration; you get trapped in Excel Function wizard or in the formula bar or cell. If you don’t know the formula well, learn about it outside Excel.
An Excel with only these functions would be an improved scientific calculator.
The Spreadsheet Functions List
These set of functions added versatility to Excel.
They were created for the Spreadsheet Environment to assist Users. Six categories belong to this type:
- Date and time
- Lookup and reference and
- Texts functions
Spreadsheet Functions return information about the content of cells; they also retrieve data from other cells and provide references to other cells.
Excel Commands also play a role in giving extra functionality.
Well, this has been a good start…
Deepen your understanding of any of the 341 functions, go to Excel Functions List
Return from Excel Functions to Excel Spreadsheet Authors Home